mysql的面世管理机制_下篇分分快三全天计划网站

作者:分分快三全天计划网站

开始时语句会在很多page上加S锁,并在表上加IS锁,但是很快就会转化为如下图的情况,锁升级为一个表级S锁,S页锁全释放。

分分快三全天计划网站 1

3 SQL分析

     考虑到下文的例子,这里补充两个概念。

     ICP:

     MRR:

      

     假设表格 tb_lock ( id int primary key not null, age int,score int,name varchar(10), key ix_age_score ( age, score ) ) 数据修改如下 :

      分分快三全天计划网站 2

 

      假设MySQL当前的隔离级别为 RR,执行 UPDATE tb_index WHERE age between 5 and 22 and score between 1 and 10 and name is not null,其执行计划如下:

      分分快三全天计划网站 3

      那么,是如何加锁的呢?

      首先,可以看到是根据索引 ix_age_score 查找,那么分为两种情况来分析,第一种,数据库支持ICP;第二种,数据不支持ICP。

分分快三全天计划网站 4

分分快三全天计划网站 5


分分快三全天计划网站 6

其上锁情况为:

    温馨提示:下文有几个表格长度较长,右下角的博文导航目录会挡道,浏览时,可以点击 导航目录的左下角按钮收缩目录:分分快三全天计划网站 7

 

分分快三全天计划网站 8


分分快三全天计划网站 9

可以发现非聚集索引页的书签就是主键聚集索引的KEY值,Oracle索引的书签则全部是RowID。 

2.4 Read Serializable

所有事务隔离级别设置: set session transaction isolation level Serializable   ;

   表格     SQL

select * from tbname

where age/id ...

update tbname set name=...

where id = 4

update tbname set name=...

where age = 21

update tbname set name=...

where age between 5 and 15

tb_no_index

不支持快照读,所有SELECT都是当前读,所有SELECT操作都需要加S锁,除主键定值查找唯一索引定值查找外,其他基于索引或者主键的范围查找都会添加 S GAP LOCK。并发度是四个隔离级别中性能最差的。

当前读,根据主键修改数据

tbname 加意向表锁 IX

id=4 加 行锁 X

表格的age列无索引,所以update过程中

全表加X锁,期间全表堵塞UPDATEDELETEINSERT

同左

tb_index

表格的age列有索引,update过程中

tb_index 加 表格意向锁 IX

age索引上面,age=21 行添加行锁 X

再在主键上,给id=3 这一行数据,添加行数 X

在age索引上 添加两个gap lock ((9,2) ,(21,3)),((21,3), (21,25))

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

同时会在索引 age的值上添加 3个 gap lock,分别为

((4,4),(7,5))、((7,5),(9,2))、((9,2),(21,3))

tb_unique_index

唯一索引列上 每一个age都是唯一的,也就是age=21只有一个,不会再INSERT一个新的 age =21进来,故在这里不需要加gap lock,加锁情况如下:

tb_index 加 表格意向锁 IX

age=21 行添加行锁 X 

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

同时会在索引 age的值上添加 3个 gap lock,分别为 

((4,4),(7,5))、((7,5),(9,2))、((9,2),(21,3))

但是,范围查询添加到gap lock在其他情况下跟非唯一索引会有一些差别,可以看下表的例子。

 

 

    这里详细的来分析下 RS 隔离级别下的SELECT操作加的锁:

      分分快三全天计划网站 10

 

表格     SQL

select * from tbname

where id=5

select * from tbname

where id betwee 5 and 15

select * from tbname

where age=21

select * from tbname

where age betwee 5 and 9

tb_no_index

主键定值查找

表格tbname 添加 IS 意向锁

id=5 添加 S锁

主键范围查找

表格tbname 添加 IS 意向锁

id=5,id=6 两行数据 添加 S锁

同时添加2个 S GAP LOCK ,分别为 ((5,7),(6,25))跟((6,25), ∞)

全表查找

表格 tbname 添加 IS 意向锁

由于全表查找,整个表格 再次添加 S 表锁

全表查找

表格 tbname 添加 IS 意向锁

由于全表查找,整个表格 再次添加S 表锁

tb_index

ix_age索引查找

