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

Tom Ivar Helbekkmo <tih+nr_at_eunetnorge.no> writes:

>>By removing the row locking mechanism you have managed to create an
>>app that allows bad data to be corrected in two opposite ways by two
>>different keyboard operators. Which transaction does the new
>>transaction manager rollback?
>
>Whichever one commits last. This is the trade-off: your application
>has to be prepared for this failure mode.

FWIW, there is an analogy here with version control systems. Under a system like RCS, you check out and lock a file, then check in your changes. If someone else wants to edit the file they wait for your lock to be released. Once you have a lock you're certain that nobody else will change the file in the meantime. Thus, the first person to begin a transaction (ask for the lock) wins.

In systems such as CVS, you edit a file without getting an exclusive lock, and when you commit there is a check that nobody else has changed it first. If they did, your commit fails and you must worry about how to merge the other person's changes with yours. So the first person to end a transaction (commit the file) wins.

The latter system provides more concurrency, but you must be prepared to have commits fail occasionally and have some way of reconciling conflicting changes. (CVS can try to merge changes into a working copy of a file, but this is just an aid to the user, and the final decision on what's committed remains with the user. Similarly a database server will not guess a way to merge conflicting changes to rows; it is up to the application to decide what recovery action to take.)

-- 
Ed Avis <ed_at_membled.com>