Re: Avoiding any locks in SQL Servers - read and understand....itsmagic.

wizofoz2k_at_yahoo.com.au (Noons) writes:

>>If a deadlock is detected, I can't see any option particularly
>>better than rolling the transaction back, and having the client try
>>again later.

>The problem I have is if all this "non-locking" on write takes place
>under the "serialization" of a supervisor, what happens when one of
>those serialised ones is the object of a deadlock? What exactly gets
>rolled back and in which sequence. Worries me as no matter what
>scenario I try, without locking there WILL be a loss of data.

There will not be a loss of data because the database tells the application that the transaction has been rolled back. Any non-buggy application will not just ask the database to commit, hope for the best and walk away; it must ask to commit the transaction and then get the reply from the database saying 'yep, committed successfully'. This is true whatever the locking mechanism. Suppose the database were put into read-only mode for maintenance while a transaction were open, for example - is it good application design to ignore the failure to commit?

When you ask a database to commit the transaction it can do two things. It can write the data permanently, all of it, and reply saying that the commit was successful. Or it can roll back the transaction, so that none of the changes are applied, and reply that the transaction was rolled back. In neither case is there a loss of data.

If it is difficult to write your applications to recover cleanly from commit failures, you might prefer to use a locking strategy that reduces the number of such failures even if it means a lower throughput and more time waiting for a lock during the transaction. But even with the most simple locking strategy and the one least likely to suffer conflicts (that is, allowing only one transaction to execute at a time) - even then, you still have some possibility that a transaction will be rolled back. But it is not data loss as long as the database honestly reports that the transaction failed.

-- 
Ed Avis <ed_at_membled.com>