文档章节

mysql 锁

 张欢19933
发布于 2017/05/22 12:00
字数 3246
阅读 53
收藏 0

Mysql常用存储引擎的锁机制

MyISAM和MEMORY采用表级锁(table-level locking)
BDB采用页面锁(page-leve locking)或表级锁,默认为页面锁
InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁
各种锁特点
表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生冲突的概率最高,并发度最低
行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高
页面锁:开销和加锁时间介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般
InnoDB的行锁模式及加锁方法
InnoDB实现了以下两种类型的行锁。
共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁。
意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。
上述锁模式的兼容情况具体如下表所示。
                                         InnoDB行锁模式兼容性列表


意向锁是InnoDB自动加的,不需用户干预。对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁;事务可以通过以下语句显示给记录集加共享锁或排他锁。如果一个事务请求的锁模式与当前的锁兼容,InnoDB就将请求的锁授予该事务;反之,如果两者不兼容,该事务就要等待锁释放。

  • 共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
  • 排他锁(X):SELECT * FROM table_name WHERE ... FOR UPDATE。

加锁条件

快照读:简单的select操作,属于快照读,不加锁。
select * from table where ?;

当前读:特殊的读操作,插入/更新/删除操作,属于当前读,需要加锁。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
所有以上的语句,都属于当前读,读取记录的最新版本。并且,读取之后,还需要保证其他并发事务不能修改当前记录,对读取记录加锁。其中,除了第一条语句,对读取记录加S锁 (共享锁)外,其他的操作,都加的是X锁 (排它锁)。

innodb的排它锁锁有三种类型

记录锁(行锁Recordlock):是加在索引记录上的。

InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!

间隙锁(Gap):对索引记录间的范围加锁,或者加在最后一个索引记录的前面或者后面

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。间隙锁(无论是S还是X)只会阻塞insert操作。


Next-key锁:记录锁和间隙锁的组合,间隙锁锁定记录锁之前的范围。

innodb下的记录锁(也叫行锁),间隙锁,next-key锁统统属于排他锁。

生活中的间隙锁
编程的思想源于生活,生活中的例子能帮助我们更好的理解一些编程中的思想。
生活中排队的场景,小明,小红,小花三个人依次站成一排,此时,如何让新来的小刚不能站在小红旁边,这时候只要将小红和她前面的小明之间的空隙封锁,将小红和她后面的小花之间的空隙封锁,那么小刚就不能站到小红的旁边。
这里的小红,小明,小花,小刚就是数据库的一条条记录。
他们之间的空隙也就是间隙,而封锁他们之间距离的锁,叫做间隙锁。

Mysql中的间隙锁
下表中(见图一),id为主键,number字段上有非唯一索引的二级索引,有什么方式可以让该表不能再插入number=5的记录?


图一

根据上面生活中的例子,我们自然而然可以想到,只要控制几个点,number=5之前不能插入记录,number=5现有的记录之间不能再插入新的记录,number=5之后不能插入新的记录,那么新的number=5的记录将不能被插入进来。
那么,mysql是如何控制number=5之前,之中,之后不能有新的记录插入呢(防止幻读)?
答案是用间隙锁,在RR级别下,mysql通过间隙锁可以实现锁定number=5之前的间隙,number=5记录之间的间隙,number=5之后的间隙,从而使的新的记录无法被插入进来。

间隙是怎么划分的?

:为了方面理解,我们规定(id=A,number=B)代表一条字段id=A,字段number=B的记录,(C,D)代表一个区间,代表C-D这个区间范围。

图一中,根据number列,我们可以分为几个区间:(无穷小,2),(2,4),(4,5),(5,5),(5,11),(11,无穷大)。
只要这些区间对应的两个临界记录中间可以插入记录,就认为区间对应的记录之间有间隙。
例如:区间(2,4)分别对应的临界记录是(id=1,number=2),(id=3,number=4),这两条记录中间可以插入(id=2,number=3)等记录,那么就认为(id=1,number=2)与(id=3,number=4)之间存在间隙。

很多人会问,那记录(id=6,number=5)与(id=8,number=5)之间有间隙吗?
答案是有的,(id=6,number=5)与(id=8,number=5)之间可以插入记录(id=7,number=5),因此(id=6,number=5)与(id=8,number=5)之间有间隙的,

间隙锁锁定的区域
根据检索条件向左寻找最靠近检索条件的记录值A,作为左区间,向右寻找最靠近检索条件的记录值B作为右区间,即锁定的间隙为(A,B)。
图一中,where number=5的话,那么间隙锁的区间范围为(4,11);

