锁定表-select...for-update-行和间隙的粗浅解析 (锁定表头不滚动怎么回事)
关于这个疑问,我4年前就专门钻研过,最近看到网上很多相关的文章,要么总结得不全,要么存在很多疑问。
觉得有必要自己写一篇,一方面对网上的常识启动纠偏,另一方面也想片面总结一下这块常识,繁难大家学习。
这篇文章应该是全网总结最全的,假设有发现比我这篇写得更好,更全,必定要私我哈。
不BB,上文章目录:
图片
01环境预备
在验证之前,咱们先预备好详细的环境和数据,事务隔离级别RR,数据库版本5.7.26。
为了繁难测试,索引都是整型:
CREATETABLEuser(idint(11)unsignedNOTNULLAUTO_INCREMENT,user_noint(11)NOTNULLCOMMENT'用户编号',user_namevarchar(16)DEFAULTNULLCOMMENT'用户名',ageint(3)DEFAULTNULLCOMMENT'年龄',PRIMARYKEY(id),UNIQUEKEYun_idx_user_no(user_no),KEYidx_age(age))ENGINE=InnoDBAUTO_INCREMENT=0DEFAULTCHARSET=utf8;
初始化数据:
insertintouservalues(1,10,'楼仔',18);insertintouservalues(4,15,'二哥',28);insertintouservalues(8,20,'一灰',38);
罕用命令操作:
>starttransaction;//开启事务>commit;//提交事务>rollback;//回滚事务>select@@transaction_isolation;//检查事务隔离级别>select@@version;//检查数据库版本>SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCKS;//查问锁
02场景分类
2.1主键(有值)
说明:主键查问,查问数据存在。
口头失望锁查问:
select*fromuserwhereid=1forupdate;
口头降级操作,被锁住了:
updateusersetuser_name="楼仔小弟"whereid=1;ERROR1205(HY000):Lockwttimeoutexceeded;tryrestartingtransaction
检查锁消息:
图片
论断:查问条件为主键,且有值,行锁
2.2主键(空值)
操作:主键查问,查问数据不存在。
口头失望锁查问:
select*fromuserwhereid=2forupdate;
口头拔出操作,被锁住了:
insertintouservalues(3,14,'楼仔小弟',28);ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
这里的间隙锁,锁住的区间是id字段的(1,4)区间,检查锁消息:
图片
论断:查问条件为主键,且空值,间隙锁
2.3惟一索引(有值)
说明:惟一索引查问,数据存在。
口头失望锁查问:
select*fromuserwhereuser_no=10forupdate;
口头降级操作,被锁住了:
updateusersetuser_name="楼仔小弟"whereuser_no=10;ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
图片
论断:查问条件为惟一索引,且有值,行锁
2.4惟一索引(空值)
说明:惟一索引查问,数据不存在。
![锁定表](https://tyye.cn/zb_users/theme/tpure/style/images/lazyload.png)
口头失望锁查问:
select*fromuserwhereuser_no=11forupdate;
口头拔出操作,被锁住了:
insertintouservalues(3,14,'楼仔小弟',28);ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
这里的间隙锁,锁住的区间是user_no字段的(1,4)区间。
图片
论断:查问条件为惟一索引,且空值,间隙锁
2.5普通索引(有值)
说明:普通索引,数据存在。
口头失望锁查问:
select*fromuserwhereage=18forupdate;
口头降级操作,被锁住了:
updateusersetuser_name="楼仔小弟"whereage=18;ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
图片
口头拔出操作,被锁住了:
insertintouservalues(3,14,'楼仔小弟',20);ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
图片
这里锁住的是age字段的[18,28)这区间。
论断:查问条件为普通索引,且有值,间隙锁
2.6普通索引(空值)
说明:普通索引,数据不存在。
口头失望锁查问:
select*fromuserwhereage=19forupdate;
口头拔出操作,被锁住了:
insertintouservalues(3,14,'楼仔小弟',20);ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
图片
这里锁住的是age字段的(18,28)这区间。
论断:查问条件为普通索引,且空值,间隙锁
2.7索引(范围查问)
说明:这里的索引,包括主键索引、惟一索引和普通索引。
口头失望锁查问:
select*fromuserwhereid>1forupdate;
口头拔出操作,被锁住了:
insertintouservalues(3,14,'楼仔小弟',20);ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
图片
这里其实可以对id=1的数据启动降级,关于其它数据,都被锁住,锁住的范围是id字段的(1,4],(4,8],(8,正无量)区间。
论断:查问条件为索引,且是范围查问,间隙锁。
2.8无索引
口头失望锁查问:
select*fromuserwhereuser_name="楼仔"forupdate;
口头拔出操作,被锁住了:
insertintouservalues(3,14,'楼仔小弟',20);ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
这里清楚是锁表了,但是为什么锁的消息还是行锁呢,知道的同窗,可以私我哈~~
论断:查问条件为无索引,表锁。
03加锁规定
3.1法令总结
咱们把上方的论断启动汇总:
图片
总结如下法令:
3.2加锁规定
那能否有一套加锁规定呢?
为了便于大家了解,我先遍及3个概念:
其实大佬林晓斌在极客期间讲过,起初也有很多博主转发过他的加锁规定,我间接把这套规定贴一下。
两个准则:
两个优化:
3.3剖析一下
这里咱们联合上方的案例,来解读这套加锁规定。
针对咱们前面总结的5条法令,咱们先剖析这两条:
上方咱们依据两个准则+两个优化来剖析一下。
依据准则1,加锁的基本单位是next-keylock,当索引上为等值查问时(即能查到该数据),依据优化1,间隙锁退步为行锁。
同理,依据优化2,索引上的等值查问,向右遍历时且最后一个值不满足等值条件的时刻,next-keylock退步为间隙锁。
再剖析这两条:
同上,经过准则1和优化2,普通索引是加的间隙锁。
关于范围查问,团体以为上方的规定还不能齐全笼罩,过后林晓斌针对这些规定,举了4个示例,而后启动详细剖析,包括间隙锁的范围区间计算。
由于篇幅要素,这里就不再详细开展,假设后续须要,我也或者会独自出一篇。
04写在最后
最后咱们再回忆一下(RR隔离级别):
至于间隙锁的范围,如何计算,本文没有详细论述,但是上方的这些规定,就能基本满足咱们日常上班须要。
mysql的行锁与表锁(select* .... FOR UPDATE)
mysql中使用selectforupdate的必须针对InnoDb,并且是在一个事务中,才能起作用。 select的条件不一样,采用的是行级锁还是表级锁也不一样。 由于InnoDB预设是Row-LevelLock,所以只有「明确」的指定主键,MySQL才会执行Rowlock(只锁住被选取的资料例),否则MySQL将会执行TableLock(将整个资料表单给锁住)。 举个例子:假设有个表单products,里面有id跟name二个栏位,id是主键。 例1:(明确指定主键,并且有此笔资料,rowlock)SELECT*FROMproductsWHEREid=3FORUPDATE;例2:(明确指定主键,若查无此笔资料,无lock)SELECT*FROMproductsWHEREid=-1FORUPDATE;例2:(无主键,tablelock)SELECT*FROMproductsWHEREname=MouseFORUPDATE;例3:(主键不明确,tablelock)SELECT*FROMproductsWHEREid<>3FORUPDATE;例4:(主键不明确,tablelock)SELECT*FROMproductsWHEREidLIKE3FORUPDATE;注1:FORUPDATE仅适用于InnoDB,且必须在交易区块(BEGIN/COMMIT)中才能生效。 注2:要测试锁定的状况,可以利用MySQL的CommandMode,开二个视窗来做测试。
FOR UPDATE的使用
利用select * for update 可以锁表/锁行。 自然锁表的压力远大于锁行。 所以我们采用锁行。 什么时候锁表呢? 假设有个表单products ,里面有id跟name二个栏位,id是主键。 例1: (明确指定主键,并且有此笔资料,row lock) SELECT * FROM wallet WHERE id=’3′ FOR UPDATE; 例2: (明确指定主键,若查无此笔资料,无lock) SELECT * FROM wallet WHERE id=’-1′ FOR UPDATE; 例2: (无主键,table lock) SELECT * FROM wallet WHERE name=’Mouse’ FOR UPDATE; 例3: (主键不明确,table lock) SELECT * FROM wallet WHERE id<>’3′ FOR UPDATE; 例4: (主键不明确,table lock) SELECT * FROM wallet WHERE id LIKE ‘3’ FOR UPDATE;
免责声明:本文转载或采集自网络,版权归原作者所有。本网站刊发此文旨在传递更多信息,并不代表本网赞同其观点和对其真实性负责。如涉及版权、内容等问题,请联系本网,我们将在第一时间删除。同时,本网站不对所刊发内容的准确性、真实性、完整性、及时性、原创性等进行保证,请读者仅作参考,并请自行核实相关内容。对于因使用或依赖本文内容所产生的任何直接或间接损失,本网站不承担任何责任。