MySQL常用存储引擎的锁机制

MySQL常用存储引擎有MyISAM、Memory、BDB、InnoDB等。

MySQL常用存储引擎的锁机制

MyISAM和MEMORY采用表级锁（table-level locking）；

BDB采用页面锁（page-leve locking）或表级锁，默认为页面锁；

InnoDB支持行级锁（row-level locking）和表级锁，默认为行级锁。

各种锁特点

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

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

页面锁：开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

InnoDB引擎中锁类型及兼容性

InnoDB锁的算法

1、Record Lock：单个行记录上的锁。

2、Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。

3、Next-key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。

Record Lock总是会锁住索引记录，如果InnoDB存储引擎表建立的时候没有设置任何一个索引，这时InnodB存储引擎会使用隐式的主键来进行锁定，在Repeatable Read隔离级别下，Next-key Lock 算法是默认的行记录锁定算法。

共享（S）锁与排他（X）锁

InnoDB 实现了标准的行级锁，包括两种：共享锁（简称 s 锁）、排它锁（简称 x 锁）。

共享锁（S锁）：允许持锁事务读取一行。 排他锁（X锁）：允许持锁事务更新或者删除一行。 如果事务 T1 持有行 r 的 s 锁，那么另一个事务 T2 请求 r 的锁时，会做如下处理：

T2 请求 s 锁立即被允许，结果 T1 T2 都持有 r 行的 s 锁 T2 请求 x 锁不能被立即允许 如果 T1 持有 r 的 x 锁，那么 T2 请求 r 的 x、s 锁都不能被立即允许，T2 必须等待T1释放 x 锁才可以，因为X锁与任何的锁都不兼容。

意向锁

意向共享锁( IS 锁)：事务想要获得一张表中某几行的共享锁 意向排他锁( IX 锁)： 事务想要获得一张表中某几行的排他锁 比如：事务1在表1上加了S锁后，事务2想要更改某行记录，需要添加IX锁，由于不兼容，所以需要等待S锁释放；如果事务1在表1上加了IS锁，事务2添加的IX锁与IS锁兼容，就可以操作，这就实现了更细粒度的加锁。

InnoDB存储引擎中锁的兼容性如下表：

记录锁（Record Locks）

记录锁是最简单的行锁，仅仅锁住一行。如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE 记录锁永远都是加在索引上的，即使一个表没有索引，InnoDB也会隐式的创建一个索引，并使用这个索引实施记录锁。 会阻塞其他事务对其插入、更新、删除 记录锁的事务数据（关键词：lock_mode X locks rec but not gap），记录如下：

RECORD LOCKS space id 58 page no 3 n bits 72 index PRIMARY of table test.t trx id 10078 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 00000000274f; asc 'O;; 2: len 7; hex b60000019d0110; asc ;; 4.间隙锁（Gap Locks）

间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。 使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。 间隙锁只阻止其他事务插入到间隙中，他们不阻止其他事务在同一个间隙上获得间隙锁，所以 gap x lock 和 gap s lock 有相同的作用。

Next-Key Locks

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

插入意向锁（Insert Intention）

插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，亦即多个事务在相同的索引间隙插入时如果不是插入间隙中相同的位置就不需要互相等待。 假设有索引值4、7，几个不同的事务准备插入5、6，每个锁都在获得插入行的独占锁之前用插入意向锁各自锁住了4、7之间的间隙，但是不阻塞对方因为插入行不冲突。

MySQL中的事务

事务的 四个特征（ACID）

事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性。

1 、原子性。事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

2 、一致性。事 务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。

3 、隔离性。一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

4 、持续性。也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

MySQL的四种隔离级别

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

出现问题

这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。

脏读（Drity Read）

脏读即一个事务可以读到另一个事务中未提交（同一份）的数据，这违反了数据库的隔离性。

脏读发生的条件：需要事务的隔离级别为Read uncommitted。

如果某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读(Non-repeatable read)

不可重复读：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读(Phantom Read)

幻读：在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

