views:

82

answers:

1

Subquestioning SQL - when should you use “with (nolock)”

In one local financial institution I was rebuked by their programmers for expressing them my opinion that (their programmers' obsession with) (b)locking issues in their MS SQL Server 2005 database(s) did not make much sense to me.

What are the possible issues with possible locking, blocking, deadlocking if financial operations are never updated, deleted and even incorrect operations (part of "transactions"?) are corrected by inserting (adding) new correcting records into database(s)?
What is the term for this in English? In other languages it is called storno, stornoed (?) operations/records.

So, as I understand, the "transactions" are really never rolled back and there are never incorrect/non-existent records, only non-actualized ones.

Update:
I googled for storno and could not find any results with its definition in English or its use in English texts.
I found definition for storno (in latin letters) only in Italian.
But accounting was invented in Italia and many Italian accounting terms are used in other languages, for ex., in Russian accountance (banking).
I also thought that it was internationally accepted practice in financial operations accountance, isn't it?

Update2:
S.Lott gave me link to The way that transactions are reversed in an ERP application is a big deal! telling that storn is reversal transaction.

Well, this is not correct. Storno is not only transation, it is any operation (part of transaction) correcting incorrect operation though 2 operations combined might seem to reverse tranaction (consisting of 2 operations - of crediting and debiting on target and source accounts).

So, storno is not common financial accountancy practice through the world?

Anyway, I'd like to avoid discussion of accountancy details/techniques/terms and to restrict the question to context when records are never deleted or updated.

What are the possible problems with locking, blocking, deadlocking, performance in this context?

A: 

"Storno Transactions" or "Reversing Transactions" are summarized nicely. In lots of places.

http://richardatopenbravo.blogspot.com/2010/02/way-that-transactions-are-reversed-in.html

http://help.sap.com/saphelp_46b/helpdata/en/d2/6f921f415e11d182b10000e829fbfe/content.htm

http://forum.wordreference.com/showthread.php?t=1875166

Don't conflate software implementation with accounting. A reasonable implementation can get by with minimal locking. That doesn't mean anything, however. You may have earned a rebuke because the software is (a) badly designed and (b) requires careful locking because of poor design.

What are the possible issues...?

Since we don't know how well or how poorly the software is written, it's impossible to guess. They may know something about their system that you didn't know.

A simple storno transaction system should be easy to implement. Indeed, it should be trivial.

A pair of "insert-only" tables can still encounter deadlocks if page-level locking is used during the inserts.

Table A, page 1 has an insert in transaction X.

Table B, page 2 has an insert in transaction Y.

Table B, page 2 has an insert in transaction X.

Table A, page 1 has an insert in transaction Y.

The only way to avoid deadlocks is to have the entire system use a single table. Or have all transactions limited to a single table. Or use a single database-wide lock.

If you have multiple-table operations (and page-level locking) then you will still have potential deadlocks even with insert-only operations. Clearly it's rare, but still possible.

S.Lott