Re: Transactions: good or bad?

"Todd Bandrowsky" <anakin_at_unitedsoftworks.com> wrote in message news:af3d9224.0305190428.5654175e_at_posting.google.com...
> > > Is deadlock a system failure?
>
> Writing a single var atomically is not enough. If you try to write
> 100 values atomically, like, you have a super update statement, you
> will still have deadlocks because all 100 rows will have to have read
> and write isolation internally.
>
> If you have, in your model, two competing UPDATE statements, and,
> there is an intersection of the rows they are updating, then, you can
> still have a deadlock.

This depends of course on how the implementation decides to serialise things. If you do true serialisation - only one update running at any one time, then unless you have user transactions in the equation you won't get deadlocks. A more efficient serialisation would be to not only allow two 'overlapping' updates to run simultaneously.

> The problem of deadlocks cannot be solved by assuming an atomic
> assignment of multiple values of once -

The database is a single value. A non-scalar value consisting of relation values (that consist of tuple values that consist of scalar values).

> atomic assignment of multiple
> values is exactly what a transaction is

Most people's idea of a transaction is of something that is not atomic over time.

If your idea of a transaction is an atomic-in-logical-time change to multiple values within the non-scalar database value then your statement holds.

>, and that causes deadlocks.

Nope, its either implementation optimisations (such as running two update statements in parallel and only serialising 'after the fact'), or open user transaction that can cause deadlocks.

> So, you have to have deadlock detection scheme in your code anyway.

Possibly, depending on the internal implementation code.

> It seems silly to have the users of your system use TWO deadlock
> detection schemes, the one you propose and the one you have to have.

It might seem silly, but it is infact a benefit because it is an example of independence. Your users are not dependent on internal deadlock detection schemes for their logical problem of deadlocks. The implementation can freely alter, tweak, tune or discard their deadlock detection schemes independently of any user visible scheme.

Your argument is one that goes directly against the principle of data independence.

> Of course, you can serialize based on TABLE - or do TABLE level
> locking, in otherwords, and that could make your atomic assignment
> work. But, congratulations, you've just rewritten MYSQL.

Assignments are to the database variable so one would need to serialise based on the whole database, not individual tables....

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Mon May 19 2003 - 17:46:03 CEST