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

疑问

我们一行行加 X+Gap 锁，那没加行锁时数据会不会产生影响？还是先加表锁，等对所有行加 X+Gap锁后再释放表锁？

实验

为了验证，我将存储过程改了一下，让其处理大数据量操作，然后再进行实验

实验一

确定表锁的样子

1. 确认隔离级别

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


2. 会话一：锁表

   lock table test.t1 read;


3. 查询数据字典

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

1. 先创建一张表，并插入测试数据，为了让检索尽可能慢，此处数据有点多，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 select count(*) from performance_schema.data_locks;


   

2. 会话二：此时行锁并没有覆盖所有数据，那此时是否可以修改数据呢？（没想到修改成功了）

   update t1 set c='Data000002' where id=2999999;


3. 会话三：查看是否有锁表（既然能修改，应该是没有）

   • metadata_locks：验证是否有表锁

     mysql> select * from performance_schema.metadata_locks\G;


     

4. 会话一：COMMIT 数据后，验证是否会出现幻读

   select * from t1 where a='Data2999999';


   

实验二

rollback 的结果又会是怎样的呢？此处直接上图展示结果，就不再赘述实验过程了

实验三

RC 的结果差异如下，不出意外，也出现了幻读

• data_locks

  mysql> select * from performance_schema.data_locks;


  
  

• 依旧验证是否会出现幻读

  select * from t1 where a='Data2999999';


  

实验结论

4. RC 下 MySQL 锁表的方式是查找哪行，在哪行上加 X no gap 锁，如果符合检索项，则不释放。不符合，则释放掉该锁，继续检索下一条数据。此过程如果在未检索到之前用索引的方式修改数据，则会被覆盖——有出现幻读的风险
5. RR 下 MySQL 锁表的方式是一行一行加 X+Gap 锁。此过程如果在未加锁之前用索引的方式修改数据，则会被覆盖——有出现幻读的风险

总结

那么刚刚看到的这种机制，会不会有问题呢？下面我们假设一个业务案例来进行说明。

此处举一个敏感的例子（钱），假设有一张记录借贷信息的汇总表，包含：

• 状态列（一般很少有在状态列上创建索引吧，因为过滤性差）
  • 1：借贷
  • 2：超期
  • 3：还完
• 用户主键列（一般会有创建索引，便于快速定位某用户的信息，返回相应结果，且过滤性极好）

下面我假设一下场景

1. 步骤一：我使用平台进行借贷操作，状态是 1
2. 步骤二：由于昨天是最后还款日，那么今天 0:00 会批量将状态 1 改成 2（超期），这是个批处理，将所有没还钱的状态都变更为超期
3. 步骤三：有个用户忽然想起来需要还钱，略迟于上面的操作去执行，走用户 id（有索引）。还完钱，1 应该改成 3（还完）
   按道理说，此时应该出现锁，告诉我后台在处理数据，晚些再进行操作。但结果是由于没有锁到这条数据，所以允许还款

最终结果：我刚还完款，状态没变成 3 反而变成 2 了（出现了幻读），而且由于超期，往上叠加了逾期的利息，而且还的钱还给我扣了~

所以从数据安全角度考虑，目前已经提交 MySQL Bug，不知道社区会如何看待这个机制