How to prevent Deadlock exception in SELECT FOR UPDATE after the update?

Question

I have an account table with 1 row. I have 2 threads which do the next:

1st thread:

begin transaction;
select * from account where balance=0 for update;
UPDATE account SET balance = 10 WHERE balance=0;
// waiting here for several seconds
commit transaction;

2nd thread:

begin transaction;
select * from account where balance=0 for update;
commit transaction;

The flow is the next:
1) 1st thread starts and proceeds up to the waiting here for several seconds line.
2) 2nd thread starts and is blocked (logically because the 1st thread has not released pessimistic locks yet).
3) 1st thread successfully commits a transaction.
4) 2nd thread gets an error: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Why Deadlock exception is getting in such scenario, if there is no deadlocks? I tried this scenario with all 4 transaction isolation levels and with all isolation levels the same error is acquired.

Use the innodb status monitor to find out what exactly causes the deadlock. — Shadow, Jan 13 '19 at 10:11
@Shadow thank you. I will try to monitor it and then I will update the question. — Oleksandr, Jan 13 '19 at 10:23

score 1 · Accepted Answer · answered Jan 13 '19 at 21:18

The problem was my mistake. So the above code actually should work. My problem was that I used lock in share mode in the first thread and in the second I used for update. I thought that they are the same, but actually they are not. When I changed the first thread to use for update also, they started to work correctly.

Thanks to Shadow who gave a tip to use innodb status monitor to check deadlock transactions information (SHOW ENGINE INNODB STATUS;).

How to prevent Deadlock exception in SELECT FOR UPDATE after the update?

1 Answers1