当前位置:首页 > 数码 > 锁定表-select...for-update-行和间隙的粗浅解析 (锁定表头不滚动怎么回事)

锁定表-select...for-update-行和间隙的粗浅解析 (锁定表头不滚动怎么回事)

admin3个月前 (04-18)数码24

关于这个疑问,我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惟一索引(空值)

说明:惟一索引查问,数据不存在。

锁定表

口头失望锁查问:

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;

免责声明:本文转载或采集自网络,版权归原作者所有。本网站刊发此文旨在传递更多信息,并不代表本网赞同其观点和对其真实性负责。如涉及版权、内容等问题,请联系本网,我们将在第一时间删除。同时,本网站不对所刊发内容的准确性、真实性、完整性、及时性、原创性等进行保证,请读者仅作参考,并请自行核实相关内容。对于因使用或依赖本文内容所产生的任何直接或间接损失,本网站不承担任何责任。

标签: 表锁