表格tbname 添加 IS 意向锁

索引上 age = 21 添加 S 行锁

主键上 id=3 添加 S 行锁

同时添加 2个 S GAP LOCK ,分别为 ((9,2) ,(21,3)),((21,3), (21,25))

ix_age索引查找

表格tbname 添加 IS 意向锁

age索引上面,age=7,age=9 行添加行锁 S

再在主键上,给id=2,id=5 这一行数据,添加行数 S

同时会在索引 age的值上添加 3个 S gap lock,分别为

((4,4),(7,5))、((7,5),(9,2))、((9,2),(21,3))

tb_unique_index

ix_age索引查找

表格tbname 添加 IS 意向锁

索引上 age = 21 添加 S 行锁

主键上 id=3 添加 S 行锁

由于age列唯一,故不需要添加GAP LOCK

ix_age索引查找

表格tbname 添加 IS 意向锁

age索引上面,age=7,age=9 行添加行锁 S

再在主键上,给id=2,id=5 这一行数据,添加行数 S

同时会在索引 age的值上添加 2 个 S gap lock,分别为

((4,4),(7,5))、((7,5),(9,2))

 

   至此,已说明了四个隔离级别是如何加锁,那么,释放锁呢?

 

   在MySQL INNODB中,遵循的是 strong strict 2-PL,也就是所有的write lock 跟read lock 都是在 事务 commit后才释放。

分分快三全天计划网站 11

开始时语句会在很多page上加S锁,并在表上加IS锁,但是很快就会转化为如下图的情况,锁升级为一个表级S锁,S页锁全释放。

 

1. 查询主键索引的select语句

需要注意的是如果范围查询的KEY值过多(约为5000),则会发生锁升级,此时只会在表上加一个S锁,释放掉所有的主键KEY锁和IS表锁。锁升级只会由行锁或页锁直接升级为表锁,不存在行锁升级为页锁的情况。关于锁升级参见官网锁升级相关页面。

2.3.2 RR下的唯一索引加锁情况

      update tbname set name=...  where age between .. and ... 

 

      因为唯一索引上面的索引键值都是唯一的,故不会出现重复值的插入的情况,下表罗列了同样的 范围查询修改语句,在唯一索引及非唯一索引上加 GAP_LOCK的情况。

      表格数据如下:

 

      分分快三全天计划网站 12

 

      加GAP_LOCK的情况如下(注意注意,方便查看,省略了主键值,实际上是需要添加上主键键值的):      

 
update tbname set name=...
where age between 1 and 7
update tbname set name=...
where age between 2 and 7
update tbname set name=...
where age between 5 and 10
update tbname set name=...
where age between 15 and 50
tb_index
(-∞,2),(2,4),(4,7),(7,9)
(-∞,2),(2,4),(4,7),(7,9)
(4,7),(7,9),(9,21)
(9,21),(21,25),(25, ∞)
tb_unique_index
(-∞,2),(2,4),(4,7)
(2,4),(4,7)
(4,7),(7,9),(9,21)
(9,21),(21,25),(25, ∞)

 

本例中使用begin tranwith (holdlock)提示来观察SQL Server在select语句中的锁。

分分快三全天计划网站 13

2.1 Read Uncommitted

     所有事务隔离级别设置: set session transaction isolation level read Uncommitted ;

     RU是读未提交,不添加 LOCK IN SHARE MODE 跟 FOR UPDATE 的 SELECT 语句,均为读未提交,不加锁,存在脏读、不可重复读及幻读。

     所有UPDATE、DELETE、INSERT获取当前读记录,加锁。

 表格     SQL

select * from tbname

where age/id ...

update tbname set name=...

where id = 4

update tbname set name=...

where age = 21

update tbname set name=...

where age between 5 and 15

tb_no_index

读不加锁,读未提交数据

可能有脏读、不可重复读及幻读

当前读,根据主键修改数据

tbname 加意向表锁 IX

id=4 加 行锁 X

分分快三全天计划网站 14

 

 

 

表格的age列无索引,所以update过程中,全表加X锁

支持semi-constent-read,如果有其他update语句修改其他行不堵塞,但是不支持 select ... for update

同左

tb_index

