>>>>> "Dana" == Dana Powers <dana@stripped> writes:
Dana> Ok, so the problem Im seeing is that any table that has an index other than
Dana> the primary key is prone to deadlocks.
Dana> I cant think of any reason that this is acceptable behaviour ( i.e. not a
Dana> bug ), because I am doing 4 atomic operations on a single table with no user
Dana> level locks.
In BDB the secondary indexes are not maintained directly in BDB, but
MySQL has to handle them itself. That means that there is a possible
race condition when updating indexes at the same time from many
Dana> Here is a test:
Dana> CREATE TABLE bdb_test ( id int unsigned not null auto_increment primary key,
Dana> key1 char(32) not null, val1 char(32), unique (key1) ) TYPE=BDB;
Dana> ( replacing unique(key1) with index(key1) also results in deadlocks ).
Dana> Now run the following 4 queries in parallel:
Dana> ## Insert a new row with 2 key values - 1 auto_incremented and 1 specified
Dana> INSERT INTO bdb_test (key1) VALUES(CONCAT(CONNECTION_ID(),' - some extra
Dana> ## Update a non key column in row
Dana> UPDATE bdb_test SET val1='test val' WHERE id=LAST_INSERT_ID();
Dana> ## Select based on secondary key
Dana> SELECT * FROM bdb_test WHERE key1=CONCAT(CONNECTION_ID(),' - some extra
Dana> ## Select based on primary key
Dana> SELECT * FROM bdb_test WHERE id=LAST_INSERT_ID();
Dana> I get deadlocks on my machine ( Intel w/ Dual 400 + 512MB RAM, running
Dana> linux ) with 64 simultaneous threads.
For which statements do you get errors ?
Dana> It appears that as the table gets larger ( and more pages created for
Dana> locking, hehe ), the probability that we get a deadlock grows.
Normally when the table grows, page locks conflicts should be more
uncommon. The problem in your case is that for 3 of 4 of your
statements (all except the 'select based on secondary key', you will
create a lock on the last block for the primary index; In other words
all threads are trying to get a lock on the same key block.
Another problem is that for every update of type:
UPDATE bdb_test SET val1='test val' WHERE id=LAST_INSERT_ID()
You are hitting and locking the same block in the val1 index.
Page locks or row locks will never work good if you are always hitting
the same block. If you can't change your application to not do this,
then I suggest you to try InnoDB tables instead.