如何锁定一行

灰太狼 2022-09-12 12:45 137阅读 0赞

# 一 锁定方法 #

# ![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16][] 二 实战 #

# 原始数据
    mysql> select * from test_innodb_lock;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | b1   |
    |    3 | mm   |
    |    5 | mm   |
    |    6 | 6000 |
    |    7 | 7000 |
    |    8 | 8000 |
    |    9 | 9001 |
    |    1 | b1   |
    |    2 | 2000 |
    +------+------+
    9 rows in set (0.00 sec)
    
    # 第一个客户端：锁 a = 8 这一行
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from test_innodb_lock where a = 8 for update;
    +------+------+
    | a    | b    |
    +------+------+
    |    8 | 8000 |
    +------+------+
    1 row in set (0.00 sec)
    
    # 第二个客户端：执行对 a = 8 的更新，发生阻塞
    mysql> update test_innodb_lock set b='YYY' where a = 8;
    
    # 第一个客户端：执行对 a = 8 的更新，操作成功
    mysql> update test_innodb_lock set b='zzz' where a = 8;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    # 第一个客户端：提交
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    # 第二个客户端：阻塞解除
    mysql> update test_innodb_lock set b='YYY' where a = 8;
    Query OK, 1 row affected (11.96 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    # 第二个客户端：提交
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    # 在两个客户端分别查询数据，最终 b 的数据是 YYY
    mysql> select * from test_innodb_lock;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | b1   |
    |    3 | mm   |
    |    5 | mm   |
    |    6 | 6000 |
    |    7 | 7000 |
    |    8 | YYY  |
    |    9 | 9001 |
    |    1 | b1   |
    |    2 | 2000 |
    +------+------+
    9 rows in set (0.00 sec)

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/2f12204b263f41d7b36dd17fa21e64bb.png