What are locking, deadlocking issues in financial operations?

Question

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?

Gael

"other languages?" Could you actually be specific? – Ecumenicity

Are you talking about this? richardatopenbravo.blogspot.com/2010/02/… – Ecumenicity

@S.Lott, Post your answer. I upvote it, since it is helpful to me. See my update in main post – Freightage

Answer 1

"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.

Ecumenicity

"Or use a single database-wide lock." Er, that's called a single user system. Sure, you've eleiminated deadlocks, but you've serialised all transactions. – Seward

... even with insert-only operations. Clearly it's rare, but still possible. It is not "rare". Inserts cause locks at various points: the heap; index pages; when rows need to be moved. When num of users increases, blocking locks and thus deadlocks increase. It is ordinary. – Seward

@PerformanceDBA: "SQL server will lock" is not the same as a deadlock. With a single lockable resource, deadlocks are logically impossible. – Ecumenicity

@PerformanceDBA: "Inserts cause locks at various points". True, but they rarely cause deadlock because insert transactions -- typically -- involve few resources and little contention for resources. – Ecumenicity

@S.Lott: I will restate. The only way to avoid deadlocks is to have the entire system use a single table. That is grossly incorrect, SQL server will lock (and therefore block, and therefore deadlock) just as much within on table. On a multi-user system, you've just ensured that the locks are close to gether (instead of spread across many tables) and thus guaranteed more blocking locks and deadlocks. Since you do not know anything about SQL Server locking, I suggest you (a) do not post re the subject and (b) avoid challenging those who do know. – Seward

@S.Lott: because insert transactions - typically - involve few resources and little contention for resources. Nonsense again. Evidently you have no idea what a transaction is. Insert is a statement (within an xact), not a transaction ... unless you have no transactions, which means no data integrity or security or Consistency. Hell, if you have locking problems, you just remove transactions. Hilarious. And Inserts do involve substantial resources. Read up on the subject before posting further. – Seward

@PerformanceDBA: With only one resource, deadlock is impossible. Deadlock requires multiple resources. That's a matter of definition. If the entire system is in a single table, the one-and-only-table will effectively serialize all locking. But without contention over resources, deadlock will be impossible. If you have a database system which has non-deterministic locking of multiple hidden resources, deadlock is theoretically possible. I would not use that system as an example of anything but problems caused by non-deterministic locking. – Ecumenicity

@PerformanceDBA: "Evidently you have no idea what a transaction is". Good to know. My 30 years experience with insert transactions is that they rarely deadlock. I'm sorry your insert transactions are so poorly behaved. I've noted that insert transactions usually contain mostly insert statements. And they tend to acquire resources in a dependable, deterministic order, and rarely deadlock. Clearly, your experience is different from mine. I'm not challenging you. My experience seems to be different from yours. I can't see why, but you sure are happy to point it out. Enjoy. – Ecumenicity

@S.Lott: The fact that, even after pointing out the above to you, which would invoke a capable person to be precise, you continue to talk about Insert Transactions, reinforces the point (already proved) that you are clueless about Transactions. I won't be engaging with that. Stop wasting peoples' time. – Seward

@PerformanceDBA: What? "you continue to talk about Insert Transactions " The question talks about "Storno" Accounting, which is largely based on insert transactions; usually one sometimes two insert statements. What is your point? How can we avoid talking about insert transactions (CRUD -- the C part)? What point are you trying to make? – Ecumenicity

@Slott: Er, you've missed the context, read the question again, it is (repeatedly) about locks, blocks, and deadlocks; secondly in an insert-only system. And you are posting 101K points of complete misinformation. Flipping back and forth under questioning has ruined your cred. See if you can differentiate Financial and SQL Transactions when you write. Stop wasting peoples' time. – Seward

@PerformanceDBA: Got it. Very clear. "Flipping back and forth under questioning has ruined your cred". Whatever that means, you're absolutely right. Thanks for clearing that up. It really helps a lot to clarify and correct the answer. I'll certainly take your advice to heart. – Ecumenicity