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

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> writes:

> time t0 tx1 selects name,address line1,address line 2,zip code from an
> address table for supplier id 1;
> time t0+1 tx2 selects name,address line1,address line 2,zip code from an
> address table for supplier id 1;
> time t0+2 tx1 updates name to the name of the correct supplier at that
> address.
> time t0+3 tx2 updates the address to the correct address of the supplier for
> the name that existed at t0+1
> time t1 tx and tx2 both commit. 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. Quoting SQL-1992:

      The execution of a <rollback statement> may be initiated
      implicitly by an implementation when it detects the inability to
      guarantee the serializability of two or more concurrent
      SQL-transactions. When this error occurs, an exception condition
      is raised: transaction rollback-serialization failure.

This is "transaction isolation level serializable" in SQL-1992, and is specified there to be the default level.

-tih

-- 
Tom Ivar Helbekkmo, Senior System Administrator, EUnet Norway
www.eunet.no  T: +47-22092958 M: +47-93013940 F: +47-22092901