deadlock problems....???

[Marie]

Hi,
I am looking for the "cause" behind a deadlock which occur quite frequently. It can be reproduced in the following manner:

CREATE TABLE test (
id bigint(20) NOT NULL auto_increment,
id2 bigint(20) NOT NULL default 0,
str varchar(255) default NULL,
PRIMARY KEY (id),
KEY id2 (id2)
) ;

Insert few records:
INSERT INTO test (id2, str) VALUES (1, 'tim');
INSERT INTO test (id2, str) VALUES (2, 'tom');
INSERT INTO test (id2, str) VALUES (3, 'jim');
INSERT INTO test (id2, str) VALUES (4, 'neo');
INSERT INTO test (id2, str) VALUES (5, 'ken');

Consider two threads T1 and T2 performing the following operations in the sequence specified below:

T1: BEGIN;

T2: BEGIN;

T1: DELETE FROM test WHERE id2 = 2;

T2: DELETE FROM test WHERE id2 = 3;

T1: INSERT INTO test (id2, st -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[Beau]

the two delete statement causes exclusive next-key lock on index id2.
when tx1 want to insert, it needs shared next-key lock, which is held by tx2, so tx1 hang;
when tx2 want to insert, it needs shared next-key lock too, which is held by tx1, so dead lock, tx2 rollback

try to remove the next-key locks with the my.cnf option:

innodb_locks_unsafe_for_binlog

also, read the caveats in the manual. -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --