当前位置: 澳门新濠3559 > 数据库 > 正文

无论是读还是写都只会加表锁,排他锁是写锁(

时间:2019-11-09 00:17来源:数据库
本文参考自MySQL官网5.6版本参考手册的14.5.1,此小节说明MySQL的锁分类,此外还有14.5.2小节和14.5.3小节详述事务隔离级别和各SQL语句的加锁模式,后两节将单独写2篇笔记。 官网参考: h

本文参考自MySQL官网5.6版本参考手册的14.5.1,此小节说明MySQL的锁分类,此外还有14.5.2小节和14.5.3小节详述事务隔离级别和各SQL语句的加锁模式,后两节将单独写2篇笔记。

官网参考:https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

InnoDB:Lock & Transaction,innodblock

InnoDB 是一个支持事务的Engine,要保证事务ACID,必然会用到Lock。就像在Java编程一下,要保证数据的线程安全性,必然会用到Lock。了解Lock,Transaction可以帮助sql优化、deadlock分析等。

  • 1、Lock
    • 1.1 Shared Lock & Exclusive Lock
    • 1.2 Intention Lock
    • 1.3 Index Record Lock
    • 1.4 Gap Lock
    • 1.5 Next-Key Lock
  • 2、Transaction
    • 2.1 事务过程中可能出现的问题
    • 2.2 ACID
    • 2.3 MVCC
    • 2.4 现阶段锁(2PL)管理
    • 2.5 隔离级别
    • 2.6 SQL 加锁分析
  • 3、DeadLock

 

MySQL把读操作分为两大类:锁定读和非锁定读(即locking read和nonlocking read),所谓非锁定读就是不对表添加事务锁的读操作,如Repeatable Read和Read Committed隔离级别下的select语句(可能脏读也算?)。MySQL的一致性非锁定读是通过MVCC机制实现的。锁定读是指添加事务锁的读操作,例如select for update和select lock in share mode语句。

1、Lock

InnoDB中,有多种类别的锁,下面将一一说明。

https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html

 

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。

 

关于MySQL的锁机制和事务隔离级别,参考以下两篇博客:

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分为两种:SX锁,也就是对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的,因为没有必要的。

SELECT * FROM child WHERE id = 100;

Id是索引,并且唯一的。此时执行上述SQL时,最多只会找到一行记录,就不需要持有gap lock,而是直接持有index record lock。

 

 

Myisam的锁比较容易理解,无论是读还是写都只会加表锁,表锁又分为read锁和write锁,可以使用如下方式手动加锁:

 

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。

 

 

--加表锁语句(同样适用于InnoDB):
lock tables
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE
--解表锁语句:
unlock tables
--如何观察InnoDB锁:
set @@global.innodb_status_output_locks=on; 
--这样show engine innodb statusG可以显示InnoDB额外的锁信息(锁太多时也无法完全显示),标准情况下只显示锁数目。

第一部分:概述

2、Transaction

Myisam的read、write表锁其实可以看做一种元数据锁。

锁定读、update和delete,这些操作通常会在扫描到的索引记录上添加record locks,InnoDB不关心这些行是否会被where条件过滤,因为InnoDB不记得具体的where条件,它只知道哪个索引范围被扫描过。

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

 

Dirty reads:  (脏读)一个事务A读到了另一个事务B还没有提交的数据(未提交的增删改的数据)。此时事务A就发生了脏读。因为有可能事务B不再提交这个数据,那么A就是读到的垃圾数据。

Fuzzy or non-repeatable reads: 当一个事务内,重新读取之前已经读过的数据时,发现读取到了其他的事务修改了某些数据或者删除了某些数据。

Phantom reads:  (幻读)在一个事务内,重新执行一个查询时,发现有其他的已提交的事务插入了新的数据符合查询条件的数据。

 

 

由于Myisam这样的锁机制,导致Myisam是一款读性能较好,并发写性能较差的存储引擎,本文主要讨论如今的MySQL默认存储引擎InnoDB的锁机制。

这些锁定添加的锁通常是next-key lock,这种锁既锁定扫描到的索引记录,也锁定索引间的gap。不过gap锁可以被显示的禁用,参考的Gap lock部分。

2.2 ACID

Atomicity:事务是一个原子操作,对其数据的修改,要么全部执行,要么都不执行。

Consistent:连续性,一致性。必须保证一个事务内,连续两次执行同样的查询,执行结果是一样的。

Isolation:隔离性。事务提供了几种不同的隔离级别。隔离是值事务与事务之间的隔离,隔离强度越大,出现的问题就越少。

Durable:持久性。事务完成后,数据的变更是持久化的。MySQL InnoDB通过undo来保证持久性。

 

 

 

2.3 MVCC

在说隔离级别前,先了解一下MVCC(Multi-Version-Concurrency-Control)。

大致意思就是:当数据库采用MVCC方案来设计事务时,通常是这样的:

当修改一行时,在提交之前,在内存中,不会使用新数据直接覆盖老数据,而是对老版本数据做一个标记,并创建一个新版本的数据。并且老版本的数据与新版本的数据是一个链式结构。如此一来,每一个修改的数据都有一个history chain。

    当删除一行数据时,在提交之前,不会真的将数据从内存中删除,只是做一个删除标记罢了。

    这里可以了解到变更的数据都有一个history chain。也就是说在内存中保留了相关Row的多个版本。保留多个版本,那么在进行并发读取时,就会大大提供并发量。

这也是MVCC最大的好处:读不加锁,读写不冲突。在读多写少的OLTP应用中,读写不重读非常重要。会极大的增加系统的并发性能。这也是为什么现阶段所有的RDBMS,都支持MVCC。

 

在运行时,不同的SQL语句采用不同的数据读取方式。根据读取方式的不同,分为snapshot读current 。上面说的读不加锁,读写不冲突是针对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

 

 

 

第二部分:InnoDB锁分类

如果在SQL执行时你需要对次级索引记录加X模式的行锁,那么InnoDB也会检索相应的主键索引并加锁。

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也同样如此:

 

 

 

InnoDB存储引擎在使用到索引时会使用行锁,否则使用表锁。InnoDB没有页锁,只有表锁行锁

 

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)

