Dana,
if you have page level locking, you can easily
get a deadlock from an atomic insert done as
a single transaction, and another user doing
an atomic select in another single transaction.
It happens this way:
User A User B
insert record select record from secondary
to primary index index, get S-lock on page 2
get X-lock on page 1
select record from primary index
get X-lock on page 1 -> MUST WAIT
insert record to
secondary index
get X-lock on page 2 ->MUST WAIT -> DEADLOCK
Regards,
Heikki
http://www.innodb.com
>Ok, so the problem Im seeing is that any table that has an index other than
>the primary key is prone to deadlocks.
>I cant think of any reason that this is acceptable behaviour ( i.e. not a
>bug ), because I am doing 4 atomic operations on a single table with no user
>level locks.Here is a test:
>CREATE TABLE bdb_test ( id int unsigned not null auto_increment primary key,
>key1 char(32) not null, val1 char(32), unique (key1) ) TYPE=BDB;
>( replacing unique(key1) with index(key1) also results in deadlocks ).
>Now run the following 4 queries in parallel:
>## Insert a new row with 2 key values - 1 auto_incremented and 1 specified
>INSERT INTO bdb_test (key1) VALUES(CONCAT(CONNECTION_ID(),' - some
extratext'));
>## Update a non key column in row
>UPDATE bdb_test SET val1='test val' WHERE id=LAST_INSERT_ID();
>## Select based on secondary key
>SELECT * FROM bdb_test WHERE key1=CONCAT(CONNECTION_ID(),' - some extratext');
>## Select based on primary keySELECT * FROM bdb_test WHERE id=LAST_INSERT_ID();
>I get deadlocks on my machine ( Intel w/ Dual 400 + 512MB RAM, running
>linux ) with 64 simultaneous threads.
>It appears that as the table gets larger ( and more pages created for
>locking, hehe ), the probability that we get a deadlock grows.
---------------------------------------------------------------------
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 <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
