1.情景展示
我们知道,当在对表执行新增、修改、删除,或者select ...for update时,会触发数据库的锁机制;
但如何才能知道当前操作触发的是哪种锁呢?以mysql为例
2.分析
首先,我们需要了解一下mysql的锁机制:
锁是计算机协调多个进程或线程并发访问某一资源的机制。
在mysql中,锁可以分为:行锁和表锁两种类型;
其次,需要确定默认存储引擎。
MyISAM存储引擎:只支持表锁(table-level locking);
MEMORY存储引擎:只支持表锁(table-level locking);
BDB存储引擎:支持页面锁,也支持表级锁,已被InnoDB取代(page-level locking);
InnoDB存储引擎:支持行锁和表锁,默认使用行锁(row-level locking)。
需要确定mysql当前正在使用的是存储引擎是不是InnoDB,只有InnoDB才支持行锁;
锁特点:
原理:
InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。
InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!!!(下面一起来验证一下)
3.解决方案
办法:
可以来两个事务,一个修改某一行数据,先别提交,然后另一个事务去修改别的行数据,如果不阻塞那应该就是行锁,否则就是表锁。
前提:
关闭mysql的自动提交事务;
在Navicat中,要想进行锁测试,必须关掉mysql的自动提交事务机制,实现方式见文末推荐。
先来验证mysql是不是由索引触发的行锁?
第一步:验证表锁
此时,表中没有任何一个索引。
此时当前号为
更新第一行记录,但并未提交事务。
此时来看表数据是否发生变化
点击底部刷新按钮,数据并未变化;
现在,更新第二条记录,并立刻提交。
我们会发现,它一直处于执行状态,如果时间过长,当前事务被迫中止,结果就是更新失败。
我们需要重新执行更新并提交才行,这次,不能再等待这么长时间,之所以这样弄,是为了方便让大家看效果。
这次,重新执行第二条更新语句,此时第一条更新语句还没有提交:
提交第一条更新语句;
此时,隔壁也会立刻提交完毕。
此时,两条记录都已+1。
如此说来,也就证明了上面的观点的前半句:
如果where条件没有索引,触发的将是表索引。
第二步:验证行锁
因为查询条件中有字段RUSERID,所以,为了验证该表能否触发行锁,加上索引试一试。
点击左上角保存
此时,索引已经加上,还是先更新第一行记录,不提交;
接着更新第二条记录,并提交。
再来看下表数据
这个时候,我们再换一个查询条件,把RUSERID去掉(就是查询条件没有涉及到索引列)。
此时,我们还是先更新第一行记录,不提交;再更新第二条记录(提不提交无所谓,都会失败)。
这时,就证明了上面的观点的后半句:
如果where条件有索引条件项(字段加的有索引),触发的将是表索引。
还有一种情况是:联合索引。
为上面的查询条件添加联合索引
再次按照上面的方式和查询条件,执行结果如下:
如此说来,联合索引并不能触发行锁,也只能触发表锁。
4.总结
在mysql中,如果存储引擎是InnoDB,结论如下:
第一,select.. for update, insert,update,delete操作都会触发锁,只要涉及到表的改动,都会触发锁;
第二,表中有索引,并不代表触发的锁就是行锁;
当where条件中含有至少一个字段添加了索引,才会触发行锁,否则就是表锁;
除了上面的条件,还有两种特殊情况:
当表中只有联合索引,而且是根据查询条件所创建的索引(总之,就是他俩涉及的列一模一样),触发的将是表锁,而不是行锁;
当查询结果过多(具体超过全表记录的百分之多少没记录),索引将会失效,触发全表扫描,此时,也会触发表锁。
第三,行锁并不一定就是只锁了一行,具体视情况而定。
2020-12-09
第四,经过测试,当where条件中携带表主键时,即使不走显式索引(mysql是不是默认会给主键加索引不清楚),触发的是行锁,而不是表锁。
2020-12-14
第五,经过测试,当执行插入语句insert into时,同样的,当插入的字段含有索引字段时,触发的是行锁;否则,触发的是表锁。
所以,在高并发需要频繁插入的时候,我们不仅可以使用批量插入语句,还可以创建索引来避免锁表。
写在最后
哪位大佬如若发现文章存在纰漏之处或需要补充更多内容,欢迎留言!!!
相关推荐: