InnoDB Deadlock behavior when selecting by primary key for update

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

In both mysql version 4.0.16 and 4.1.8 I'm running into deadlock
behavior that I don't understand: can anyone explain why the following
happens?  Heres a basic table definition that exhibits the behavior and
some data I inserted:

create table deadlock_test (
  id integer not null,
  primary key(id)
) type=innodb;

insert into deadlock_test values(0);
insert into deadlock_test values(1);
insert into deadlock_test values(2);
insert into deadlock_test values(3);
insert into deadlock_test values(4);
insert into deadlock_test values(5);

Then, I start two transactions, and type the following in this order:
<transaction 1> select id from deadlock_test where id = 0 for update;
<transaction 2> select id from deadlock_test where id = 0 for update;
<transaction 1> select id from deadlock_test for update;

This causes a deadlock.  However, when id is not a primary key, it does
not deadlock.

- Steven

Site Timeline