未加星标

Mysql InnoDB引擎的锁和隔离机制那些事儿

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二05 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

对于DB来说,经常会面对并发问题,但是开发的时候DB总是能很好的解决并发的问题。那么面对并发DB是怎么进行控制的呢?之前一段时间总是对mysql的锁机制概念十分模糊,什么时候加锁?加什么锁?锁住之后会是怎么样?

需要明确的点

首先,锁是为了解决数据库事务并发问题引入的特性,在Mysql中锁的行为是和mysql隔离机制有关的,毕竟锁是用来解决DB的隔离性和一致性的。并不是任何操作都是需要加锁的,读操作是不加锁的,当然也可以显式的加锁(lock in share mode或for update)。

Mysql锁的类型

Mysql因为有很多种存储引擎,导致它的实现也是五花八门,但是最常用的就应该是MyISAM和InnoDB了。对于两者的区别之前也写过,其中有一点是MyISAM锁级别是表级而InnoDB的锁级别是行级(当然InnoDB也有表级锁)。mysql锁的类别如下:

表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

不同的锁粒度决定了不同引擎的应用场景,我们最常用的表级锁的引擎是MyISAM和InnoDB,行级引擎是InnoDB。至于页级锁的引擎常用的是Berkeley DB。

Mysql的锁

Mysql的锁主要为两种:共享锁(S Lock)和排他锁(X Lock)。从字面上我们可以理解,共享锁就是多个事务可以共享,互相兼容。而排他锁则是多个事务不兼容互相排斥。

如果一个事务T1获得了r行的共享锁,那么另外一个事务T2可以立即获得r的共享锁,这种情况称为“锁兼容”。如果有T3想获得r行的排他锁必须等到T1、T2释放r行的共享锁,这种称为“锁不兼容”,下表对应的是锁兼容性:


Mysql InnoDB引擎的锁和隔离机制那些事儿

可以看到只有共享锁是兼容的,也就是说读请求和读请求之间是没有影响的。

InnoDB为了支持在不同粒度上加锁操作,InnoDB支持另一种加锁机制――意向锁。意向锁的意思很简单,就是有意愿进行加锁。

意向共享锁(IS Lock):事务想要获取一张表中的某几行共享锁。

意向排他锁(IX Lock):事务想要获取一张表中的某几行的排它锁。

由于InnoDB支持的行级别的锁,因此意向锁其实不会阻塞除全表扫描意外的任何请求。意向锁的兼容性如下所示:


Mysql InnoDB引擎的锁和隔离机制那些事儿

意向锁和意向锁之间是完全兼容的,但是意向锁和共享锁以及排它锁可能是有互斥性的。因为意向锁的锁粒度是表级锁,所以在全表扫描是往往会对表加锁,那么此时就会发生锁冲突。

之前一直不明白意向锁到底是干什么的,相信很多人和我一样,后来查了很多资料才知道,有一个很形象的例子:

如果你家小区有一个保安,那么就能避免经常有人去按你家的门锁...

保安就是意向锁,它能避免经常有请求去请求行级锁,因为访问行级锁也是有一定开销的。

上面说的东西概念性都比较强,但是千万别被误导,因为上面的概念在实际的查询中不一定全都会使用,例如mysql的读操作,通常是不会加锁的(和隔离机制有关),也就是说通常的读操作是不加锁的,而是通过mvcc去解决的,对于通常的写请求,insert、update、delete通常会加行锁、间隙锁或表锁(这和索引是有关系的),这些锁通常是排他的,会阻塞其他的事务写事务。具体的情况需要结合隔离机制。

Mysql的隔离性

隔离性是指一个事务所做的修改在最终提交之前,对其他的事务是不可见的。

mysql的隔离性分为四个隔离级别,不同的隔离级别有不同的特点和实现:

1.Read Uncommitted(脏读):从隔离级别的名称可知,事务可以读取到其他没有commit的事务的修改,所以称为脏读,因为读取到了本来不应该读到的记录,此事务隔离级别一般是不会用的,因为如果后面另一个事务rollback掉了,岂不是悲剧了?

