As I said, I don't know anything about BDB, but I don't see how they could
possibly prevent chaos without locking both all table pages and all index
pages that are being modified by a transaction. And indeed, that would make
a deadlock possible for multi-phase updates on the same row and thereby
quite neatly explain the behaviour you're seeing.
Stupid question on the side (just being nosy): why do you need to generate a
unique char string from the primary key? I mean, what is the benefit when it
was unique already?
An ugly solution to your problem, by the way, might be to split the table
into two: if you don't really need the auto-incremented primary key for
SELECTs, you could move it to a separate table that contains only this one
field.
The flow would then be:
- insert row into generator table.
- grab newly created primary key (autoincrement) and generate a unique 32
byte char string based on it.
- insert data row with generated char string as primary key.
- commit/rollback
This would still give you the unique seed for the generation of your char
string, and the insert into the real table would be atomic again. Depends on
the needs of your application, of course, whether this is workable. And,
yes, from a design point of view it IS ugly.
cs
> -----Ursprüngliche Nachricht-----
> Von: Dana Powers [mailto:[EMAIL PROTECTED]]
> Gesendet: Sonntag, 16. September 2001 00:14
> An: Christian Sage
> Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Betreff: Re: BDB table error
>
>
> 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
