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

"Volker Hetzer" <volker.hetzer_at_ieee.org> wrote:
> "Tom Ivar Helbekkmo" <tih+nr_at_eunetnorge.no> schrieb im Newsbeitrag
> news:86y8xq3qdu.fsf_at_athene.i.eunet.no...
> > "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:

> But shouldn't the one that *started* first win?

That depends on what the meaning of "should" is.

> I mean, that's what a
> transaction is for, isn't it?

No. A transaction is to create the illusion of instantaneous changes and serialization. If transactions conflict, all but one of them must roll back. Which one rolls back may have practical performance impact, but is immaterial to the logical nature of transactions.

> I want to do something that takes time and
> lots of different queries and therefore I enclose it in a transaction. I
> don't
> want the transaction disturbed by someone else.

Then kick everyone else off the system. Twenty bucks, same as in town.

> Now, in your system,
> transactions aren't isolated because a commit in another transaction can
> abort my own transaction evan if I "was there first".

Hey, they commited first. Since the entire transaction is deemed to occur at the instance of the commit (update of timestamp columns not withstanding), it sounds like they were there "first".

Many times I've wished for this writers-don't-block-writers feature in Oracle (when a low priority long running transaction causes lots of important small transactions to block). But if I had it, the opposite problem would make me wish for blocking, I'm sure.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB