MySQL事务中修改的实验

如果A或B在事务期间涉及到同一行的增删改，另一个事务都会等待。
现在有2行(1,2)，如果A修改了1，B修改了2。现在A要修改2(先提交)，B要修改1(后提交)，此时B会因为死锁直接报异常回滚，由A对1、2行做修改。

用户A：
mysql> update account set count = 800 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update account set count = 100 where id = 1;
Query OK, 1 row affected (2.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from account;
+----+-------+
| id | count |
+----+-------+
|  1 |   100 |
|  2 |   800 |
|  3 |  1000 |
+----+-------+
3 rows in set (0.00 sec)

用户B：
mysql> update account set count = 800 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+-------+
| id | count |
+----+-------+
|  1 |   800 |
|  2 |   800 |
|  3 |  1000 |
+----+-------+
3 rows in set (0.00 sec)

mysql> update account set count = 8000 where id = 2;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

MySQL事务中修改的实验

推荐阅读更多精彩内容