Hello Steve,

Wednesday, July 10, 2002, 8:39:43 PM, you wrote:

SH> ========================= SH> BRIEF PROBLEM DESCRIPTION SH> =========================

SH> I have a problem simultaneously running two instances of a Perl program SH> that rapidly repeats a transaction which INSERT's a row into a BDB table SH> with an AUTO_INCREMENT column.

SH> No value is specified for the AUTO_INCREMENT column: it is left up to SH> the database to choose the "next" value. After a large (but variable) SH> number of iterations it apparently chooses the value "1", and then gives SH> the error:

SH> Duplicate entry '1' for key 1

SH> when trying to COMMIT the transaction because the value "1" has already SH> been used.

Thank you for your bug report. We will take a look it and see if there is possibility to fix this for BDB tables.

Currently I could propose you to use INNODB tables as work around. They also support transactions, as well as have many other features.

SH> Very often a deadlock error (number 1213) occurs with the instruction SH> to retry the transaction -- the program above therefore detects that SH> error and retries the transaction as instructed. I don't understand why SH> this program should give deadlock errors - it doesn't obviously suffer SH> from any of the usual reasons for deadlock (cycle deadlock, conversion SH> deadlock or thread deadlock) - and it never gives deadlock errors when SH> run on a Sybase database system on the same machine.

This is well known issue with BDB tables. BDB tables use page level locking which is known to produce more deadlock errors compared to row level locking. This is one more reason to use INNODB tables as they have row level locking + consistent reads and so you are likely not to get that many deadlock errors, if any.