丢失更新

如何避免丢失更新：让事务变成串行操作，而不是并发的操作，即对每个事务开始—对读取记录加排他锁。

不可重复读与脏读的区别

不可重复读与脏读的区别是：脏读是读到未提交的数据，而不可重复读读到的是已经提交的数据。

一般来说，不可重复读是可以接受的，在InnoDB存储引擎中，通过使用Next-Key Lock算法来避免不可重复读的问题。

值得注意的是，默认情况下InnoDB存储引擎不会回滚超时引发的错误异常。

在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示：

一致性的非锁定读

一致性的非锁定行读（consistent nonlocking read）是指InnoDB存储引擎通过行多版本控制（multi versioning）的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE、UPDATE操作，这是读取操作不会因此而会等待行上锁的释放，相反，InnoDB会去读取行的一个快照数据。

之所以称其为非锁定读，因为不需要等待访问的行上X锁的释放。快照数据是指该行之前版本的数据，该实现是通过undo来实现的。但是在不同事务隔离级别下，读取的方式不同，并不是每个事务隔离级别下读取的都是一致性读。

例如：

对于read committed的事务隔离级别，他总是读取行的最新版本，如果行被锁定了，则读取该行版本的最新一个快照。

对于repeatable read(innoDB存储引擎的默认隔离级别),总是读取事务开始时的行数据。

非锁定读的机制大大提高了数据读取的并发性，在Innodb存储引擎默认设置下，这是默认的读取方式，但是在某些情况下，可以对读进行加锁，比如：

1、显式对读进行加锁，如使用 select — for update ;select — lock in share mode

2、在外键的插入和更新上，因为在外键的插入和更新上，对于数据的隔离性要求较高，在插入前需要扫描父表中的记录是否存在，所以，在外键的插入删除上，InnoDB会使用加S锁的方式来实现。

死锁相关问题

死锁发生的条件

互斥条件：一个资源每次只能被一个进程使用；请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺；循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

死锁检测

Innodb检测死锁有两种情况，一种是满足循环等待条件，还有另一种策略：锁结构超过mysql配置中设置的最大数量或锁的遍历深度超过设置的最大深度时，innodb也会判断为死锁（这是提高性能方面的考虑，避免事务一次占用太多的资源）。

因循环等待条件而产生的死锁只有可能是四种形式：两张表两行记录交叉申请互斥锁、同一张表则存在主键索引锁冲突、主键索引锁与非聚簇索引锁冲突、锁升级导致的锁等待队列阻塞。

死锁避免

1.如果使用insert…select语句备份表格且数据量较大，在单独的时间点操作，避免与其他sql语句争夺资源，或使用select into outfile加上load data infile代替 insert…select，这样不仅快，而且不会要求锁定 2. 一个锁定记录集的事务，其操作结果集应尽量简短，以免一次占用太多资源，与其他事务处理的记录冲突。 3.更新或者删除表格数据，sql语句的where条件都是主键或都是索引，避免两种情况交叉，造成死锁。对于where子句较复杂的情况，将其单独通过sql得到后，再在更新语句中使用。 4. sql语句的嵌套表格不要太多，能拆分就拆分，避免占有资源同时等待资源，导致与其他事务冲突。 5. 对定点运行脚本的情况，避免在同一时间点运行多个对同一表进行读写的脚本，特别注意加锁且操作数据量比较大的语句。 6.应用程序中增加对死锁的判断，如果事务意外结束，重新运行该事务，减少对功能的影响。

死锁解决

1)先执行show processlist找到死锁线程号.然后Kill pid

2)Show innodb status检查引擎状态 ,可以看到哪些语句产生死锁

3)查看information_schema架构下的innodb_locks、innodb_trx、innodb_lock_waits等表

参考

死锁排查分析 MySql实现高性能高并发的计数器与INSERT…ON DUPLICATE KEY UPDATE MySQL优化--INSERT ON DUPLICATE UPDATE死锁

文章转自：InnoDB存储引擎中的锁及带来的问题