从简单的增删改查角度，了解了一下锁，那么从视图角度是怎样的呢？
概念
锁的类型
兼容性
| 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 锁来达到锁表的目的，以保证不可虚读。
