美文网首页
MySQL事务中修改的实验

MySQL事务中修改的实验

作者: MacDonald | 来源:发表于2019-08-17 11:44 被阅读0次

    如果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事务中修改的实验

          本文链接:https://www.haomeiwen.com/subject/vbmsaqtx.html