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.
Thanks,
- Steven