UPDATE account SET balance = 2700 WHERE account_id = 2;
UPDATE accountSET balance = 2000WHERE account_id = 2;
SELECT * FROM account;+----+------------+---------+| id | account_id | balance |+----+------------+---------+| 1 | 1 | 1500 || 4 | 2 | 2000 |+----+------------+---------+
Time Line
Transaction A\"Transfer 200 from 1 to 2\"
locked
UPDATE account SET balance = 2700 WHERE account_id = 2;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
SELECT * FROM account;+----+------------+---------+| id | account_id | balance |+----+------------+---------+| 1 | 1 | 1000 || 4 | 2 | 2500 |+----+------------+---------+
blocking
lock account_id 2
UPDATE accountSET balance = 800WHERE account_id = 1;
lock account_id 1
Transaction B\"Transfer 500 from 2 to 1\"
START TRANSACTION;
SELECT * FROM account;+----+------------+---------+| id | account_id | balance |+----+------------+---------+| 1 | 1 | 800 || 4 | 2 | 2700 |+----+------------+---------+
COMMIT;
unlock all
UPDATE accountSET balance = 1500WHERE account_id = 1;
deadlock solved
