MySQL：关于 RR 的一些实验及拓展（二）

原创 Ryan Bai 2024-05-08

119

从简单的增删改查角度，了解了一下锁，那么从视图角度是怎样的呢？

概念

锁的类型

共享锁（S Lock）：允许拥有共享锁的事务读取该行行数据。当一个事务拥有一行的共享锁时，另外的事务可以在同一行数据也获得共享锁，但另外
的事务无法获得同一行数据上的排他锁
排他锁（X Lock）：允许拥有排它锁的事务修改或删除该行数据。当一个事务拥有一行的排他锁时，另外的事务在此行数据上无法获得共享锁和排它锁，只能等待第一个事务的锁释放
除了共享锁和排他锁之外， InnoDB 也支持意图锁。该锁类型是属于表级锁，表明事务在后期会对该表的行施加共享锁或者排它锁。
- 意向共享锁（IS Lock）：事务想要获得一张表中某几行的共享锁
- 意向排他锁（IX Lock）：事务想要获得一张表中某几行的排他锁

兼容性

	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

疑问

RR 锁有没有可能真的像其中一位大佬分析的那样，是行锁？（附上图，便于说明）
那 5 行数据查到的锁数据会不会也出现 5 行？（5 行是为了便于观察结果）

带着这些疑问，我做了如下实验

实验

准备

下面我做几个实验，从各个角度来分析一下 RR 和锁

先创建一张表，并插入测试数据

create table t1 (id int auto_increment,
    a varchar(10),
    b varchar(10),
    c varchar(10),
    primary key(id)
)  engine=INNODB  default charset=utf8;


DELIMITER $$
 
CREATE PROCEDURE InsertRows()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 5 DO
    INSERT INTO t1 (id, a, b, c)
    VALUES (i, CONCAT('Data', i), CONCAT('Data', i), CONCAT('Data', i));
    SET i = i + 1;
  END WHILE;
END$$
 
DELIMITER ;
 
CALL InsertRows();

实验一

修改隔离级别

set global transaction_isolation='repeatable-read';

然后所有会话需要重新登录

确认隔离级别

mysql> show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

会话一：开启事务，修改一条数据

begin;
update t1 set c='Data11' where a='Data5';

会话二：查看各个视图情况

table_handles

mysql> select * from performance_schema.table_handles\G;
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: t1
OBJECT_INSTANCE_BEGIN: 139946276421760
      OWNER_THREAD_ID: NULL
       OWNER_EVENT_ID: NULL
        INTERNAL_LOCK: NULL
        EXTERNAL_LOCK: NULL
1 row in set (0.00 sec)

metadata_locks

mysql> select * from performance_schema.metadata_locks\G;
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: t1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139946276255856
            LOCK_TYPE: SHARED_WRITE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6093
      OWNER_THREAD_ID: 48
       OWNER_EVENT_ID: 5057
*************************** 2. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139946085863232
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6093
      OWNER_THREAD_ID: 50
       OWNER_EVENT_ID: 38
2 rows in set (0.00 sec)

INNODB_TRX

mysql> select * from information_schema.INNODB_TRX\G;
*************************** 1. row ***************************
                    trx_id: 3893
                 trx_state: RUNNING
               trx_started: 2024-05-07 11:22:15
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 8
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 6
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.00 sec)

data_locks

mysql> select * from performance_schema.data_locks;

结论：从上面截图可以知道，对于表的确会生成表 IX 锁，也会生成 5 行的行锁。还加了基数据锁，以防止有人对表结构做修改。

疑问：那么锁表的操作是 X 行锁还是 IX 表锁呢？

实验二

我们再来对比一下 RC 下和 RR 下有什么不同

修改隔离级别

set global transaction_isolation='read-committed';

然后所有会话需要重新登录

确认隔离级别

mysql> show variables like '%iso%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

会话一：开启事务，修改一条数据

begin;
update t1 set c='Data11' where a='Data5';

会话二：查看各个视图情况

table_handles

mysql> select * from performance_schema.table_handles\G;
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: t1
OBJECT_INSTANCE_BEGIN: 140324434622688
      OWNER_THREAD_ID: NULL
       OWNER_EVENT_ID: NULL
        INTERNAL_LOCK: NULL
        EXTERNAL_LOCK: NULL
*************************** 2. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: t1
OBJECT_INSTANCE_BEGIN: 140324434430400
      OWNER_THREAD_ID: NULL
       OWNER_EVENT_ID: NULL
        INTERNAL_LOCK: NULL
        EXTERNAL_LOCK: NULL
2 rows in set (0.00 sec)

metadata_locks

mysql> select * from performance_schema.metadata_locks\G;
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140324500471696
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6093
      OWNER_THREAD_ID: 50
       OWNER_EVENT_ID: 4
*************************** 2. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: t1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140324434446240
            LOCK_TYPE: SHARED_WRITE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6093
      OWNER_THREAD_ID: 49
       OWNER_EVENT_ID: 15
2 rows in set (0.00 sec)

INNODB_TRX

mysql> select * from information_schema.INNODB_TRX\G;
*************************** 1. row ***************************
                    trx_id: 4369
                 trx_state: RUNNING
               trx_started: 2024-05-07 11:40:55
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 9
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.00 sec)

data_locks

mysql> select * from performance_schema.data_locks;

为了证明锁是表锁，我修改了一条数据，尴尬的是，修改成功。

mysql> update t1 set c='Data11' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

结论：RC 下，对于表也会生成表 IX 锁和单行的无 GAP 行锁；还有基数据锁，依旧是为了防止有人对表结构做修改。

第一行非表排他锁，而是表意向排他，起到排他作用的锁是 X，整体看 RR 下是全表数据的 行排他+GAP锁 起到了表锁的现象。

总结

整体看，RC 环境下不会出现锁表的情况，而 RR 环境下的锁会更严一些。对于 UPDATE，会在 RC 的基础上对全表每一条数据增加 X 锁和 GAP 锁来达到锁表的目的，以保证不可虚读。

mysql mysql事务

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

MySQL：关于 RR 的一些实验及拓展（二）

概念

锁的类型

疑问

实验

准备

实验一

实验二

总结

评论