2.Read Committed(提交读,也叫不可重复读):只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)。对于此级别的隔离,比较上面的脏读是会严格一些的,例如事务1开始查询了一条记录,但是随后另一个事务2修改了本条记录,此时事务1再次进行读取,此时是读取不到的因为事务2没有进行commit,随后事务2commit,事务1再次读取,可以读到最新修改后的记录。这比脏读更加严格了一些,因为读取不到未提交的数据,但是此种隔离级别在同一个事务(事务1中)两次读取,读取到了不同的结果,这也就是不可重复读。

在RC级别中,数据的读取都是不加锁的,但是数据的写入、修改和删除是需要加锁的。

一个例子:

SQL代码

CREATETABLE`student`( `id`int(11)NOTNULLAUTO_INCREMENT, `name`varchar(100)NOTNULL, `stu_id`int(11)NOTNULL, PRIMARYKEY(`id`), KEY`idx_student_id`(`stu_id`) )ENGINE=InnoDBAUTO_INCREMENT=5

SQL代码

+----+------+--------+ |id|name|stu_id| +----+------+--------+ |1|语文|1| |2|数学|2| |3|英语|1| +----+------+--------+ 3rowsinset

上面是student表内的数据,接下来设置事务隔离级别为RC

SET session transaction isolation level read committed;

SET SESSION binlog_format = 'ROW';

接下来测试一下update的行锁:

T1 T2 update student set name = '生物' where stu_id = 2; update student set name = '生物' where stu_id = 2; 更新成功 阻塞 commit 更新成功

上面的update例子说明,在更新记录的时候会对此记录加行锁,在事务没有commit之前不会释放锁,所以事务2的更新会阻塞等待事务1的排它锁,当事务1Commit后,行锁释放事务2获得行锁,更新成功。

其实mysql的锁机制是通过对索引加锁,但是一旦更新不走索引会怎么样,答案是会全表扫描,锁表。所以在更新的时候尽量走索引,避免不必要的麻烦。

接下来实验一下RC基本写的不可重复读:

事务1:

SQL代码

mysql>begin; QueryOK,0rowsaffected mysql>select*fromstudentwherestu_id=2; +----+------+--------+ |id|name|stu_id| +----+------+--------+ |2|生物|2| +----+------+--------+ 1rowinset

事务2:

SQL代码

mysql>begin; QueryOK,0rowsaffected mysql>updatestudentsetname='地理'wherestu_id=2; QueryOK,1rowaffected Rowsmatched:1Changed:1Warnings:0 mysql>commit; QueryOK,0rowsaffected

接下来事务1再次查询:

SQL代码

mysql>select*fromstudentwherestu_id=2; +----+------+--------+ |id|name|stu_id| +----+------+--------+ |2|地理|2| +----+------+--------+ 1rowinset

上述过程可见,带事务1的一个事务中,两次请求得到了不同的结果,就导致了不可重复读的现象。

3.Repeatable Read(可重读或者叫幻读):RR解决了脏读的问题,该级别保证了在同一个事务中多次读取同样记录的结果是一致的。

例子和上面RC中的例子一样,只不过在事务2提交时,事务1再次查询是看不到事务1更新的记录的,所以叫可重复读,但是理论上这种方式只能解决更新问题,但是解决不了新增的问题,因为无论RC还是RR,mysql都是通过Mvcc(Multi-Version Concurrency Control )机制去实现的。

Mvcc是多版本的并发控制协议,它和基于锁的并发控制最大的区别和优点是:读不加锁,读写不冲突。它将每一个更新的数据标记一个版本号,在更新时进行版本号的递增,插入时新建一个版本号,同时旧版本数据存储在undo日志中。

而对于读操作,因为多版本的引入,就分为快照读和当前读。快照读只是针对于目标数据的版本小于等于当前事务的版本号,也就是说读数据的时候可能读到旧的数据,但是这种快照读不需要加锁,性能很高。当前读是读取当前数据的最新版本,但是更新等操作会对数据进行加锁,所以当前读需要获取记录的行锁,存在锁争用的问题。

RC和RR都是基于Mvcc实现,但是读取的快照数据是不同的。RC级别下,对于快照读,读取的总是最新的数据,也就出现了上面的例子,一个事务中两次读到了不同的结果。而RR级别总是读到小于等于此事务的数据,也就实现了可重读。

下面是快照读和当前读的常见操作:

1. 快照读:就是select

select * from table ....;

2. 当前读:特殊的读操作(加共享锁或排他锁),插入/更新/删除操作,需要加锁。

select from table where ? lock in share mode;

select from table where ? for update;

insert;

update ;

delete;

其实Mysql实现的Mvcc并不纯粹,因为在当前读的时候需要对记录进行加锁,而不是多版本竞争。下面是具体操作时的Mvcc机制:

1. SELECT时,读取创建版本号<=当前事务版本号,删除版本号为空或>当前事务版本号。

2. INSERT时,保存当前事务版本号为行的创建版本号

3. DELETE时,保存当前事务版本号为行的删除版本号

4. UPDATE时,插入一条新纪录,保存当前事务版本号为行创建版本号,同时保存当前事务版本号到原来删除的行

上面说明了RR是如何解决重读问题,但是众所周知,RR有一个致命的问题就是幻读,即只能解决另一个事务2更新对事务1不可见的问题,但是当事务2新插入一行数据的时候,事务1还是可见,这就是幻读问题。但是在实际使用中,我们发现并没有发生“幻读”问题。那么,Mysql是如何解决幻读问题的呢?

我们分两个方面说:

1.快照读:对于快照读,其实是不会出现幻读问题的,通过上面我们得知,select时只会读取小于等于当前事务版本的行,但是新行的版本号是高于读事务的,那么新插入的行对之前的读事务是不可见的。

2.当前读:因为当前读,读到的往往是最新的行数据,但是对于事务1更新了一行,同时事务2插入了一个新行(利用一个非唯一索引进行更新),那么会利用gap锁去控制新行的插入来避免这个问题。一个例子看一下:

首先开启事务A:

SQL代码

mysql>begin; QueryOK,0rowsaffected mysql>select*fromstudentwherestu_id=3; +----+------+--------+ |id|name|stu_id| +----+------+--------+ |2|化学|3| +----+------+--------+ 1rowinset mysql>updatestudentsetname="物理"wherestu_id=3; QueryOK,1rowaffected Rowsmatched:1Changed:1Warnings:0

接下来开启事务B:

SQL代码

mysql>begin; QueryOK,0rowsaffected mysql>insertintostudent(id,name,stu_id)values(5,"历史",3); QueryOK,1rowaffected

我们可以看到,事务A在更新之后,事务B进行插入操作的时候会阻塞,但是这里使用的不是行锁,这就是因为rr隔离模式下,mysql使用的是next-keylocking机制防止“当前读”的幻读问题。如果不阻塞新插入的数据,那么就会导致更新之后,再次查询时会发现部分数据没有更新,本意是按照索引更新所有的行,但是新插入的行没有更新,这就会令我们很奇怪。

那需要先说说Mysql里面特殊的锁――Next-Key锁:

Next-Key锁是行锁和Gap锁(间隙锁)的合体(可以理解为二者相加,因为gap锁是开区间的,加上行锁正好是闭区间)。间隙锁,顾名思义,是对一个间隙进行加锁,间隙是索引的间隙,也就是说,更新的时候必须走索引,否则会将全表锁住。导致其他所有的写操作全部阻塞。next-key锁主要是针对非唯一索引,因为唯一索引和主键索引每次只会定位到单条记录,所以不需要next-key锁,下面盗一张图来理解下:


Mysql InnoDB引擎的锁和隔离机制那些事儿
当按照id(非唯一索引,不是主键,主键是name)进行更新或删除的时候会先对id索引进行加锁,但加的是next_key锁。因为在RR隔离级别下,需要防止“当前读”的幻读问题,加上next-keylock之后,在[6-10]区间和[10-11]区间进行插入时会阻塞,因为已经加了next-key锁,为什么用next-key锁?因为新增加的记录只能在10的左边和10的右边或者就是10。那么锁住范围后就能保证防止“幻读”。

4.Serializable(可串行化):这个隔离级别,在并发效果上最差的,因为读加共享锁,写加排他锁,读写互斥。也就是说此级别下select是需要加锁的。此模式下可以保证数据安全,适用于并发比较低,同时数据安全性要求比较高的场景。

总结:mysql的锁机制和事务隔离级别有关。并不是说所有的读操作都不加锁,写操作加锁,加什么锁也和索引类型、有无索引有关。

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: InnoDBSQL数据数据存储其实删除AUTUTAU冲突
分页:12
转载请注明
本文标题:Mysql InnoDB引擎的锁和隔离机制那些事儿
本站链接:http://www.codesec.net/view/483266.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(43)