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

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

概念

锁的类型

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

兼容性

疑问

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

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

实验

准备

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

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

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


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

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


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

   • 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 下有什么不同

1. 修改隔离级别

   set global transaction_isolation='read-committed';


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

2. 确认隔离级别

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


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

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


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

   • 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;


     

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

   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 锁来达到锁表的目的，以保证不可虚读。