I can totally understand that, but the problem is, my queries are only
interested in one row each ( accessed by primary key ). Perhaps it has to do
with the extra unique index?
The flow is this:
insert row.
grab newly created primary key ( autoincrement ) + generate a unique 32 byte
char string based on the primary key.
update row to set unique char string.
commit | rollback
Could it be that bdb needs to grab a page lock on the index as well? hmm,
that might explain it.
dpk
----- Original Message -----
From: "Christian Sage" <[EMAIL PROTECTED]>
To: "Dana Powers" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Saturday, September 15, 2001 3:08 PM
Subject: AW: BDB table error
Dana,
a deadlock can easily occur on a single table with row-level (or page-level)
locking. What happens is something like the following:
connection has lock on wants lock on
alpha object A object B
beta object B object A
With page-level locking this would obviously be possible to happen only if
the objects resided in different pages. Anyway, this type of situation
cannot be resolved by the connections on their own, because they both see
only their own context and end up sitting there indefinitely waiting for the
object they want to be freed. Therefore, it must be handled by either the
application code or the rdbms itself.
Some of the other database systems I know detect this situation on their
own. Oracle, for example, will roll back one of the contending connections
and write a trace file plus an entry in its alert log (for an ORA-00060
error). Sadly, I don't know anything about BDB, so I can't really help you.
Generally speaking I've not yet met a situation where it was necessary to
sequentially lock several objects on the same table, though. I may be wrong,
but as far as I can see this would seem to point at either loose design (not
fully normalized - if the data is normalized you simply go and lock the
(single) parent object, then all child objects of this parent are implicitly
locked if all connections behave in the same way) or at sub-optimal coding
(atomicity of operations should have been preserved). No offense intended,
as I say, I may be totally off the beam here.
Cheers,
Christian Sage
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
