Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1731651
  • 博文数量: 107
  • 博客积分: 1715
  • 博客等级: 上尉
  • 技术积分: 3168
  • 用 户 组: 普通用户
  • 注册时间: 2012-04-18 18:42
个人简介

阿里巴巴DBA,原去哪儿网DBA。专注于MySQL源码研究、DBA运维、CGroup虚拟化及Linux Kernel源码研究等。 github:https://github.com/HengWang/ Email:king_wangheng@163.com 微博 :@王恒-Henry QQ :506437736

文章分类

全部博文(107)

文章存档

2014年(2)

2013年(38)

2012年(67)

分类: Mysql/postgreSQL

2012-12-25 11:52:22

目的

       MySQLinnodb存储引擎支持行级锁,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连接,并且将mysqlautocommit设置为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

结论

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

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

阅读(7137) | 评论(2) | 转发(2) |
给主人留下些什么吧!~~

shehuimin2015-10-31 07:54:41

反过来也是一样, ex>
 delete from test where name =\"eee\"; 
delete from test where name=\"aaa\";
这样也处于锁等待;

shehuimin2015-10-31 07:54:40

反过来也是一样, ex>
 delete from test where name =\"eee\"; 
delete from test where name=\"aaa\";
这样也处于锁等待;