疑问

我们一行行加 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 <= 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();

实验二

1. 会话一：开启事务，修改中间的一条数据，此处先执行，应该改两条数据，即 id=1 和 2999999（为了让操作真正达到全表扫描的目的，而非只针对某个内存块修改）

   update t1 set a='Data2999999' where id=1;update t1 set c='Data000001' where a='Data2999999';

2. 会话三：查看视图情况

   data_locks：验证是否加锁（由于数量比较多，所以只检索了数据锁的总量，和 t1 表数据量进行对比）

   mysql> select count(*) from performance_schema.data_locks;

   

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

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

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

   • metadata_locks：验证是否有表锁

     mysql> select * from performance_schema.metadata_locks\G;

     

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

   select * from t1 where a='Data2999999';

   

实验二

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

实验三

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

• data_locks

  mysql> select * from performance_schema.data_locks;

  
  

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

  select * from t1 where a='Data2999999';

  

实验结论

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

总结

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

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

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

下面我假设一下场景

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

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

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