最高级别,事务级

 

 

 

一、InnoDB表锁有以下几种:

如果执行的SQL找不到合适的索引,InnoDB不得不去进行全表扫描,那么InnoDB会把表的每一个聚集索引记录都锁住,这可以看作是表级锁,同样参考的表锁部分。这种全表锁定会导致其他事务无法插入和更改(同样参考链接中的表锁兼容性部分),因此为SQL创建合适的索引是很有必要的,因为表锁(非意向锁)会导致DML操作阻塞。

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的约束。

 

 

InnoDB也可以使用lock tables ... read/write来添加元数据表锁。

 

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:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这个配置项在innodb_deadlock_detect 开启时是不会使用的,当innodb_deadlock_detect关闭时,都会使用的。这是因为:开启时,一旦发现死锁,就会立即触发Rollback。

 

    当发生死锁时,处理方案:

1) 找到相关的SQL

2) 通过Explain 分析SQL执行方式(主键索引、 唯一键扫描、范围扫描、全表扫描)

3)结合事务级别、SQL执行顺序等对死锁原因进行分析。

Transaction,innodblock InnoDB 是一个支持事务的Engine,要保证事务ACID,必然会用到Lock。就像在Java编程一下,要保证数据的线程安全性...

InnoDB支持的事务表锁有:

对于select...for update和select...lock in share mode这种锁定读来说,开始时InnoDB会锁定所有扫描的索引记录,但是最后会释放那些不符合条件的索引记录上的锁(例如被where语句过滤掉的行)。但是在某些情况下由于结果行与源表的联系丢失,导致这些行锁不会被释放,例如:union操作,被扫描的中间结果行会被插入到一个临时表中以便形成最终的结果集,在这种情况下锁定行与原表之间的联系丢失,那么剩余的扫描行直到整个SQL执行结束才会被释放(不是事务执行结束)。