间隙锁的目的是为了防止幻读,其主要通过两个方面实现这个目的:
(1)防止间隙内有新数据被插入
(2)防止已存在的数据,更新成间隙内的数据(例如防止numer=3的记录通过update变成number=5)

innodb自动使用间隙锁的条件:
(1)必须在RR级别下
(2)检索条件必须有索引(没有索引的话,mysql会全表扫描,那样会锁定整张表所有的记录,包括不存在的记录,此时其他事务不能修改不能删除不能添加)

接下来,通过实际操作观察下间隙锁的作用范围

图二


案例一:

````
session 1:
start  transaction ;
select  * from news where number=4 for update ;

session 2:
start  transaction ;
insert into news value(2,4);#(阻塞)
insert into news value(2,2);#(阻塞)
insert into news value(4,4);#(阻塞)
insert into news value(4,5);#(阻塞)
insert into news value(7,5);#(执行成功)
insert into news value(9,5);#(执行成功)
insert into news value(11,5);#(执行成功)
````

检索条件number=4,向左取得最靠近的值2作为左区间,向右取得最靠近的5作为右区间,因此,session 1的间隙锁的范围(2,4),(4,5),如下图所示:


间隙锁锁定的区间为(2,4),(4,5),即记录(id=1,number=2)和记录(id=3,number=4)之间间隙会被锁定,记录(id=3,number=4)和记录(id=6,number=5)之间间隙被锁定。

因此记录(id=2,number=4),(id=2,number=2),(id=4,number=4),(id=4,number=5)正好处在(id=3,number=4)和(id=6,number=5)之间,所以插入不了,需要等待锁的释放,而记录(id=7,number=5),(id=9,number=5),(id=11,number=5)不在上述锁定的范围内,因此都会插入成功。

案例二:

````
session 1:
start  transaction ;
select  * from news where number=13 for update ;

session 2:
start  transaction ;
insert into news value(11,5);#(执行成功)
insert into news value(12,11);#(执行成功)
insert into news value(14,11);#(阻塞)
insert into news value(15,12);#(阻塞)
update news set id=14 where number=11;#(阻塞)
update news set id=11 where number=11;#(执行成功)
````
检索条件number=13,向左取得最靠近的值11作为左区间,向右由于没有记录因此取得无穷大作为右区间,因此,session 1的间隙锁的范围(11,无穷大),如下图所示:


此表中没有number=13的记录的,innodb依然会为该记录左右两侧加间隙锁,间隙锁的范围(11,无穷大)。

有人会问,为啥update news set id=14 where number=11会阻塞,但是update news set id=11 where number=11却执行成功呢?

间隙锁采用在指定记录的前面和后面以及中间的间隙上加间隙锁的方式避免数据被插入,此图间隙锁锁定的区域是(11,无穷大),也就是记录(id=13,number=11)之后不能再插入记录,update news set id=14 where number=11这条语句如果执行的话,将会被插入到(id=13,number=11)的后面,也就是在区间(11,无穷大)之间,由于该区间被间隙锁锁定,所以只能阻塞等待,而update news set id=11 where number=11执行后是会被插入到(id=13,number=11)的记录前面,也就不在(11,无穷大)的范围内,所以无需等待,执行成功。

案例三:

````
session 1:
start  transaction ;
select  * from news where number=5 for update;

session 2:
start  transaction ;
insert into news value(4,4);#(阻塞)
insert into news value(4,5);#(阻塞)
insert into news value(5,5);#(阻塞)
insert into news value(7,11);#(阻塞)
insert into news value(9,12);#(执行成功)
insert into news value(12,11);#(阻塞)
update news set number=5 where id=1;#(阻塞)
update news set id=11 where number=11;#(阻塞)
update news set id=2 where number=4 ;#(执行成功)
update news set id=4 where number=4 ;#(阻塞)
````

检索条件number=5,向左取得最靠近的值4作为左区间,向右取得11为右区间,因此,session 1的间隙锁的范围(4,5),(5,11),如下图所示:


有人会问,为啥insert into news value(9,12)会执行成功?间隙锁采用在指定记录的前面和后面以及中间的间隙上加间隙锁的方式避免数据被插入,(id=9,number=12)很明显在记录(13,11)的后面,因此不再锁定的间隙范围内。

为啥update news set number=5 where id=1会阻塞?
number=5的记录的前面,后面包括中间都被封锁了,你这个update news set number=5 where id=1根本没法执行,因为innodb已经把你可以存放的位置都锁定了,因为只能等待。

