1.引入
我们在之前的课程中,和大家说了一下数据库锁的相关分类,上几节课程中,我们说了Myisam存储引擎的读锁和写锁,那么接下来呢,我们就一起来看一下行锁。
2.行锁的特点
行锁偏向于InnoDB存储引擎,开销大,加锁慢;会出现死锁;锁定的粒度最小,发生锁冲突的概率最小,并发度也最高。
3.行锁的一个基本案例
(1).创建测试数据库表boys
DROP TABLE IF EXISTS `boys`;
CREATE TABLE `boys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`boyName` varchar(20) DEFAULT NULL,
`userCP` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
//注意,这里面的存储引擎我们使用的是InnoDB
添加数据并查看内容:
给字段boyName和userCP分别创建索引
(2).关闭数据的自动提交,测试的俩个会话都关闭自动提交
set autocommit = 0;
注意,这里面关闭自动提交以后各个会话中只能够做到读自己所提交的数据!!,如下:
(3).俩个session都进行修改操作
A:对同一行数据进行修改操作,修改boyName值对于id为1的这一行
会话1进行数据的修改操作,操作已执行。
会话2进行数据的修改操作,操作阻塞,会话1提交以后,会话2立刻执行提交
最后的数据存储结果以最后提交的数据为准:
B:对不同行数据进行修改操作
会话1修改id为2的行
会话2修改id为3的行
俩个会话提交以后的结果就是各自提交的数据内容,如下:
4.索引失效致使行锁变为表锁
(1).为了方便我们的操作演示,我们呢把userCP字段的值进行一个更改,结果如下:
(2).通过俩个不同的会话进行不同的数据操作
会话1对userCP = 100的记录进行更改操作
会话2对userCp = 200的记录进行更改操作
我们在索引的内容的时候和大家说过,字符串类型的值必须要加引号,那么当我们手误把引号去掉以后是一个什么情况?
会话1我们还是修改boyName = 诸葛亮 的记录
我们这里就会直接报错,它认为是把值当做了一个新的字段列。
如果执行成功,那么它就会对其他的会话连接进行阻塞,这其实是一个很隐晦的问题,也是一个不容易察觉的问题。
也就是它把行锁直接变成了表锁。
5.如何锁定一行(其实就是如何多个会话同时操作一行数据时候针对这一行数据进行一个锁定)
会话1操作如下。
会话2在操作id = 1的字段就会阻塞
6.行锁总结
(1).分析行锁定
可以使用InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况。使用如下的指令:
show status like 'innodb_row_lock%';
结果以及参数说明如下:
其中比较重要的是我们需要看一下参数2,参数3,参数5,其中尤其是等待次数,当等待的次数比较高的时候,我们就需要分析系统中为什么会有如此多的的等待,然后根据分析的结果制定优化计划。
(2).行锁优化建议
1.尽可能的让所有数据检索都通过索引来完成,避免无所有行锁升级为表锁。
2.合理的设计索引,尽量缩小锁的范围。
3.尽可能较少检索条件,避免间隙锁的发生。
4.尽量控制事务大小,减少锁定资源量和时间长度。
5.尽可能低级别事务隔离。