表格的age列有索引,update过程中

tb_index 加 表格意向锁 IX

age索引上面,age=21 行添加行锁 X

再在主键上,给id=3 这一行数据,添加行数 X

 

分分快三全天计划网站 15

 

 

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

 

分分快三全天计划网站 16

 

tb_unique_index

同上

同上

      

分分快三全天计划网站 17

开启事务是为了保证时间极短的查询也能观察到锁情况,因为holdlock会在事务结束后释放锁。

1.3 锁与隔离级别(不考虑 lock in shar mode跟for update )

  • RU,读未提交记录,不加锁读,正常写锁;
  • RC,快照读,无锁;当前读,加 Record Lock
  • RR,快照读,无锁;当前读,对读取到的记录加 Record Lock,同时为了确保where条件范围内的数据无变化,会增加Next key lock
  • RS,读写均为当前读,不支持快照读。包括select 在内,对读取到的记录加 Record Lock,同时为了确保where条件范围内的数据无变化,会增加Next key lock。

可以发现非聚集索引页的书签就是主键聚集索引的KEY值,Oracle索引的书签则全部是RowID。 

 

3.1 支持ICP情况

      当数据库支持ICP的时候,根据复合索引第一列 age 查找 age between 5 and 22,然后在索引内部过滤 score between 1 and 10后,取得索引值后,如果数据库支持 MRR,则会把取得的索引值放到buffer中,对主键进行排序,然后可以根据顺序的主键值去 主键中查找行数据,如果不支持,则跳过这一步排序步骤,直接根据索引值内部的主键值,查找主键行数,最后过滤 name is not null 。

    分分快三全天计划网站 18

      加锁过程如下,tb_lock添加 IX 意向锁,在索引  ix_age_score 给索引值(7,10,5),(21,4,3)添加上 X record lock,并添加4个 X GAP LOCK,如图片红色素箭头展示,分别为((4,7,4), (7,10,5)),((7,10,5), (9,15,2)),((9,15,2),(21,4,3)),((21,4,3),(25,1,6)),最后在主键上给id=3及id=5 两行数就添加X record lock。

2. 查询非聚集索引的select语句

分分快三全天计划网站 19

1.1 锁定兼容情况

  四个锁之间的兼容性,需要分成两种情况来讨论,锁粒度小于表级别的锁的兼容情况,表级的锁兼容情况。

  1. 锁粒度小于表级别的锁的兼容情况

分分快三全天计划网站 20

对于这两行锁的兼容说明如下:

  • 假设有一行数据,添加了行锁S锁,那么这个行数据,可以提供给其他事务进行S锁的申请和添加,但是不支持其他事务对这一行进行X锁的申请和添加。比如,事务A,对 pk100 这一行进行了 查询操作并添加了S锁,那么其他事务仍然可以对这一行数据进行查询,但是不能对这行数据进行 UPDATE 跟 DELETE 操作,会处于锁等待情况,直到该事务A结束并释放S锁;
  • 假设有一行数据,添加了X锁,那么这个行数据,不允许其他事务对这一行数据进行加锁。比如,事务A,对pk100这一行进行了UPDATE操作,那么其他事务在事务A没有结束之前,都无法对这一行数据申请 S锁。

表级的锁兼容情况

分分快三全天计划网站 21

对于表级别的锁兼容性如下:

当一个表格持有S表锁时,不需要其他事务对该表格申请X锁跟IX锁,但是允许申请S跟IS锁。比如,事务A对表格tba全表读,加了S表锁,期间支持其他事务对tba全表读(申请S表锁成功)、支持其他事务对tba行数据查询(申请IS表锁成功),但是不支持对表格全表的修改操作(申请X表锁等待)跟不支持对表格行数据修改操作(申请IX表锁等待);

当一个表格持有X表锁时,持有锁期间,不支持其他所有锁的申请;

