首页  流程图  详情

Transaction2

2019-08-24 19:46:13   1  举报





仅支持查看

可重复读

作者其他创作

大纲/内容

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