S :其实是行锁,MySQL的行锁不会有额外的锁开销,因此我更愿意把这种全表的S行锁称作表S锁。

 

X :其实是行锁,MySQL的行锁不会有额外的锁开销,因此我更愿意把这种全表的X行锁称作表X锁。

第二部分:InnoDB中SQL语句的加锁类型

IS:表级意向共享锁,即表示事务有向底层资源加共享行锁的意向。如select ... lock in share mode语句,在加行锁之前会在表上现加IS锁,这样可以提高锁冲突检测的效率,同时也可以避免事务在表级添加会使其他事务行锁失效的表级锁。

1.在Repeatable Read和Read Committed事物隔离级别下,SELECT ... FROM语句是一种一致性非锁定读。而在SERIALIZABLE隔离级别下是锁定读,会在扫描的索引记录范围内添加Next-key行锁,不过如果扫描的是唯一索引,那么只会添加Record lock。

IX:表级意向独占锁,即表示事务有向底层资源加独占行锁的意向。一般来说delete、update语句和select ... for update语句都会在加行锁之前先加表级IX锁,除非未用到索引(此时直接加表级X锁)。

 

表锁的兼容性图:

2.SELECT ... FROM ... LOCK IN SHARE MODE在扫描到的索引记录上添加S模式的Next-key行锁,同样的如果扫描的是唯一索引,那么只会添加S模式的Record lock。

图片 1

 

此外表级锁还有一种比较特殊的锁:AUTO-INC Locks

3.SELECT ... FROM ... FOR UPDATE在扫描到的索引记录上添加X模式的Next-key行锁,同样的如果扫描的是唯一索引,那么只会添加X模式的Record lock。

这种锁只在向自增主键中插入记录时出现,由于自增主键在MySQL中较为常见,因此也算是经常会遇到的锁,这种锁是为自增主键设计的,无需和以上4钟锁检测冲突。

 

AUTO-INC Locks的锁机制:

4.UPDATE ... WHERE ...语句会在扫描到的所有记录上添加X模式的next-key lock(即便被删的行不存在),同样的如果扫描的是唯一索引,那么只会添加X模式的Record lock。

在向自增主键中插入记录时,其他insert事务都需要等待直到本事务的插入完成才能继续插入自增记录,注意是插入完成而不是本事务完成。这很好理解,因为需要保证自增主键的连贯性。但是如果你有超高的插入并发,那么肯定会带来性能问题。

 

因此InnoDB也提供了折中的方案,innodb_autoinc_lock_mode参数可以控制你是否使用这种锁,如果你的自增主键不需要严格连贯而且需要更高的insert并发,那么可以禁用掉这种锁。

5.当UPDATE语句修改的是主键索引时,InnoDB会隐式的将所有的次级索引锁定(二级索引都是用主键做书签的,因此修改主键索引是很耗资源的操作)。在插入二级索引记录或者为插入二级索引做重复性检查扫描时(unique index),update也会把受影响的二级索引锁定。

但是如果你做了主从复制,而且使用的是statement模式的binlog,那么禁用innodb_autoinc_lock_mode后可能造成主从自增主键不一致,尤其是遇到insert ... select ... from table_name;这种语句。此时需要改为row模式或mixed模式的binlog主从复制,因为row模式对SQL执行顺序不敏感,而mixed模式也会将可能影响主从复制的statement改为row模式传输。

 

那么最后还有个问题就是既需要超高插入并发又需要连贯自增,那该怎么办?

6.DELETE FROM ... WHERE ...语句会在扫描到的所有记录上添加X模式的next-key lock(即便被删的行不存在),同样的如果扫描的是唯一索引,那么只会添加X模式的Record lock。

凉拌~

 

 

