Explain deadlock in mysql

Question

I am new to mysql. I encountered a deadlock. Please help to explain.

I created a table:

create table test(id INT, value INT, PRIMARY KEY(id));
insert into test(id, value) values(0, 0);
insert into test(id, value) values(1, 1);

In transaction 1:

begin;
select * from test where id=1 for update; //it holds record_lock(id=1)

In transaction 2:

begin;
select * from test where id=1 for update; //it waits for record_lock(id=1)

Then in transaction 1:

select * from test where id>0 for update;

After this statement in transaction 1, transaction 2 got error:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

This is latest detected deadlock:

Answer 1

After reading this example from MySQL, I know the reason:

1) Transaction 01 (T1):

begin;
select * from test where id=1 for update; //it holds record_lock(id=1)

After executing that statement, T1 holds record lock (id=1)

2) Transaction 02 (T2):

begin;
select * from test where id=1 for update; //it waits for record_lock(id=1)

T2 is put into waiting queue because it is trying to acquire the lock that is held by T1.

3) Transaction 01:

select * from test where id>0 for update;`enter code here`

This statement is trying to acquire gap lock (from 1 to infinity), but T2 is waiting record-lock(id=1) in the queue, so it should wait for T2. Deadlock happens. Even T1 has record-lock(id=1), it can't even get this gap lock as T2 is waiting in queue.