脚本宝典收集整理的这篇文章主要介绍了MySQL——锁,脚本宝典觉得挺不错的,现在分享给大家,也给大家做个参考。
由于任何一种隔离级别都不允许脏写(写-写)的现象发生,所以,当多个未提交事务相继对一条记录进行改动的时候,就需要让它们排队执行。
这个排队的过程其实是通过为该记录加锁来实现的。这个锁本质上是一个内存中的结构。
【写-写】的具体操作流程如下F1a;
【上图解释如下:】 (1)、一开始是没有锁结构与记录进行关联的,即:上图第一个图例所示。 (2)、当一个事务T1想对这条记录进行改动时,会看看内存中有没有与这条记录关联的锁结构,如果没有,就会在内存中生成一个锁结构与这条记录相关联,即:上图第二个图例所示。我们把该场景称之为获取锁成功或者加锁成功。 (3)、此时又来了另一个事务T2要访问这条记录,发现这条记录已经有一个锁结构与之关联了,那么T2也会生成一个锁结构与这条记录关联,不过锁结构中的is_waiting属性值为true,表示需要等待。即:上图第三个图例所示。我们把该场景称之为获取锁失败/加锁失败。 (4)、事务T1提交之后,就会把它生成的锁结构释放掉,然后检测一下还有没有与该记录关联的锁结构。结果发现了事务T2还在等待获取锁,所以把事务T2对应的锁结构的is_waITing属性设置为false,然后把该事务对应的线程唤醒,让T2继续执行。
为了避免在“读-写”或“写-读”情况下避免脏读、不可重复读、幻读现象,有如下两种可选的解决方案: (1)、读操作使用多版本并发控制(;mVCC),写操作进行加锁。 (2)、读、写操作都采用加锁的方式。
所有普通的SELECT语句在READ COMMITTED或REPEATABLE READ隔离级别下都算是一致性读。比如:
select * From student;
select * from student s
left join address a on s.addr_id = a.id;
一致性读并不会对表中的任何记录进行加锁操作,其他事务可以自由地对表中的记录进行改动。
在使用加锁的方式来解决读写问题的时候,由于既要允许读-读情况不受影响,又要使写-写或读-写情况中的操作互相阻塞,所以MySQL给锁分为以下两类:
共享锁(S锁) Shared Lock:在事务要读取一条记录时,需要先获取该记录的S锁。
独占锁(X锁) Exclusive Lock:在事务要修改一条记录时,需要先获取该记录的X锁。
S锁和X锁的兼容关系
【上图解释如下:】
情况1:事务T1首先获取了一条记录的S锁 如果事务T2也要获得这条记录的S锁,那么此时,T2是可以获得这条记录的S锁。如果事务T2要获得这条记录的X锁,那么操作会被阻塞,直到事务T1提交之后将S锁释放掉为止。
情况2:事务T1首先获取了一条记录的X锁 那么无论事务T2要获得这条记录的S锁还是X锁,T2都会被阻塞,直到事务T1提交之后将X锁释放掉为止。
锁定读的语句 (1)、对读取的记录加S锁
SELECT ... LOCK IN SHARE MODE;
(2)、对读取的记录加X锁
SELECT ... FOR UPDATE;
DELETE 先在B+树中定位到这条记录的位置,然后获取这条记录的X锁,最后再执行delete mark操作。
INSERT 一般情况下,新插入的一条记录受隐式锁保护,不需要在内存中为其生成对应的锁结构。
UPDATE(分为如下3种情况)
第一种情况:未修改主键并且被更新的列在修改前后所占用的存储空间未发生变化。 先在B+树中定位到这条记录的位置,然后获取这条记录的X锁,最后在原记录的位置进行修改操作。
第二种情况:未修改主键并且被更新的列在修改前后所占用的存储空间发生变化。 先在B+树中定位到这条记录的位置,然后获取这条记录的X锁,之后将原记录彻底删除掉(即:把记录彻底移入垃圾链表),最后再插入一条新记录。
第三种情况:修改主键。 相当于在原记录上执行DELETE操作之后再来一次INSERT操作。加锁操作就需要按照DELETE和INSERT的规则进行了。
官方名称:LOCK_INSERT_INTENTION,也称为插入意向锁:事务在等待时也需要在内存中生成一个锁结构,表明有事务想在某个间隙中插入新记录,但是现在处于等待状态。如下图所示:
【上图解释】
type属性,用来表明该锁的类型。 (1)、由于T1持有no=9的gap锁(即:no等于5~9之间不能插入记录),所以T2和T3分别想插入no=6和no=7的两条记录时会生成插入意向锁的锁结构并且处于等待状态。 (2)、 当T1提交后会把gap锁释放掉,这时候,T2和T3之间也并不会相互阻塞,他们可以同时获取到number值为9的插入意向锁,然后执行插入操作。
事实上插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁,就是这么鸡肋。
一般情况下,执行INSERT语句是不需要在内存中生成锁结构的。
但是也会有例外,比方说:一个事务首先插入了一条记录 (此时并没有与该记录关联的锁结构),然后另一个事务执行如下操作: (1)、立即使用 SELECT… LOCK IN SHARE MODE 语句读取这条记录 (也就是要获取这条记录的S锁),或者使用 SELECT … FOR UPDATE 语句读取这条记录(也就是要获取这条记录的X锁),该咋办?如果允许这种情况的发生,那么可能出现脏读现象。 (2)、立即修改这条记录(也就是要获取这条记录的X锁),该咋办?如果允许这种情况的发生,那么可能出现脏写现象。
解决办法:使用事务id,我们把聚簇索引和二级索引中的记录分开看一下 (1)、对于聚簇索引 有一个trx_id隐藏列,该隐藏列记录着最后改动该记录的事务id。在当前事务中新插入一条聚簇索引记录后,该记录的trx_id隐藏列代表的就是当前事务的事务id。如果其他事务此时想对该记录添加S锁或者X锁,首先会看一下该记录的trx_id隐藏列代表的事务是否是当前的活跃事务。如果不是的话就可以正常读取:如果是的话,那么就帮助当前事务创建一个X锁的锁结构,该锁结构的is_waiting属性为false:然后为自己也创建一个锁结构,该锁结构的is_ waiting属性为true,之后自己进入等待状态。 (2)、对于二级索引 本身并没有trx_id隐藏列,但是在二级索引页面的Page Header 部分有一个PAGE_MAX_TRX_ID属性,该属性代表对该页面做改动的最大的事务id。如果PAGE_MAX_TRX_ID属性值小于当前最小的活跃事务id,那就说明对该页面做修改的事务都己经提交了,否则就需要在页面中定位到对应的二级索引记录,然后通过回表操作找到它对应的聚筷索引记录,然后再重复情景1的做法。
综上所述,隐式锁起到了延迟生成锁结构的用处。即:一般情况不生成隐式锁,如果发生上述冲突的锁操作,则采用隐式锁结构来保护记录。
以上已经介绍过,对一条记录加锁的本质就是在内存中创建一个锁结构跟这条记录相关联,那么如果我们在操作一个事务的时候,对应多条记录的时候,是不是要针对多条记录生成多个内存的锁结构呢?比如我们执行select * from tb_user for update的时候,tb_user表中如果存在1万条数,那么难道要生成1万个内存的锁结构吗?那当然不会是这样的。其实,如果符合以下几个条件,那么这些记录的锁就可以放到一个内存中的锁结构里了,条件如下所示: (1)、加锁操作时在同一个事务中。 (2)、需要被加锁的记录在同一个页中。 (3)、需要加锁的类型是一致的。 (4)、锁的等待状态是一致的。
那么这么多次的锁结构,它到底是怎么组成的呢? 主要是由6部分组成的。分别为:锁所在的事务信息、索引信息、表锁或行锁信息、type_mode、其他信息、与heap_no对应的比特位。如下图所示:
- 【上图解释】锁所在的事务信息 一个锁结构对应一个事务,那么这里就存储着锁对应的事务信息。它其实只是一个指针,可以通过它获取到内存中关于该事务的更多信息,比如:事务id是多少。
索引信息 对于行级锁来说,这里记录的就是加锁的记录属于哪个索引。
表锁/行锁信息 (1)、对于表锁,主要是来记录对哪张表进行的加锁操作以及其他的信息。 (2)、对于行锁,内容包括3部分: Space ID:记录所在的表空间ID。 Page Number:记录所在的页号。 n_bits:一条记录对应一个bit,那么当我们对多条记录进行加锁操作的时候,就会对应多个bit,那么这个值就是用来记录有多少个bit的,而具体哪条记录对应哪个bit,是在【与heap_no对应的比特位】这块内容中有mapping映射的。但是,大家需要注意的是,并不是有多少条记录n_bits的值就是多少。为了之后在页面中插入新记录的时候也不至于重新分配锁结构,n_bits的值一般都比页面中的记录条数多一些。
type_mode 它是由32个bit组成的,分别为:lock_mode、lock_type、lock_wait和rec_lock_type,如下图所示:
其他信息 为了更好的管理系统运行过程中生成的各种锁结构,而设计了各种哈希表和链表。
与heap_no对应的比特位 如果是行级锁,会通过这部分的比特位来对应n_bit属性的值。在每条记录的头信息中保存一个叫做heap_no的属性,它是用来表示记录在堆中的相对位置的。即:Infimum的heap_no为0,Supermum的heap_no为1,然后插入记录的heap_no依次类推,每次加一。那么,这个所谓的“与heap_no对应的比特位”就是一个bit与heap_no的对应关系。以n_bit=16为例,如下所示:
示例
假设要开启一个事务T1,往tb_user表(已存在5条记录)中表空间为67,页号为3的页面上,插入一个number=15的记录(number是主键),并位这个记录加S锁,那么我们分析一下它所生成的行级锁结构是怎么样的?
由于开启的是事务T1,所以【锁所在的事务信息】指的就是T1这个事务。
由于要直接对number这个聚簇索引加锁,所以【索引信息】值的就是Primary索引。
由于是行级锁,所以在【表锁/行锁信息】中【Space ID】等于67,【Page Number】等于3,【n_bits】等于72
其中,n_recs包含伪记录(Infimum和Supermum)共2条记录和正常记录5条记录,共7条记录。那么根据上面的公式计算,得出如下结果 n_bits=(1+((7+64)/ 8))*8=72。【type_mode】由四部分组成,其中lock_mode=LOCK_S=2,lock_type=LOCK_REC=32,lock_wait=NOT_WAITING=0,rec_lock_type=LOCK_REC_NOT_GAP=1024;那么组合在一起就是 type_mode=2|32|0|1024=1058。
【其他信息】不做重点讨论。
【与heap_no对应的比特位】,因为之前已经存在5条记录了,所以number=15对应的no_heap=7,它对应的bit位,如下所示:
综上所述:锁结构为如下所示:
示例: tb_user表的表结构和表中存在的数据如下图所示。
隔离级别 | 加锁方式 | 存在问题 |
---|---|---|
READ UNCOMMITTED | 不加锁,直接记录的最新版本 | 可能出现脏读、不可重复读和幻读 |
READ COMMITTED | 不加锁,每次执行select 时都会生成一个ReadView | 可能出现不可重复读和幻读 |
REPEATABLE READ | 不加锁,只在第一次执行select 语句时生成一个ReadView | 可以很大程度上解决幻读问题,但并不是完全解决 |
SERIALIZABLE | 当autocommit=0,select语句会被转成select … LOCK IN SHARED MODE,即:给记录加S锁。 当autocommit=1,select语句不会加锁,只是利用MVCC生成一个Read View来服务记录。因为启动了自动提交,意味着一个事务中只包含一条语句,那么执行一条语句,也就不会出现重复读和幻读了。 | 不会出现脏读、不可重复读和幻读 |
针对锁定读的语句,其实可以归类为以下四种语句: (1)、语句1:SELECT … LOCK IN SHARE MODE; (2)、语句2:SELECT … FOR UPDATE; (3)、语句3:UPDATE … (4)、语句4:DELETE …
解释:因为语句3和语句4在操作update和delete之前,都要隐式的去查找相应的数据,所以也可以认为是一种锁定读。
锁定读的过程如下所示:
步骤1:快速在B+树中定位到该扫描区间(即select的查询区间)中的第一条记录,把该记录作为当前记录。
步骤2:根据不同的隔离级别,为当前记录加不同类型的锁
步骤3:判断索引条件下推(ICP:Index Condition Pushdown)的条件是否成立。如果符合索引条件下推,则执行步骤4,否则,则获取记录所在的单向链表的下一条记录,并做为新的记录,跳到步骤2继续执行。另外,本步骤还会判断当前记录是否符合扫描区间的边界条件,如果超出了扫描边界,则跳过步骤4和步骤5,直接向server层返回查询完毕。注意,步骤3不会释放锁。
ICP:只适用于二级索引,且只适用于select语句。它是用来把查询中与被使用索引有关的搜索条件下推到存储引擎中去判断,而不是返回到server层再去判断。ICP只是为了减少回表次数,也就是减少读取完整的聚簇索引记录的次数,从而减少I/O操作。
步骤4:执行回表操作,获取到对应的聚簇索引记录,并加锁。
步骤5:判断边界条件是否成立,如果还在边界内,则执行步骤6,否则,如果隔离级别为READ UNCOMMITTED或READ COMMITTED,则要释放掉加在该记录上面的锁,如果隔离级别为REPEATABLE READ或SERIALIZABLE,则不去释放记录上面的锁。
步骤6:server层判断其余搜索条件是否成立。如果不满足搜索条件,也要像步骤5中描述的那样,根据不同的隔离级别来确定对当前记录是否加锁or释放锁。
步骤7:获取当前记录所在单向链表的下一条记录,并跳到步骤2。
那么针对上面的步骤描述,我们通过几个示例的演示,加深一下上面步骤的理解。
【示例一】针对聚簇索引number作为搜索条件,隔离级别为READ UNCOMMITTED或READ COMMITTED,执行select * from tb_user where number >2 AND number <=7 AND age=25 LOCK IN SHARE MODE;
、
【示例二】针对聚簇索引number作为搜索条件,隔离级别为REPEATABLE READ或SERIALIZABLE,执行select * from tb_user where number >2 AND number <=7 AND age=25 LOCK IN SHARE MODE;
示例二与示例一的区别只在于隔离级别上。那么从上面我们介绍步骤原理的时候,也说过,如果是READ COMMITTED或SERIALIZABLE的隔离级别的话,如果不满足条件是不会解锁的。所以,我们具体步骤就不再赘述了,可以参照实例一中的具体步骤,我们就来看一下加锁情况变成了怎样?
【示例三】针对二级索引name作为搜索条件,隔离级别为READ UNCOMMITTED或READ COMMITTED,执行select * from tb_user FORCE INDEX(idx_name) where name >= ‘rose’ AND name <= ‘john’ AND age =20 LOCK IN SHARE MODE;
【步骤】
步骤1:首先扫描区间为[‘rose’,‘john’]中的第一条记录,即:name=‘rose’。
步骤2:为name='rose’的二级索引记录加S型的记录锁。
步骤3:由于查询条件为二级索引,所以符合ICP。
步骤4:执行回表操作,找到相应的聚簇索引记录,也就是number=9,然后为该聚簇索引记录加一个S型的记录锁。
步骤5:扫描区间为[‘rose’,‘john’],当前区间为name=‘rose’,符合扫描区间
步骤6:server层判断number=9记录上面的其他条件,它的age=11,不满足查询条件,所以释放掉该记录在二级索引和聚簇索引上的锁。
步骤7:获取name='rose’记录所在单向链表的下一条记录,即:name=‘john’,继续执行步骤2的操作,下面针对其他name的操作就不在赘述了。最终加锁结果如下图所示:
【示例四】针对二级索引name作为搜索条件,隔离级别为REPEATABLE READ或SERIALIZABLE,执行select * from tb_user FORCE INDEX(idx_name) where name >= ‘rose’ AND name <=‘john’ AND age=20 LOCK IN SHARE MODE;
【示例一】隔离级别为READ UNCOMMITTED或READ COMMITTED,执行update tb_user set name = ‘unknown’ where number >2 AND number <=7 AND age<25;
【示例二】隔离级别为REPEATABLE READ或SERIALIZABLE,执行update tb_user set name = ‘unknown’ where number >2 AND number <=7 AND age<25;
@H_360_1158@
当插入记录的主键与已存在的主键列值重复的时候,会引发插入报错。但是在报错之前,会对该主键值加S锁操作,具体如下所示: (1)、当隔离级别为READ UNCOMMITTED或READ COMMITTED时,加S型记录锁; (2)、当隔离级别为REPEATABLE READ或SERIALIZABLE时,加S型next-key锁;
如果与唯一二级索引重复,那么无论是什么隔离级别,都会对已经存在的B+树中的那条唯一二级索引记录加next-key锁。
另外,在使用INSERT…ON DUPLICATE KEY…这样的语法来插入记录时,如果遇到主键或者唯一二级索引列的值重复,会对B+树中已存在的相同键值的记录加X锁,而不是S锁。
待插入记录的外键在主表中能找到 在插入成功之前,无论当前事务的隔离级别是什么,只需要直接给主表对应的那条记录加S型记录锁即可。
待插入记录的外键在主表中找不到 (1)、当隔离级别为READ UNCOMMITTED或READ COMMITTED时,并不对记录加锁; (2)、当隔离级别为REPEATABLE READ或SERIALIZABLE时,对主表查询不到的那个键值附近加gap锁;
以上是脚本宝典为你收集整理的MySQL——锁全部内容,希望文章能够帮你解决MySQL——锁所遇到的问题。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。