7.INSERT语句会在插入的行上添加Record lock,Insert语句不会阻止其他事务在同一个gap上插入行。

二、InnoDB行锁有以下四种:

虽然Insert语句不使用gap行锁,但是会使用一种叫插入意向锁的gap锁,即Insert Inrention Locks。这种锁的作用是为添加行锁做锁冲突检测,具体示例参考的插入意向锁部分。

1.Record lock

此外INSERT语句还涉及到主键的重复性检测,示例说明如下:

即在索引上加的锁,lock_mode分为S和X两种模式。

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
--会话A执行:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
--会话B执行:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
--会话C执行:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
--最后会话A在执行:
ROLLBACK;
--最后发现会话B和C形成了死锁。

例如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;就会c1列的索引上添加X类型的Record lock。

因为开始时会话A在i=1上添加了X模式的行锁,会话BC在做重复性检测时发现已有i=1,于是在各自请求行上的一个S行锁,当A会话rollback后,BC的S行锁都获取到了,此时B和C都需要把S行锁转化为X行锁,但是都不愿意放弃自己的S锁,而S和X是互斥的,因此形成死锁。这个问题其实和SQL Server的更新锁出现的原因一样,只不过SQL Server通过U锁解决了此问题,即重复性检测使用的是U锁,而U锁只能有一个会话获取。

Record lock一定是加在索引记录上的,即便是一个没有定义任何索引的表,InnoDB也会创建一个隐式的聚集索引,在用到此索引时加Record lock。

 

2.Gap lock

8.INSERT ... ON DUPLICATE KEY UPDATE,这种插入语句和普通的INSERT语句区别在于,他会在发生重复性键值错误时向索引记录上添加X行锁,如果是主键那添加X模式的record lock行锁,如果是普通的唯一索引那添加X模式的next-key行锁。这姑且算是对7的死锁问题的一种解决办法吧。

即间隙锁,锁定不存在的索引记录,官方定义是:Gap lock用于锁定2个索引记录之间、或第一个索引记录之前、或最后一个索引记录之后的范围。

 

通常我们会把Record lock和Gap lock合起来用,称为Next-key lock,因此Gap lock就不多说了。

9.REPLACE语句可以看做是INSERT ... ON DUPLICATE KEY UPDATE的简写。

之所以设计Gap lock主要是为了解决幻读问题的,参考SQL Server的键范围锁。Gap锁是可以禁用的,你可以将数据库的全局隔离级别设置为read committed或者将innodb_locks_unsafe_for_binlog参数设置为1来禁用Gap lock,只是这样就会出现幻读,不过幻读一般并不是什么大问题,比如Oracle数据库的默认隔离级别下就无法避免幻读,不也大把人在用吗。

 

另外必须要说的一点是同一个gap上的Gap lock的S和X模式效果完全一样的,就算你加了一个X模式的gap lock,其他事务也能在同一个gap上再加一个X模式的gap lock,不会阻塞,当然仅限于同一个gap。

10.INSERT INTO T SELECT ... FROM S WHERE ...语句会在T表的每个被插入的行上添加X模式的record lock(无gap锁)。

3.Next-key lock

如果事务隔离级别被设置为READ COMMITTED,或者innodb_locks_unsafe_for_binlog设为1而且事物隔离级别不是SERIALIZABLE,那么这两种情况下InnoDB对S表执行一致性非锁定读。否则InnoDB会对S表上的每个行都添加S模式的next-key lock。

即Record lock和Gap lock的合体。例如SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;会在[10,20]之间的c1 index record上加lock_mode为X的next-key lock,也就是说会在[10,20]之间的所有存在的index record上加X模式的record lock,同时也会用X模式的gap锁锁定不存在的index record防止幻读,这两种锁加起来就称作next-key lock。

 

如果使用的索引是唯一索引,那么不加next-key lock的,只加record lock。

11.CREATE TABLE ... SELECT ...语句的加锁机制与INSERT INTO T SELECT ... FROM S WHERE ...完全一致。