同理,update news set id=11 where number=11由于记录(id=10,number=5)与记录(id=13,number=11)中间的间隙被封锁了,你这句sql也没法执行,必须等待,因为存放的位置被封锁了。

案例四:

session 1:
start  transaction;
select * from news where number>4 for update;

session 2:
start  transaction;
update news set id=2 where number=4 ;#(执行成功)
update news set id=4 where number=4 ;#(阻塞)
update news set id=5 where number=5 ;#(阻塞)
insert into news value(2,3);#(执行成功)
insert into news value(null,13);#(阻塞)

检索条件number>4,向左取得最靠近的值4作为左区间,向右取无穷大,因此,session 1的间隙锁的范围(4,无穷大),如下图所示:

session2中之所以有些阻塞,有些执行成功,其实就是因为插入的区域被锁定,从而阻塞。

next-key锁
next-key锁其实包含了记录锁和间隙锁,即锁定一个范围,并且锁定记录本身,InnoDB默认加锁方式是next-key 锁。
上面的案例一session 1中的sql是:select * from news where number=4 for update ;
next-key锁锁定的范围为间隙锁+记录锁,即区间(2,4),(4,5)加间隙锁,同时number=4的记录加记录锁。

本文转载自:http://www.jianshu.com/p/bf862c37c4c9

粉丝 46
博文 538
码字总数 247200
作品 0
海淀
私信 提问
MySQL中的共享锁与排他锁

在MySQL中的行级锁,表级锁,页级锁中介绍过,行级锁是Mysql中锁定粒度最细的一种锁,行级锁能大大减少数据库操作的冲突。行级锁分为共享锁和排他锁两种,本文将详细介绍共享锁及排他锁的概念、...

steven
2016/09/23
67
0
MySQL中的行级锁,表级锁,页级锁

在计算机科学中,锁是在执行多线程时用于强行限制资源访问的同步机制,即用于在并发控制中保证对互斥要求的满足。 在数据库的锁机制中介绍过,在DBMS中,可以按照锁的粒度把数据库锁分为行级...

Hosee
2016/06/01
702
0
MySQL数据库的锁机制

在并发访问情况下,很有可能出现不可重复读等等读现象。为了更好的应对高并发,封锁、时间戳、乐观并发控制(乐观锁)、悲观并发控制(悲观锁)都是并发控制采用的主要技术方式。 锁分类 ①、按操...

Javahih
2018/01/02
0
0
MySQL MVCC && 事务隔离级别 && 锁

MySQL MVCC && 事务隔离级别 && 锁 InnoDB多版本并发控制——MVCC http://my.oschina.net/xinxingegeya/blog/208821 MySQLS锁X锁read lockwrite lock http://my.oschina.net/xinxingegeya/bl......

秋风醉了
2014/07/30
583
0
Mysql InnoDB行锁实现方式

mysql中InnoDB存储引擎的行锁和表锁 InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味...

mickelfeng
01/18
34
0

没有更多内容

加载失败,请刷新页面

加载更多

关于AsyncTask的onPostExcute方法是否会在Activity重建过程中调用的问题

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 本文链接:https://blog.csdn.net/XG1057415595/article/details/86774575 假设下面一种情况...

shzwork
今天
6
0
object 类中有哪些方法?

getClass(): 获取运行时类的对象 equals():判断其他对象是否与此对象相等 hashcode():返回该对象的哈希码值 toString():返回该对象的字符串表示 clone(): 创建并返此对象的一个副本 wait...

happywe
今天
6
0
Docker容器实战(七) - 容器中进程视野下的文件系统

前两文中,讲了Linux容器最基础的两种技术 Namespace 作用是“隔离”,它让应用进程只能看到该Namespace内的“世界” Cgroups 作用是“限制”,它给这个“世界”围上了一圈看不见的墙 这么一...

JavaEdge
今天
8
0
文件访问和共享的方法介绍

在上一篇文章中,你了解到文件有三个不同的权限集。拥有该文件的用户有一个集合,拥有该文件的组的成员有一个集合,然后最终一个集合适用于其他所有人。在长列表(ls -l)中这些权限使用符号...

老孟的Linux私房菜
今天
7
0
面试套路题目

作者:抱紧超越小姐姐 链接:https://www.nowcoder.com/discuss/309292?type=3 来源:牛客网 面试时候的潜台词 抱紧超越小姐姐 编辑于 2019-10-15 16:14:56APP内打开赞 3 | 收藏 4 | 回复24 ...

MtrS
今天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部