MySQL Innodb行锁升级问题—测试篇

目的

       MySQL的innodb存储引擎支持行级锁，innodb的行锁是通过给索引项加锁实现的，这就意味着只有通过索引条件检索数据时，innodb才使用行锁，否则使用表锁。然而，在数据处理中，当操作的索引列数据较多时，行锁将会升级，导致非索引项的操作也会处于锁等待状态。

测试方案

       创建数据表test，表定义如下所示：

 

CREATE TABLE `test` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(20) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `idx_name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

 

       创建测试数据集，插入SQL语句如下所示：

 

insert into test value(null,'aaa'), (null,'aaa'), (null,'aaa'), (null,'aaa'), (null,'aaa'), (null,'aaa');

insert into test value(null,'bbb'), (null,'ccc'), (null,'ddd'), (null,'eee'), (null,'fff'), (null,'ggg'), (null,'hhh'), (null,'iii'), (null,'jjj'), (null,'kkk'), (null,'lll'), (null,'mmm');

 

测试

       测试过程从三个方面测试：首先测试行锁的有效性，即测试索引项的行锁操作；其次测试行锁的升级，测试索引项操作的锁等待；最后，减少操作索引项数据，查看行锁升级的临界值。

       测试前，首先建立两个mysql连接，并且将mysql的autocommit设置为0，然后进行一下各个测试。执行的sql语句为：

 

set autocommit=0;

 

1、行锁测试

       在mysql客户端1上执行以下操作：

delete from test where name='ccc';

      

       在mysql客户端2上执行以下操作：

insert into test values(null,’nnn’);

 

       在客户端1没有提交和回滚操作之前，在客户端2上执行插入操作成功。由此可见，mysql在操作时的确是使用了行级锁。

       验证结束之后，回滚操作，分别在两个客户端上执行回滚操作：

rollback;

 

2、行锁升级测试

       在mysql客户端1上执行以下操作：

delete from test where name='aaa';

      

       在mysql客户端2上执行以下操作：

insert into test values(null,’nnn’);

 

       客户端1没有提交和回滚的情况下，客户端2始终处于锁等待状态。只有当在客户端1上执行回滚操作或者提交操作后，客户端2上的操作才能执行。从行锁的理论来看，这是不符的。但是当操作的数据占整个数据的比例较大时，行锁将会升级，导致其他操作处于锁等待状态。

3、行锁升级临界测试

       行锁升级临界测试，主要是通过在客户端1上每次增加操作的记录数，然后在客户端2上执行相同的插入操作，测试是否会产生锁等待。每次操作完成后，执行rollback操作，恢复测试环境，保证测试环境的一致性。

       在mysql客户端1上每次执行以下一条SQL语句：

1) delete from test where name>='bbb' and name<='ccc';

2) delete from test where name>='bbb' and name<='ddd';

3) delete from test where name>='bbb' and name<='eee';

4) delete from test where name>='bbb' and name<='fff';

      

       在mysql客户端2上执行每次对应执行以下操作：

insert into test values(null,'nnn');

 

       经过以上测试，当客户端1执行测试语句4）时，此时客户端2的操作处于锁等待状态。此时客户端1操作的记录数为5。

结论

       通过以上测试可知，MySQL的innodb存储引擎当操作的记录数达到一定条件时，会使得行锁升级，导致与操作不相关的操作处于锁等待状态。对于行锁的升级，需要根据MySQL的innodb源码中的具体实现进行进一步的分析。具体的操作记录的临界值与具体数据表有关，这里测试仅是查看当前表结构和记录数情况下的临界值，不具有参考性。

       因此，在数据库进行数据清理或者大量数据修改时，建议使用在线实时操作工具，或者将操作细粒度到很小的记录数，然后进行操作。否则，会因为表升级而影响线上正常的业务。