再次提醒的是next-key lock其实并不存在而是Record lock和Gap lock的合体,show engine innodb statusG显示的结果也都是用Record lock来展示的,不过展示出的数目比较诡异看不懂源码的话不建议深究,这点比Oracle和Sqlserver差太远。这里我就要顺带吐槽一下官网手册了,毕竟是开源DB,一些前后矛盾和明显有歧义的解释也是让人很无奈。

REPLACE INTO t SELECT ... FROM s WHERE ...或者UPDATE t ... WHERE col IN (SELECT ... FROM s ...)这两种SQL语句对s表的行添加S模式的next-key行锁。

4.插入意向锁(Insert Intention Locks)

 

这个锁也是一个InnoDB的奇葩例子,不知道大家发现没InnoDB在谈IX IS还有行锁这些锁的时候基本不用insert语句来举例,这点如果是熟悉Oracle和SQL Server的人就会很困惑,因为增删改全都是DML语句,大家加锁机制基本相似的,无非就是表级意向锁+页级or行级锁的套路,但是InnoDB不是这样!!!insert语句和delete、update完全不是一路人!!关于Insert语句的加锁模式可以参考中的INSERT说明部分。

12.关于AUTO-INC Locks参考的AUTO-INC Locks部分。

这个锁用于表明:只要不是插入相同的index record,多个事务向同一个gap插入记录是不会阻塞的。

 

Insert语句的基本加锁模式为:表级IX锁--行级插入意向锁--行级锁。

13.如果表上有外键约束,那么任何需要做外键约束检测的DML语句都会在相应的外键上添加S模式的行锁。即便约束失败也会设置这些行锁。

插入意向锁其实是行级别的一种意向gap锁,既然有意向两字那么可以认定就是用于检测锁冲突的,是为在行级别获取X模式的record lock锁提前做检测。

 

用一个例子来解释更为明了:

14.LOCK TABLES也会在表上设置表锁,只是这种表锁并非是InnoDB层的表锁,而是MySQL层的表锁。因此如果死锁涉及到这些表锁时,InnoDB的死锁自动检测机制无法检测到这些表锁。而且由于MySQL层对InnoDB层的行锁机制并不清楚,因此此类表锁甚至可以加在正在使用行锁的InnoDB表上。不过这并不会危及到事务的完整性,具体说明详见:

--会话A执行:
CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
INSERT INTO child (id) values (90),(102);
START TRANSACTION;
SELECT * FROM child WHERE id > 100 FOR UPDATE;
--会话B执行:
INSERT INTO child (id) VALUES (101);

可以看到会话B被阻塞了,而show engine innodb statusG看到的锁等待如下:

图片 2

即insert语句想在(90,102)的gap上加个lock_mode=X的gap锁,也就是Insert Intention Lock,但是会话A的select for update语句已经在(100,102)的gap上添加了X模式的gap锁,这是一个与(90,102)不同但被包含在内的gap,于是被阻塞无法获取X模式的Insert Intention Gap Lock。

 

三、总结

MySQL的锁机制基本就如上所示了,但是了解InnoDB锁只是初步的,还必须结合事务隔离级别的概念去判断各种SQL的具体加锁机制,因为事务隔离级别会影响SQL的默认加锁模式。

MySQL的事务隔离级别定义也是遵循ANSI SQL92标准的,不过但凡是家数据库厂商都会说自己遵循SQL92标准,而事实是早已加料加的面目全非。当然这全都是为了能够提供更好的并发性能。例如Oracle也说自己遵循SQL92标准,结果四大隔离级别只支持2个,SQL Server也说自己支持,结果又多造出来2个事务隔离级别。

同样的MySQL也提供了4大基本的事务隔离级别,不同的隔离级别下加锁机制区别很大,参考:。

编辑:数据库 本文来源:无论是读还是写都只会加表锁,排他锁是写锁(

关键词: