在线编程在线课堂在线测评Anycodes在线编程

编程论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

How to use bs4??
本帖最后由 carry0987 于
Double Queue 问题描述 : The new founded Balkan Investment Group Bank (
John 问题描述 : Little John is playing very funny game
linux-command Linux命令大全搜索工具,内容包含Linux命令
Coati 是一款跨平台的代码查看工具,适用于 C/C++ 和 Java。商业软件。特性:1. 索引
系统可承载海量并发,消息收发确认机制 保障消息必达 系统采用动态智
全平台视频监控,支持安卓苹果以及pcweb,支持海康大华等主流dvr,全部源码以及文档 单聊、群聊、商
如何访问类的私有属性? 下面以 TPathData 为例,
问题:从 XE4 以来,Firemonkey 曲线绘图在移动平台不平滑的问题一直令人诟病,提交到官方的 QC 也是族繁不及备载,官方似乎有意的
操作数据库(RODBC)   odbcConnect(dsn, uid="", p
数据模式:mode函数显示任何对象的模式。常见的单个的
系统可承载海量并发,消息收发确认机制 保障消息必达 系统采用动态智
RabbitMQ与PHP(一) 项
Iease团队扩编预备中,盼望能有Ruby或者java工程师加盟。全职兼职都可以。有爱好的伴侣请与我接洽。 邮件:i
ruby 怎么设置装备摆设GTK2,求教指导下!
#include #include #include #include using namespace std; int main() {
标题如图所示: 有n盏灯,编号1~n。一开端灯都是关着的
成熟的消息收发确认机制,支持万人大群 支持开发自定义的消息sdk接口,扩展性超强 支持单/
成熟的消息收发确认机制,支持万人大群 支持开发自定义的消息sdk接口,扩展性超强 支持单/
1. 注意列表和集合的区别 set 列表表现形式: list_1
Ajax   Ajax即“Asynchronous Javascript And
大师好,我比来在做布谷鸟优
分辨提取A和B图像Harris角点,接下来须要对

[精彩分享] InnoDB:Lock & Transaction

[复制链接]
发表于 2016-12-24 13:19:00 | 显示全部楼层 |阅读模式
InnoDB 是一个支持事务的Engine,要保证事务ACID,必然会用到Lock。就像在Java编程一下,要保证数据的线程安全性,必然会用到Lock。了解Lock,Transaction可以帮助sql优化、deadlock分析等。

1、Lock

InnoDB中,有多种类别的锁,下面将一一说明。
1.1 Shared Lock & Exclusive Lock

共享锁(S)与排他锁(X),这两个锁是row-level的锁,也就是说,可以理解为,每一行记录都有一把S,一把X锁。共享锁是读锁(Read Lock),事务执行时,如果要读取一行数据,就要先持有该行数据的读锁(S)。排他锁是写锁(Write Lock),事务执行时,如果要写数据(即更新数据, 例如update, delete),则要先持有相应的行的写锁(X)。
    此外,Read Lock可以同时被多个事务(实际上是执行这多个事务的线程)持有,Write Lock则不能。这一点,从设计上来讲,和java中的ReadLock WriteLock是类似的。
也就是说ReadLock可以同时被多个线程持有,WriteLock只能被一个线程持有。
当一个线程A持有着ReadLock(S)时,线程B也可以持有ReadLock(S),但线程B不能去持有WriteLock(X)。同时线程A如果持有着ReadLock时,如果还想再去持有WriteLock,那么必须等待其他的线程释放ReadLock,并且没有持有WriteLock。
    当一个线程A持有着WriteLock时,其他的线程不能去持有WriteLock或者ReadLock,但他自己(线程A)还是可以去读取的,而不需要去持有ReadLock。

1.2 Intention Lock

意向锁,想要做某事时的锁,这是个表锁。分为两种:意向读锁(IS)、意向写锁(IX)。
如果你想要读取某些行的记录,必须得先持有表的IS锁。想要修改、删除某些行时,必须得先持有表的IX锁。
   


X

IX

S

IS

X

Conflict

Conflict

Conflict

Conflict

IX

Conflict

Compatible

Conflict

Compatible

S

Conflict

Conflict

Compatible

Compatible

IS

Conflict

Compatible

Compatible

Compatible


使用意向锁,有两个好处:1、能够很快的进行上锁、或者不上锁操作,因为开启意向锁之后,有一个线程持有一把读锁或者意向读锁后, 另外一个线程想要持有写锁, 就要先去持有意向写锁,而意向写锁很容易就知道了暂时拿不到。如果不使用意向锁,那么就得先找到这条记录, 找到记录后,发现该行记录的读锁因为已经被其他线程持有,而不能完成写锁的持有。这样白白的浪费了查找的时间。
2、能够有效的避免死锁的发生。

但是也因为是表锁,粒度太大,导致并发很低差。在多个事务同时操作一张表时,就变成了串行操作的了。

1.3 Records Lock (Index Record Lock)

    记录锁,其实是index record Lock,也就是index row lock,不是数据row lock。Index Record Lock分为两种:S\X锁,也就是对index row加上S、X锁。
SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 如果c1是索引,将通过对c1=10的index 加上X Lock,这样就可以阻止任何其他的事务去持有t.c1=10 的索引锁。 也就是说其他的事务中, inserting、 update、 delete操作是拿不到t.c1=10的索引锁的。


1.4 Gap Lock

缝隙锁,所谓gap是指两个索引之间的gap。每一个gap也有一把锁,称为gap lock。在第一条数据之前,最后一条数据之后,也各有一个gap,所以也有gap lock。
Gap Lock可以有效的避免幻读发生。例如一个事务A在执行SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 同时另一个事务B要insert 一个 c1=15的行。此时事务B是拿不到gap lock的,因为10到20直接的gaps locks都被事务A持有。此时并不会管有没有一条c1=15的记录存在,事务B都拿不到Gap。

Gap Lock可以显式的禁用,具体方式是设置隔离级别为READ_COMMMITED或者设置系统变量:Innodb_locks_unsafe_for_binlog。这种情况下,在scan index时是无效的,只会在foreign-key检查时才会有效。也可以理解成:一个事务如果是REPEATABE_READ隔离级别,则(可能)会Gap Lock 。这是说的可能,是因为有一个特殊情况 :如果一个select语句只是从一个唯一索引的表,查询一条记录时,是不会使用gap lock的,因为没有必要的。
  1. SELECT * FROM child WHERE id = 100;
复制代码
Id是索引,并且唯一的。此时执行上述SQL时,最多只会找到一行记录,就不需要持有gap lock,而是直接持有index record lock。


1.5 Next-Key Lock

一个next-key lock是结合了一个index lock和它之前的gap lock。
InnoDB的默认隔离级别是:REPEATABLE_READ,这种隔离级别下,InnoDB使用在index scan 时,采用的是next-key。Next-key 本身不存在,只代表了index lock和它之前的gap lock。


2、Transaction

2.1事务数据库可能发生的问题有哪些?


Dirty reads:  (脏读)一个事务A读到了另一个事务B还没有提交的数据(未提交的增删改的数据)。此时事务A就发生了脏读。因为有可能事务B不再提交这个数据,那么A就是读到的垃圾数据。
Fuzzy or non-repeatable reads: 当一个事务内,重新读取之前已经读过的数据时,发现读取到了其他的事务修改了某些数据或者删除了某些数据。
Phantom reads: (幻读)在一个事务内,重新执行一个查询时,发现有其他的已提交的事务插入了新的数据符合查询条件的数据。


2.2 ACID

Atomicity:事务是一个原子操作,对其数据的修改,要么全部执行,要么都不执行。
Consistent:连续性,一致性。必须保证一个事务内,连续两次执行同样的查询,执行结果是一样的。
Isolation:隔离性。事务提供了几种不同的隔离级别。隔离是值事务与事务之间的隔离,隔离强度越大,出现的问题就越少。
Durable:持久性。事务完成后,数据的变更是持久化的。MySQL InnoDB通过undo来保证持久性。

2.3 MVCC

在说隔离级别前,先了解一下MVCC(Multi-Version-Concurrency-Control)。
https://en.wikipedia.org/wiki/Multiversion_concurrency_control
大致意思就是:当数据库采用MVCC方案来设计事务时,通常是这样的:
当修改一行时,在提交之前,在内存中,不会使用新数据直接覆盖老数据,而是对老版本数据做一个标记,并创建一个新版本的数据。并且老版本的数据与新版本的数据是一个链式结构。如此一来,每一个修改的数据都有一个history chain。
    当删除一行数据时,在提交之前,不会真的将数据从内存中删除,只是做一个删除标记罢了。
    这里可以了解到变更的数据都有一个history chain。也就是说在内存中保留了相关Row的多个版本。保留多个版本,那么在进行并发读取时,就会大大提供并发量。
这也是MVCC最大的好处:读不加锁,读写不冲突。在读多写少的OLTP应用中,读写不重读非常重要。会极大的增加系统的并发性能。这也是为什么现阶段所有的RDBMS,都支持MVCC。

在运行时,不同的SQL语句采用不同的数据读取方式。根据读取方式的不同,分为snapshotcurrent 。上面说的读不加锁,读写不冲突是针对snapshot读而言的。而对于当前读(读取最新数据),还是要加锁的。
快照读:通常情况下,像这样简单的Select,是从snapshot读取的:
select * from table where ?;
当然也有例外,如果一个事务是READ_UNCOMMITED,即便是简单的Select,也会采用current读。

对于从临时表(包括嵌套查询生成的表)读取时,会采用current读。

Snapshot如何建立?
对于不同的隔离级别,Snapshot建立方式也是不同的,这里不做详细说明,在隔离级别小节中说明。

下面例子采用current读:
select * from table where ? lock in share mode;  // S Lock
select * from table where ? for update; // X Lock and Index Lock
insert into table values (…); // X Lock
update table set ? where ?; // X Lock
delete from table where ?; // X Lock



2.4 两阶段锁(2PL)管理

在MySQL中,锁采用两阶段处理方式,即分为加锁阶段、释放锁阶段。
在2.3中,将Insert、update、delete都划归到 current读 方式中。为什么呢?
下面看看Update执行过程:



Update分为多轮进行,每一轮都有信号交流过程:current read、lock & return、 update row、success。Delete也是如此的。

对于Insert则略有不同,因为他要先查找是否存在同样的Key。
从这个过程中,可以看出每一轮进行一个current read,并加锁,直到读完为止。Update完毕,并不会立即释放锁,而是接着执行,直到事务提交时才释放锁,insert, delete也同样如此:




2.5 隔离级别

READ UNCOMMITED:顾名思义,未提交的数据也可以读。 其实,这种隔离级别下,Select语句在执行时,能够读取到相关行的当前版本(也就是最新版本),所以一些没有提交的事务对数据的变更,也能读取到。故而可能发生 脏读了。
在此种隔离级别下,采用的是current读,所以也不会创建Snapshot了。

READ COMMITED:读取已提交的数据行。每一次都会读取已提交的数据行,所以每一次Select都要刷新到最新的Snapshot。所以他会发生不可重复读的问题,必然的,幻读也会发生。
    REPEATABLE READ:可重复读。为了保证能够在同一个事务内可重复读,在一个事务开启后,由第一条要采用Snapshot方式的SQL(该select SQL未必是当前事务中的)来触发Snapshot的建立。这个也是InnoDB默认的隔离级别。

             Session A                     Session B

           SET autocommit=0;        SET autocommit=0;
time
|          SELECT * FROM t;
|          empty set
|                                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           empty set
                                                      COMMIT;

           SELECT * FROM t;
           empty set

           COMMIT;

           SELECT * FROM t;
           ---------------------
           |    1    |    2    |
           ---------------------


SERIALIZABLE:序列化。对于该级别的事务,如果客户端采用了autocommit的事务,则直接提交,那么连接下的每一个SQL都是一个单独的事务。如果没有采用autocommit方式,则采用REPEATABLE READ隔离级别,但是会将所有的简单的Select转换为Select ... LOCK IN SHARE MODE,即转为current 读。


读数据一致性及允许的并发副作用

隔离级别

读数据一致性

脏读

不重复读

幻读

未提交读(Read uncommitted

最低级别,只能保证

不读取物理上损坏的数据

可能

可能

可能

已提交度(Read committed

语句级


可能

可能

可重复读(Repeatable read

事务级



可能

可序列化(Serializable

最高级别,事务级







2.6 SQL 加锁分析

·select ... from ... Lock IN SHARE MODE (也称为加锁read
默认情况下(REPEATABLE_READ),这个select SQL中如果使用了index,会在所有匹配行的index record上,加上shared next-key lock。如果select中使用的index是一个唯一索引的话,则只是在匹配行的index record上,加上shared index record Lock。
如果是更低的隔离级别READ_COMMITED、READ_COMMITED,则直接加shared index record。

·Select ... from (不加锁读)
如果执行该SQL的事务采用的是SERIALIZABLE级别,则会转为select ... from ... Lock IN SHARE MODE,也就是会变成加锁读。
在其它的隔离级别下,则不会加锁,是从snapshot中读取数据。

·select ... from ... FOR UPDATE
在REPEATABLE_READ、SERIALIZABLE隔离级别下,这个select SQL中如果使用了index, 会在匹配的行上加上exclusive next-key lock。如果select中使用的index是一个唯一索引的话,则只是在匹配行的index record上,加上exclusive index record Lock。
如果是更低的隔离级别READ_COMMITED、READ_COMMITED,则直接加exclusive index record。

·UPDATE ... WHERE ...
在REPEATABLE_READ、SERIALIZABLE隔离级别下,这个select SQL中如果使用了index, 会在匹配的行上加上exclusive next-key lock。如果select中使用的index是一个唯一索引的话,则只是在匹配行的index record上,加上exclusive index record Lock。

如果是更低的隔离级别READ_COMMITED、READ_COMMITED,则直接加exclusive index record。


当执行update操作时,如果是在clustered index record(聚簇index)上,会隐式对所有的受影响的二级索引都加上锁。例如 table test 有聚簇index (a,b,c),那么index record 就是由a,b,c组成的。如果更新时使用:update test set d=’1’ where a=’1’ and b=’2’;这个 SQL在执行时,会对与a, b匹配的所有的index record 都加上锁exclusive。

·DELETE... WHERE ...
在REPEATABLE_READ、SERIALIZABLE隔离级别下,这个select SQL中如果使用了index, 会在匹配的行上加上exclusive next-key lock。如果select中使用的index是一个唯一索引的话,则只是在匹配行的index record上,加上exclusive index record Lock。

·INSERT
Insert 时会先查找有没有匹配的index,如果有:会在匹配的index上加上shared index Lock。
如果没有,会在某个要插入的row上加上exclusive index lock (没有对gap 加锁,防止对并发插入产生影响)。



如果在执行上述几种SQL时,没有使用到index,会引发全表扫描。在全表扫描时,并不会锁住整个表的。
有人可能会问?为什么不是只在满足条件的记录上加锁呢?这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤,那么存储引擎层面就会将所有记录加锁后返回,然后由MySQL Server层进行过滤。因此也就把所有的记录,都锁上了。
注:在实际的实现中,MySQL有一些改进,在MySQL Server过滤条件,发现不满足后,会调用unlock_row方法,把不满足条件的记录放锁 (违背了2PL的约束)。这样做,保证了最后只会持有满足条件记录上的锁,但是每条记录的加锁操作还是不能省略的。

结论:若id列上没有索引,SQL会走聚簇索引的全扫描进行过滤,由于过滤是由MySQL Server层面进行的。因此每条记录,无论是否满足条件,都会被加上X锁。但是,为了效率考量,MySQL做了优化,对于不满足条件的记录,会在判断后放锁,最终持有的,是满足条件的记录上的锁,但是不满足条件的记录上的加锁/放锁动作不会省略。同时,优化也违背了2PL的约束。


3、DeadLock

事务A 持有row1的lock的同时,事务B持有row2的lock。然后事务A也想要去持有row2的Lock,但同时事务B又不会立刻马上释放row2的lock。这种情况下,就会在事务A中抛出DeadLock的错误。


相关配置项:
--innodb_deadlock_detect: 用于控制deadlock的检测。默认值ON,代表开启。如果 要关闭,设置为OFF。
--innodb_lock_wait_timeout: 一个事务等待一行lock的时间。超过这时间,就抛出Error,并执行rollback:
  1. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
复制代码
这个配置项在innodb_deadlock_detect 开启时是不会使用的,当innodb_deadlock_detect关闭时,都会使用的。这是因为:开启时,一旦发现死锁,就会立即触发Rollback。

    当发生死锁时,处理方案:
1) 找到相关的SQL
2) 通过Explain 分析SQL执行方式(主键索引、 唯一键扫描、范围扫描、全表扫描)
3)结合事务级别、SQL执行顺序等对死锁原因进行分析。
在线编程(http://www.anycodes.cn)&编程论坛(http://www.52exe.cn)感谢您的支持!
回复

使用道具 举报

发布主题 上个主题 下个主题 快速回复 返回列表 官方QQ群
在线客服
客 服 中 心
群 机 器 人
网站二维码
收 起 客 服

QQ|Archiver|手机版|小黑屋|Anycodes ( ICP14002806Anycodes在线编程

GMT+8, 2018-11-22 01:14 , Processed in 1.590455 second(s), 67 queries .

Powered by Anycodes

© 2001-2013 吉林市群龙科技有限公司 Inc.

快速回复 返回顶部 返回列表