The applications are selecting the new key value, then updating it to
increment the counter.  They need to do it the other way round; increment,
then read.

The deadlock occurs because two sessions have read the same key value, so
both are entitled to read-consistency, and then one of them attempted to
update the counter and got blocked.  Being blocked isn't a problem, but when
the other session then tried to do its update it got blocked too.  Neither
can proceed without violating read-consistency, and so they are deadlocked.

The text of a paper I presented at CA World '96 called "Key Points About
Surrogate Keys" is available from the Resources section of our web site at
www.rationalcommerce.com.  It talks about this problem and many others, and
how to deal with them.  (It also, incidentally, argues *very* strongly
against using sequential key values, and shows an alternative.)

Not quite.  In a deadlock one of the *transactions* is aborted.  The session
is still there so all it has to do is re-try the whole transaction from the
beginning (i.e. re-do every update since its last successful
commit/rollback).

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"