数据库锁实战三

绝地灬酷狼 2022-09-12 12:43 148阅读 0赞

# 一 表脚本 #

CREATE TABLE test_innodb_lock (
      a INT(11),
      b VARCHAR(16)
    )ENGINE=INNODB;
    INSERT INTO test_innodb_lock VALUES(1,'b2');
    INSERT INTO test_innodb_lock VALUES(3,'3');
    INSERT INTO test_innodb_lock VALUES(4, '4000');
    INSERT INTO test_innodb_lock VALUES(5,'5000');
    INSERT INTO test_innodb_lock VALUES(6, '6000');
    INSERT INTO test_innodb_lock VALUES(7,'7000');
    INSERT INTO test_innodb_lock VALUES(8, '8000');
    INSERT INTO test_innodb_lock VALUES(9,'9000');
    INSERT INTO test_innodb_lock VALUES(1,'b1');
    CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
    CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
    mysql> select * from test_innodb_lock;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | b2   |
    |    3 | 3    |
    |    4 | 4000 |
    |    5 | 5000 |
    |    6 | 6000 |
    |    7 | 7000 |
    |    8 | 8000 |
    |    9 | 9000 |
    |    1 | b1   |
    +------+------+
    9 rows in set (0.00 sec)

# 二 实战 #

# 在第一个客户端：关闭自动提交
    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)
    
    # 在第二个客户端：关闭自动提交
    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)
    
    # 在第一个客户端：更新但不提交，没有手写 commit;
    mysql> update test_innodb_lock set b='b1' where a = 4;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 2  Changed: 1  Warnings: 0
    
    # 在第一个客户端：查询 a=4，更新生效，体现出读己之所写
    mysql> select * from test_innodb_lock where a=4;
    +------+------+
    | a    | b    |
    +------+------+
    |    4 | b1   |
    +------+------+
    1 row in set (0.00 sec)
    
    # 在第二个客户端：查询 a=4，更新没生效，第一个客户端还没提交，所有看不到变化
    mysql> select * from test_innodb_lock where a=4;
    +------+------+
    | a    | b    |
    +------+------+
    |    4 | 4000 |
    +------+------+
    1 row in set (0.00 sec)
    
    # 在第一个客户端：执行提交
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    # 在第二个客户端:查询 a=4，还是看不到变化，因为客户端2设置了取消自动提交
    mysql> select * from test_innodb_lock where a=4;
    +------+------+
    | a    | b    |
    +------+------+
    |    4 | 4000 |
    +------+------+
    1 row in set (0.00 sec)
    
    # 在第二个客户端：提交
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    # 在第二个客户端: 看到变化了
    mysql> select * from test_innodb_lock where a=4;
    +------+------+
    | a    | b    |
    +------+------+
    |    4 | b1   |
    +------+------+
    1 row in set (0.00 sec)
    
    # 在第三个客户端：不取消自动提交，查询 a=4，这里能看到变化，这就是取消自动提交和不取消自动提交的区别
    mysql> select * from test_innodb_lock where a=4;
    +------+------+
    | a    | b    |
    +------+------+
    |    4 | b1   |
    +------+------+
    1 row in set (0.00 sec)
    
    # 在第一个客户端：对 a=4 执行修改操作
    mysql> update test_innodb_lock set b='4002' where a = 4;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    # 在第二个客户端：对 a=4 也执行修改操作，发生阻塞
    mysql> update test_innodb_lock set b='4003' where a = 4;
    
    # 在第一个客户端：提交
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    # 在第二个客户端：阻塞解除
    mysql> update test_innodb_lock set b='4003' where a = 4;
    Query OK, 1 row affected (44.15 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    # 在第二个客户端：提交
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    # 在第一个客户端：查询 a = 4，显示的第二个客户端的修改值，因为它是后提交的
    mysql> select * from test_innodb_lock where a=4;
    +------+------+
    | a    | b    |
    +------+------+
    |    4 | 4003 |
    +------+------+
    1 row in set (0.00 sec)
    
    # 在第二个客户端：查询 a = 4，显示的第二个客户端的修改值，因为它是后提交的
    mysql> select * from test_innodb_lock where a=4;
    +------+------+
    | a    | b    |
    +------+------+
    |    4 | 4003 |
    +------+------+
    1 row in set (0.00 sec)
    
    # 在第一个客户端：更新 a = 4
    mysql> update test_innodb_lock set b='4005' where a = 4;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    # 在第二个客户端：更新 a = 9，更新成功，不会阻塞，因为更新的是不同的行，大路朝天，各走一边
    mysql> update test_innodb_lock set b='9000' where a = 9;
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 1  Changed: 0  Warnings: 0
    
    # 在第一个客户端：提交
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    # 在第二个客户端：提交
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    # 在第一个客户端：查询 a =4 和a=9，查询正确
    mysql> select * from test_innodb_lock where a in (4,9);
    +------+------+
    | a    | b    |
    +------+------+
    |    4 | 4005 |
    |    9 | 9000 |
    +------+------+
    2 rows in set (0.00 sec)
    
    # 在第二个客户端：查询 a =4 和a=9，查询正确
    mysql> select * from test_innodb_lock where a in (4,9);
    +------+------+
    | a    | b    |
    +------+------+
    |    4 | 4005 |
    |    9 | 9000 |
    +------+------+
    2 rows in set (0.00 sec)

# 三 行锁定基本演示 #

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16][]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/dd4da3fdb5c841c4b360cae870ab7ba8.png