当一个表格持有IS表锁时,允许申请 S表锁、IS表锁、IX表锁,但是不支持X表锁申请。

  • 比如,事务A对表格tba 查询了 id = 10(id为主键)这一行数据,这个时候,表格tba持有IS表锁,id = 10 这一行持有 S 行锁,期间,支持其他事务对 tba 全表查询(申请表锁S成功)或者 基于索引查找(申请表锁IS成功)
  • 如果需要对行 id = 20 进行数据修改,则会先申请 tba 的表锁 IX(申请成功),然后再申请id=20行锁X (申请成功);如果需要对 id = 10 这一行数据进行修改,则会申请 tba的表锁 IX(表锁申请成功),然后申请 id = 10 的行锁X(申请堵塞,因为 id = 10 正持有S锁);
  • 如果需要对表格进行全表修改,需要申请表锁(X锁),这个时候,IS锁的优势来了,当查看表格是否有其他事务在访问操作时,一看表锁IS就知道有其他事务对表格内部某些数据持有S锁,并且还没有释放,那么这个时候,申请X锁就会处于等待状态,而不需要一行一行去查询每一行数据有没有被其他事务持有锁,可以大规模的减少查询 锁申请情况;

当一个表格持有IX表锁时,支持申请IS、IX表锁,但是不兼容S、X表锁。

  • 比如,事务A对表格 tba 中 id=10 (id为主键)进行进行 数据修改,这个时候,会对表格 tba 先申请一个 IX 表锁(申请成功),然后申请 id =10 的 X 行锁,申请成功,则 事务A 持有 IX 表锁、id=10的X 行锁,此时事务B 查询 id=20的行,申请表锁 IS 成功,申请 id=20的 S 行锁成功;事务C 修改 id=30的行数据,申请表锁 IX 成功,申请 id=30的行锁 X成功;但是,事务D中,对整个表格发起update或者全表SELECT操作,需要申请 X表锁或者S表锁,正常情况下,应该要对表格的每一行数据进行查看,确保每一行数据的行锁情况,但是因为有了意向锁,事务D一看到 tba 持有 了IX锁,则明白,tba 中某些行持有X锁,则会不兼容其他事务对tba 表锁S ,表锁X的申请。

为什么要引入意向表锁?

  • 在没有意向锁的时候,如果事务T 需要给表格 A 添加 一个S 表锁,那么就意味这这个表格内部的每一行数据,都不能有X锁,才能够申请 S 表锁成功,如果表格数据很多,一行行查找非常浪费加锁时间,这个时候,就出现了表格意向锁,当表格内部某些行发生 UPATE DELETE INSERT操作,则会对表格 加上 一个意向 IX 表锁,这样 事务T在申请 表格A的 S 表锁时,只需要检查 表格 A 是否有 IX表锁,如果有,则意味内部有 部分行数据持有X锁,则直接进入等待情况,如果表格没有 IX表锁,则直接申请S表锁成功,这是一个多么节约加锁时间的操作!

1)当我直接执行 select * from ris_request时其加锁情况是:

本例中使用begin tranwith (holdlock)提示来观察SQL Server在select语句中的锁。

3.2 不支持ICP情况

      当数据库不支持ICP的时候,根据复合索引第一列 age 选择 age between 5 and 22,然后根据筛选的索引值 (7,10,5),(9,15,2),(21,4,3)中的主键 5、2、3,找到对应的行数据,再在行数据中 过滤 score between 1 and 10 and name is not null。

     分分快三全天计划网站 22

      加锁过程如下,tb_lock添加 IX 意向锁,在索引  ix_age_score 给索引值(7,10,5),(9,15,2),(21,4,3)添加上 X record lock,并添加4个 X GAP LOCK,如图片红色素箭头展示,分别为((4,7,4), (7,10,5)),((7,10,5), (9,15,2)),((9,15,2),(21,4,3)),((21,4,3),(25,1,6)),最后在主键上给id=2、id=3、id=5 两行数就添加X record lock。

 

参考文档:

 

 

这里我们再使用dbcc page看一下索引页22872的内容:(这里只截取了前10行)

分分快三全天计划网站 23

 

需要注意的是如果范围查询的KEY值过多(约为5000),则会发生锁升级,此时只会在表上加一个S锁,释放掉所有的主键KEY锁和IS表锁。锁升级只会由行锁或页锁直接升级为表锁,不存在行锁升级为页锁的情况。关于锁升级参见官网锁升级相关页面。

select语句会为表上读到的的每个页依次加锁,读完且获取到下一个页的S锁时释放本页的S锁,表上只有一个IS锁。


3.进行全表扫描的select语句

PatientID是一个非唯一非聚集索引,结果集只有1条,因此索引查找时会多查找一个索引键,所以加两个KEY键范围锁,当然键所在的索引页22872本身也需要一个IS锁,之后书签查找需要在数据页3104上加IS锁以便获取其中数据行,最后在相关的数据行上加S类型的KEY锁。

1.2 锁的级别

Table Lock

  • 表锁,如果没有where条件、无可用索引或者获取的行记录过多,则会使用 table full scan,添加表锁

Record Lock

  • 记录锁,如果执行计划使用了索引,则会根据索引的查找情况添加行锁

Gap Lock

  • 在RR、RS隔离级别,发生在索引值之间,在连续的两个索引值之间添加锁,加锁后,这两个索引值之间,无法插入新的索引值,不包含行记录

Next-Key Lock

  • Record Lock 跟Gap Lock的组合,合体成为Next-KEY Lock

 

      表锁、行锁都相对好理解,这里尝试简单说明下 GAP LOCK。

      假设当前隔离级别为RR,表格 tbgap( id int auto_increment primary key not null , name varchar(50) , sort int , key ix_sort (sort)) engine=innodb; 

      表格数据如下:

      分分快三全天计划网站 24

      在索引ix_sort上,一共有7个间隙,分别为(-∞,(1,6)),((1,6),(2,5)),((2,5),(3,2)),((3,2),(5,4)),((5,4),(6,1)),((6,1),(7,3)),((7,3), ∞),而根据实际的隔离级别及锁申请情况,加在这些间隙上的锁,则成为 GAP LOCK 。   

分分快三全天计划网站 25

其上锁情况为:

 1 Innodb的锁

    在innodb中,有4种类型的锁:IX、X、IS及S锁,其说明如下:

 

类型 说明 场景
S 共享锁 针对于RS隔离级别的查询或者添加Lock in share mode的SELECT查询而产生的锁
X 排它锁 针对于update、delete、insert操作而产生的锁
IS 意向共享锁 表级别的锁,在添加S锁之前对表格添加IS锁
IX 意向排他锁 表级别的锁,在添加X锁之前对表格添加IX锁

这里需要特别说明的是默认的事务隔离级别下其实key锁加的不是键范围锁,而是S锁,这里之所以显示ranges-s只是因为holdlock的作用,导致出现的是键范围锁。

分分快三全天计划网站 26

 

PatientID是一个非唯一非聚集索引,结果集只有1条,加两个KEY键范围锁,当然键所在的索引页22872本身也需要一个IS锁,之后书签查找需要在数据页3104上加IS锁以便获取其中数据行,最后在相关的数据行上加S类型的KEY锁。

2)当我加了with (holdlock)时: 

2.3.1 RR下的非唯一索引加锁情况

      update tbname set name=...  where age = 21

      分分快三全天计划网站 27 

      还记得上篇文章说过 RR隔离级别可以防止 幻读吗?因为在RR隔离级别中,加多了next-key = record lock gap lock,gap lock是加在索引值之间的锁。也就是 当修改 age=21 的行数据时,除了 在 age=21 这一行添加 X record lock , 还在 ((9,2) ,(21,3)),((21,3), (21,25))这两个age值得范围内添加 gap lock。加锁的情况是:tb_index添加 IX意向锁,age索引上添加age=21的 x record lock,再在主键上的行记录 id=5 添加 X record lock,同时在 age 值上添加两个 gap lock,分别为((9,2) ,(21,3)),((21,3), (21,25))。

     注意这里有个误区,很多小伙伴会认为,那么这么加gap锁,则意味着,当update age=21这一列时, 9<age<25 ,这个范围内,是不允许进行 UPATE/DELETE/INSERT的。这种推测实际上是不完整的,因为它没考虑到跟主键!!!

 

     注意,每次写gap lock的时候,都是有加上主键值的。比如这里,当更新 age=21这列时,加了 ((9,2) ,(21,3)),((21,3), (21,25)) 这两个范围的 GAP LOCK,那么在当前update age=21的事务还没有结束的情况下,假设有两条修改SQL的语句:

