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

Actually this reminds me of a similar scenario that can happen on Oracle:

If you set the transaction isolation level to serializable, and then try to update or delete any row that's modified by another transaction that commits after your transaction started, you'll get the error ORA-08177:   "Cannot serialize access for this transaction".

Tom Kyte defines the isolation property of ACID as "The effects of a transaction may not be visible to other transactions *until the transaction has committed*." So if a transaction is aborted because of a commit of another transaction, strictly speaking, because the other transaction has committed, this does not necessarily violate the isolation principle.

Cheers,
Dave

Brian Peasland wrote:
> If a transaction is aborted simply because of a commit of another
> transaction, then one of the four components of the ACID properties,
> Isolated, for transactions is lost. Transactions no longer appear to be
> isolated in the system. Other transactions are now responsible for the
> success or failure of your transaction. And this is supposed to be
> "better" than locking? Personally, I'd rather wait for a lock to be
> released and be sure that my transaction is going to finish than to be
> killed just because someone else committed first. If I have a long
> running transaction and there are other short running, conflicting
> transactions that will always be present, then my long running
> transaction *never* has a chance to commit. My transaction will *always*
> be killed by these shorter transactions that commit before I am ready to
> commit.
>
> Cheers,
> Brian
>
>

>>As you can see, transactions are also aborted, if there are several
>>older versions of data entries from other transactions. Don't forget,
>>all data have versions, which are assigned to their corresponding
>>transaction.
>>You see, it's more complex, than you think, but works without locks and
>>without hurting integrity. Don't misunderstand my expression "time -
>>shift" - all queries, inserts, updates run at the same time and work
>>with their own snapshot of data, their versions. But - the transaction
>>manager after some time looks at all timestamps of all data changed,
>>looks at their corresponding transaction timestamps and decides, what
>>data has finally to be written into the database, what versions of data
>>can be dropped, because of several overwrites .... this operation is
>>time - shifted, the cleanup of all versions, timestamps data. whenever a
>>transaction has finished, the transaction manager cleans up and writes
>>final data into database file.
>>To your question: If data becomes "out of sync", the transaction is
>>simply aborted. It makes no sense to update a dataset, which has changed
>>in the mean time 3 times, after having read the data. On the other hand,
>>  lost updates cannot occur, because the transaction manager has control
>>over the versions of all datasets.