Reputation: 3764
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.
Upvotes: 0
Views: 1044
Reputation: 3764
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;
).
Upvotes: 2