update tbname set age=9 where id = 1;

update tbname set age=9 where age = 4;

 

    这两条SQL,是能够正常执行,还是堵塞呢?

    innodb中,索引按照二叉树排列顺序,而这两条SQL修改后在IX_AGE上的索引值分别为:(9,1)、(9,4),可以发现(9,1)在键值(9,2)的左边,不在GAP LOCK的范围内,所以,可以正常执行;而(9,4)在键值的右边,刚好在GAP LOCK的范围内,会被堵塞!总结:第一条UPDATE SQL,正常秩序;第二条UPDATE SQL会被堵塞。

 

    所以,考虑GAP LOCK的时候,一定要注意结合整个索引键值来分析,而索引键值=索引值 主键。

这里我选择了一较为靠前的主键值,结果集有6条,因为是序列化隔离级别的键范围锁,所以持有7个KEY类型的RangeS-S锁(键范围锁,防止幻读),此外这些记录都在一个页中(1:3104页),因此最终的情况就是上图所示的7个KEY锁和一个数据页的IS锁。7个键范围锁中有6个锁对应6个主键数据行,剩下1个锁定虚拟键防止幻读。注意键范围锁只会在索引行上出现,且只针对select语句,目的是防止幻读。

 

2.3 Read Repeatable

所有事务隔离级别设置: set session transaction isolation level repeatable read ;

 

      RR隔离级别中,SELECT操作支持快照读,所有的UPDATE/DELETE/INSERT加锁,锁类型会新增一个GAP LOCK。

   表格     SQL

select * from tbname

where age/id ...

update tbname set name=...

where id = 4

update tbname set name=...

where age = 21

update tbname set name=...

where age between 5 and 15

tb_no_index

快照读,不加锁

读取的数据不一定是最新版本,但是事务内的所有查询读取数据都是同一版本的行数据,不存在脏读、不可重复读及幻读的情况

当前读,根据主键修改数据

tbname 加意向表锁 IX

id=4 加 行锁 X

分分快三全天计划网站 28

 

 

表格的age列无索引,所以update过程中

全表加X锁,期间全表堵塞UPDATEDELETEINSERT

同左

tb_index

表格的age列有索引,update过程中

tb_index 加 表格意向锁 IX

age索引上面,age=21 行添加行锁 X

再在主键上,给id=3 这一行数据,添加行数 X

 

你以为结束了!并没有,这里有趣了!

还会添加两个gap lock ((9,2) ,(21,3)),((21,3), (21,25))

 

这里我们单独拎出小表格来分析。

分分快三全天计划网站 29

 

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

同时会在索引 age的值上添加 3个 gap lock,分别为 

((4,4),(7,5))、((7,5),(9,2))、((9,2),(21,3))

 

分分快三全天计划网站 30

 

tb_unique_index

以为跟上面的加锁范围一样,no no no

唯一索引列上 每一个age都是唯一的,也就是age=21只有一个,不会再INSERT一个新的 age =21进来,故在这里不需要加gap lock,加锁情况如下:

tb_index 加 表格意向锁 IX

age=21 行添加行锁 X 

age索引上面,age=21 行添加行锁 X

再在主键上,给id=3 这一行数据,添加行数 X

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

同时会在索引 age的值上添加 3个 gap lock,分别为 

((4,4),(7,5))、((7,5),(9,2))、((9,2),(21,3))

但是,范围查询添加到gap lock在其他情况下跟非唯一索引会有一些差别,可以看下表的例子。

 

这里做亮点补充说明:

 

2)当我加了with (holdlock)时: 

这里我们再使用dbcc page看一下索引页22872的内容:(这里只截取了前10行)

2.2 Read Committed

所有事务隔离级别设置: set session transaction isolation level read committed ;

     

     RC是读已提交,不添加 LOCK IN SHARE MODE 跟 FOR UPDATE 的 SELECT 语句,均为 快照读,不加锁,同个事务内读取同一个版本的数据,可能非最新数据,但是不存在脏读、不可重复读及幻读情况。

     所有UPDATE、DELETE、INSERT获取当前读记录,加锁。

 

     下表中,黄绿色 字体 是RC与RU隔离级别不同的地方,仔细阅读分析结果可以知道,在 RU 跟 RC 间,最大的区别在于 SELECT 的查询模式,RU 为 读未提交,而 RC 为快照读。UPATE/DELETE/INSERT的加锁模式类同。

  表格

             SQL

