疑问
我们一行行加 X+Gap 锁，那没加行锁时数据会不会产生影响？还是先加表锁，等对所有行加 X+Gap锁后再释放表锁？
实验
为了验证，我将存储过程改了一下，让其处理大数据量操作，然后再进行实验
实验一
确定表锁的样子
-
确认隔离级别
mysql> show variables like '%iso%'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec)
-
会话一：锁表
lock table test.t1 read;
-
查询数据字典
-
innodb_locks
select * from performance_schema.metadata_locks
-
data_locks
mysql> select * from performance_schema.data_locks; Empty set (0.00 sec)
结论：表锁只能在 metadata_locks 中查询到，此处可以看到是只允许只读操作，之前锁没释放时如下，此处贴出来用于对比
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)
data_locks
mysql> select * from performance_schema.data_locks;
重新准备数据
下面我做个实验，看看在长时间修改时，会是什么情况，由于此处也有修改，就不查询 data_locks 了，只查看 metadata_locks 表
-
先创建一张表，并插入测试数据，为了让检索尽可能慢，此处数据有点多，100 亿行。
drop table t1;create table t1 (id int auto_increment, a varchar(50), b varchar(50), c varchar(50), primary key(id) ) engine=INNODB default charset=utf8;drop PROCEDURE InsertRows; DELIMITER $$ CREATE PROCEDURE InsertRows()BEGIN DECLARE i INT DEFAULT 1; WHILE i <= 10000000000 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();
实验二
-
会话一：开启事务，修改中间的一条数据，此处先执行，应该改两条数据，即 id=1 和 2999999（为了让操作真正达到全表扫描的目的，而非只针对某个内存块修改）
update t1 set a='Data2999999' where id=1;update t1 set c='Data000001' where a='Data2999999';
-
会话三：查看视图情况
data_locks：验证是否加锁（由于数量比较多，所以只检索了数据锁的总量，和 t1 表数据量进行对比）
mysql> select count(*) from performance_schema.data_locks;
-
会话二：此时行锁并没有覆盖所有数据，那此时是否可以修改数据呢？（没想到修改成功了）
update t1 set c='Data000002' where id=2999999;
-
会话三：查看是否有锁表（既然能修改，应该是没有）
-
metadata_locks：验证是否有表锁
mysql> select * from performance_schema.metadata_locks\G;
-
会话一：COMMIT 数据后，验证是否会出现幻读
select * from t1 where a='Data2999999';
实验二
rollback 的结果又会是怎样的呢？此处直接上图展示结果，就不再赘述实验过程了
实验三
RC 的结果差异如下，不出意外，也出现了幻读
data_locks
mysql> select * from performance_schema.data_locks;
依旧验证是否会出现幻读
select * from t1 where a='Data2999999';
实验结论
- RC 下 MySQL 锁表的方式是查找哪行，在哪行上加 X no gap 锁，如果符合检索项，则不释放。不符合，则释放掉该锁，继续检索下一条数据。此过程如果在未检索到之前用索引的方式修改数据，则会被覆盖——有出现幻读的风险
- RR 下 MySQL 锁表的方式是一行一行加 X+Gap 锁。此过程如果在未加锁之前用索引的方式修改数据，则会被覆盖——有出现幻读的风险
总结
那么刚刚看到的这种机制，会不会有问题呢？下面我们假设一个业务案例来进行说明。
此处举一个敏感的例子（钱），假设有一张记录借贷信息的汇总表，包含：
下面我假设一下场景
- 步骤一：我使用平台进行借贷操作，状态是 1
- 步骤二：由于昨天是最后还款日，那么今天 0:00 会批量将状态 1 改成 2（超期），这是个批处理，将所有没还钱的状态都变更为超期
- 步骤三：有个用户忽然想起来需要还钱，略迟于上面的操作去执行，走用户 id（有索引）。还完钱，1 应该改成 3（还完） 按道理说，此时应该出现锁，告诉我后台在处理数据，晚些再进行操作。但结果是由于没有锁到这条数据，所以允许还款
最终结果：我刚还完款，状态没变成 3 反而变成 2 了（出现了幻读），而且由于超期，往上叠加了逾期的利息，而且还的钱还给我扣了~
所以从数据安全角度考虑，目前已经提交 MySQL Bug，不知道社区会如何看待这个机制