select * from tbname

where age/id ...

update tbname set name=...

where id = 4

update tbname set name=...

where age = 21

update tbname set name=...

where age between 5 and 15

tb_no_index

快照读,不加锁

读取的数据不一定是最新版本,但是事务内的所有查询读取数据都是同一版本的行数据,不存在脏读、不可重复读及幻读的情况

当前读,根据主键修改数据

tbname 加意向表锁 IX

id=4 加 行锁 X

分分快三全天计划网站 31

 

 

表格的age列无索引,所以update过程中,全表加X锁

支持semi-constent-read,如果有其他update语句修改其他行不堵塞,但是不支持 select ... for update

同左

tb_index

表格的age列有索引,update过程中

tb_index 加 表格意向锁 IX

age索引上面,age=21 行添加行锁 X

再在主键上,给id=3 这一行数据,添加行数 X

 分分快三全天计划网站 32

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

 分分快三全天计划网站 33

 

tb_unique_index

同上

同上

 

 

开启事务是为了保证时间极短的查询也能观察到锁情况,holdlock相当于开启序列化事务隔离级别,只有在提交后才会释放锁,且对索引加键范围锁防止幻读。

2. 查询非聚集索引的select语句

    MySQL的并发处理机制,有MVCC及锁机制来处理,上篇简要说明了 MVCC及隔离级别:mysql的并发处理机制_上篇 ,这篇来说说mysql下的锁。分分快三全天计划网站 34

其上锁情况为:

 


 

这里我选择了一较为靠前的主键值,结果集有6条,因为是主键索引的范围扫描因此扫描到第7行时停止,所以持有7个KEY类型的RangeS-S锁(键范围锁),此外这些记录都在一个页中(1:3104页),因此最终的情况就是上图所示的7个KEY锁和一个数据页的IS锁。这里每一个键范围锁对应一个主键数据行。注意键范围锁只会在数据/索引行级别出现。

2 锁的申请与释放过程

      看SQL语句的锁情况,需要结合隔离级别、执行计划、表结构等,同一个SQL,不同的隔离级别、表结构、执行计划,其锁情况不一定是一样的!

      本次模拟这3个表格,age列分别:无索引、有一般索引、有唯一索引。表结构结束及数据如下:

 

CREATE TABLE tb_no_index ( id int primary key not null auto_increment, age int not null, name varchar(100) );

CREATE TABLE tb_index ( id int primary key not null auto_increment, age int not null, name varchar(100) KEY ix_age(age) );

CREATE TABLE tb_unique_index ( id int primary key not null auto_increment, age int not null,name varchar(100) UNIQUE KEY ix_age(age) );

 

INSERT INTO tb_no_index(age) values(2),(9),(21),(4),(7),(25);

INSERT INTO tb_index(age) values(2),(9),(21),(4),(7),(25);

INSERT INTO tb_unique_index(age) values(2),(9),(21),(4),(7),(25);

  

每个表格IX_age的索引行数就据如下图展示:

age
2
4
7
9
21
25
id
1
4
5
2
3
6

 

每个表格主键上面的行数就据如下图展示:

id
1
2
3
4
5
6
age
2
9
21
4
7
25
name
null
null
null
null
null
null

 

其上锁情况为:

3.进行全表扫描的select语句

    如果转载,请注明博文来源: www.cnblogs.com/xinysu/   ,版权归 博客园 苏家小萝卜 所有。望各位支持!

分分快三全天计划网站 35

1)当我直接执行 select * from ris_request时其加锁情况是:

分分快三全天计划网站 36

分分快三全天计划网站 37

select语句会为表上读到的的每个页依次加锁,读完且获取到下一个页的S锁时释放本页的S锁,表上只有一个IS锁。

1. 查询主键索引的select语句

本文由分分快三计划发布,转载请注明来源

关键词: 意向锁 mysql SQL SERVER S mysql锁