MySQL中的锁

前言

在数据库中，除传统计算资源（CPU、RAM、I\O等）的争抢，数据也是一种供多用户共享的资源。如何保证数据并发访问的一致性，有效性，是所有数据库必须要解决的问题。锁的作用就是用于管理对共享资源的并发访问，保证数据库的完整性和一致性。

首先，在介绍数据库的锁之前，要先搞清楚一些概念。我们常说的乐观锁和悲观锁都是一种概念或思想，不仅仅局限于关系型数据库系统中有乐观锁和悲观锁的概念。无论是乐观锁还是悲观锁都是数据库中实现并发控制的不同方式，不要把他们跟数据库中提供的锁机制（行锁、表锁、排他锁、共享锁）混为一谈。事实上，在数据库中，悲观锁正式利用数据库本身提供的锁机制来实现的。

1、锁的类型

1.1、行锁

MySQL中锁定 粒度最⼩ 的⼀种锁，只针对当前操作的⾏进⾏加锁。 ⾏级锁能⼤⼤减少数据库操作的冲突。其加锁粒度最⼩，并发度⾼，但加锁的开销也最⼤，加锁慢，会出现死锁。行级锁分为共享锁（读锁）和 排他锁（写锁）。InnoDB引擎⽀持行锁。

1.1.1、如何上锁

隐式上锁（默认，自动加锁自动释放）
1、select //不会上锁
2、insert、update、delete //上写锁

显式上锁（手动）
1、select * from tableName lock in share mode;//读锁
2、select * from tableName for update;//写锁
解锁（手动）
1、提交事务（commit）
2、回滚事务（rollback）
3、kill 阻塞进程

1.1.2、行锁的实现算法

1.1.2.1、Record Lock 锁

1、单个行记录上的锁。
2、Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表建立的时候没有设置任何一个索引，这时InnoDB存储引擎会使用隐式的主键来进行锁定。注意，innodb一定存在聚簇索引，因此行锁最终都会落到聚簇索引上！

1.1.2.2、Gap Lock 锁

1、当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引加锁，锁定⼀个范围，不包括记录本身。
2、优点：解决了事务并发的幻读问题
3、不足：因为query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
4、间隙锁有一个致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成锁定的时候无法插入锁定键值范围内任何数据。在某些场景下这可能会对性能造成很大的危害。
5、其目的只有一个，防止其他事物插入数据。在Read Committed隔离级别下，不会使用间隙锁。隔离级别比Read Committed低的情况下，也不会使用间隙锁，如隔离级别为Read Uncommited时，也不存在间隙锁。当隔离级别为Repeatable Read和Serializable时，就会存在间隙锁。

1.1.2.3、Next-key Lock 锁

1、同时锁住数据+间隙锁。
2、在Repeatable Read隔离级别下，Next-key Lock 算法是默认的行记录锁定算法。

1.2、表锁

MySQL中锁定 粒度最⼤ 的⼀种锁，对当前操作的整张表加锁，实现简单，资源消耗也⽐较少，加锁快，不会出现死锁。其锁定粒度最⼤，触发锁冲突的概率最⾼，并发度最低。表级锁分为共享锁（读锁）和 排他锁（写锁）。MyISAM和 InnoDB引擎都⽀持表级锁。

1.1.1、如何上锁

隐式上锁（默认，自动加锁自动释放）
1、select //上读锁
2、insert、update、delete //上写锁

显式上锁（手动）
lock table tableName read;//读锁
lock table tableName write;//写锁
解锁（手动）
unlock tables;//所有锁表

1.3、Innodb中的行锁与表锁

在Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候或只锁住一行呢？

InnoDB行锁是通过给索引上的索引项加锁来实现的，InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！值得注意的是，数据库对于主键会自动生成唯一索引，所以主键也是一个特殊的索引。即通过主键进行查询也能实现行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。这里有一点需要强调的是并不是用表锁来实现锁表的操作，而是利用了Next-Key Locks，也可以理解为是用了行锁+间隙锁来实现锁表的操作。

innodb对于⾏的查询使⽤next-key lock。Next-locking keying为了解决Phantom Problem幻读问题。

1.5、共享锁，排它锁

行级锁分为共享锁和排他锁两种。

共享锁(Share Lock，SLock)
共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

排他锁（eXclusive Lock，XLock）
排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的锁。获准排他锁的事务既能读数据，又能修改数据。

意向共享锁(IS锁) 一个事务在获取（任何一行/或者全表）S锁之前，一定会先在所在的表上加IS锁。

意向排他锁(IX锁) 一个事务在获取（任何一行/或者全表）X锁之前，一定会先在所在的表上加IX锁。

意向锁存在的目的?如果一个事务想要对整个表加X锁，就需要先检测是否有其它事务对该表或者该表中的某一行加了锁，这种检测非常耗时。有了意向锁之后，只需要检测整个表是否存在IX/IS/X/S锁就行了

假设事务T1，用X锁来锁住了表上的几条记录，那么此时表上存在IX锁，即意向排他锁。那么此时事务T2要进行LOCK TABLE … WRITE的表级别锁的请求，可以直接根据意向锁是否存在而判断是否有锁冲突。

1.6.1、三级封锁协议

• 一级封锁协议：事务在修改数据之前必须先对其加X锁，直到事务结束才释放。可以解决丢失修改问题（两个事务不能同时对一个数据加X锁，避免了修改被覆盖）；
• 二级封锁协议：在一级的基础上，事务在读取数据之前必须先加S锁，读完后释放。可以解决脏读问题（如果已经有事务在修改数据，就意味着已经加了X锁，此时想要读取数据的事务并不能加S锁，也就无法进行读取，避免了读取脏数据）；
• 三级封锁协议：在二级的基础上，事务在读取数据之前必须先加S锁，直到事务结束才能释放。可以解决不可重复读问题（避免了在事务结束前其它事务对数据加X锁进行修改，保证了事务期间数据不会被其它事务更新）

1.6.2、死锁

死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的假象。多个事务同时锁定同一个资源时，也会产生死锁。数据库系统实现了各种死锁检测和死锁超时的机制，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。

1.6.2、两段锁协议

事务必须严格分为两个阶段对数据进行加锁和解锁的操作，第一阶段加锁，第二阶段解锁。也就是说一个事务中一旦释放了锁，就不能再申请新锁了。
可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同，这种并发调度策略才是可串行化调度，即具有可串行性。事务遵循两段锁协议是保证可串行化调度的充分条件。

值得注意的是当一个事务获取到了某一个数据库对象的锁之后，并不是当前事务不需要操作它了之后，这个锁就会马上释放掉，这个锁会一直被这个事务持有，直到这个事务被提交或回滚后，这个锁才会被释放掉。所以，在当前事务还没有结束的时候，任何其他事务尝试获取这个锁的时候，都会被阻塞。知道当前事务提交或回滚后，前提事务才可以获取到这把锁。

1.7、乐观锁、悲观锁

悲观锁
悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法。

悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度(悲观) ，因此，在整个数据处理过程中，将数据处于锁定状态。

悲观锁的实现，往往依靠数据库提供的锁机制 （也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）

在数据库中，悲观锁的流程如下：
（1）在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。
（2）如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。
（3）如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

乐观锁—乐观锁是用来解决写—写冲突的无锁并发控制
乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。

乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。**一般的实现乐观锁的方式就是记录数据版本。实现数据版本可以通过使用版本号或使用时间戳。**实现流程如下：

（1）为数据表增加一个表示版本标识的字段，用于存储版本号或时间戳
（2）当读取数据时，将版本标识的值一同读出
（3）当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本标识与第一次取出来的版本标识值相等，则同时更新数据和版本号，否则认为是过期数据，返回错误给用户处理

2、快照读（MVCC）和当前读

为什么InnoDB上了写锁，别的事务还能读操作？
因为InnoDB有MVCC机制，可以使用快照读，而不会被阻塞。

<高性能MySQL>中对MVCC的部分介绍

1. MySQL的大多数事务型存储引擎实现的其实都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制(MVCC)。不仅是MySQL，包括Oracle,PostgreSQL等其他数据库系统也都实现了MVCC, 但各自的实现机制不尽相同，因为MVCC没有一个统一的实现标准。
2. 可以认为MVCC是行级锁的一个变种, 但是它在很多情况下避免了加锁操作, 因此开销更低。虽然实现机制有所不同, 但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
3. MVCC的实现方式有多种，典型的有乐观(optimistic)并发控制 和 悲观(pessimistic)并发控制。 MVCC只在 READ COMMITTED和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别和MVCC不兼容, 因为 READ UNCOMMITTED总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE则会对所有读取的行都加锁。

一般认为MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间（存的并不是实际的时间值，而是系统的版本号）。每开始一个新的事务，系统版本号就会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号作比较。

三个隐藏字段
InnoDB会为每个使用InnoDB存储引擎的表添加三个隐藏字段，用于实现数据多版本以及聚集索引，他们的作用如下:

• DB_TRX_ID(6字节)：它是最近一次更新或者插入或者删除该行数据的事务ID(若是删除，则该行有一个删除位更新为已删除。但并不是真正的进行物理删除，当InnoDB丢弃为删除而编写的更新撤消日志记录时，它才会物理删除相应的行及其索引记录。此删除操作称为清除，速度非常快)
• DB_ROLL_PTR(7字节)： 回滚指针，指向当前记录行的undo log信息(指向该数据的前一个版本数据)
• DB_ROW_ID(6字节)：随着新行插入而单调递增的行ID。InnoDB使用聚集索引，数据存储是以聚集索引字段的大小顺序进行存储的，当表没有主键或唯一非空索引时，innodb就会使用这个行ID自动产生聚簇索引。如果表有主键或唯一非空索引，聚簇索引就不会包含这个行ID了。这个DB_ROW_ID跟MVCC关系不大。

Read View
read view是读视图，其实就相当于一种快照，里面记录了系统中当前活跃事务的ID以及相关信息，主要用途是用来做可见性判断，判断当前事务是否有资格访问该行数据(详情下解)。read view有多个变量，这里将关键变量进行描述，为下文做铺垫。

trx_ids: 它里面的trx_ids变量存储了活跃事务列表，也就是Read View开始创建时其他未提交的活跃事务的ID列表。例如事务A在创建read view(快照)时，数据库中事务B和事务C还没提交或者回滚结束事务，此时trx_ids就会将事务B和事务C的事务ID记录下来。
low_limit_id： 目前出现过的最大的事务ID+1，即下一个将被分配的事务ID。
up_limit_id： 活跃事务列表trx_ids中最小的事务ID，如果trx_ids为空，则up_limit_id 为 low_limit_id，虽然该字段名为up_limit,但在trx_ids中的活跃事务号是降序的，所以最后一个为最小活跃事务ID。
creator_trx_id： 当前创建read view的事务的ID。

Undo log
Undo log中存储的是老版本数据，当一个事务需要读取记录行时，如果当前记录行不可见，可以通过回滚指针顺着undo log链找到满足其可见性条件的记录行版本。

在InnoDB里，undo log分为如下两类：
①insert undo log : 事务对insert新记录时产生的undo log, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。
②update undo log : 事务对记录进行delete和update操作时产生的undo log，不仅在事务回滚时需要，快照读也需要，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被purge线程删除。

Purge线程：InnoDB删除一个行记录时，并不是立刻物理删除，而是将该行数据的DB_TRX_ID字段更新为做删除操作的事务ID，并将删除位deleted_bit设置为true(已删除)，将其放入update undo log中。为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。purge线程自己也维护了一个read view，如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

MVCC更新操作的实现原理:

1. 事务以排他锁的形式修改原始数据
2. 把修改前的数据存放于undo log，通过回滚指针与主数据关联
3. 修改成功（commit）啥都不做，失败则恢复undo log中的数据（rollback）

MVCC更新操作的实现原理:
InnoDB中，事务在第一次进行普通的select查询时，会创建一个read view(快照)，用于可见性判断，事务只能查询到行记录对于事务来说可见的数据版本。可见性判断是通过行记录的DB_TRX_ID(最近一次插入/更新/删除该行记录的事务ID)以及read view中的变量比较来判断。
(1） 如果 DB_TRX_ID<up_limit_id，则表明这个行记录最近一次更新在当前事务创建快照之前就已经提交了，该记录行的值对当前事务是可见的，当前事务可以访问该行记录，跳到步骤(4)。
(2) 如果DB_TRX_ID>=low_limit_id，则表明这个行记录最近一次更新是快照创建之后才创建的事务完成的，该记录行的值对当前事务是不可见的，当前事务不可以访问该行记录。因此当前事务只能访问比该行记录更旧的数据版本。通过该记录行的DB_ROLL_PTR指针，找到更旧一版的行记录，取出更旧一版的行记录的事务号DB_TRX_ID，然后跳到步骤(1)重新判断当前事务是否有资格访问该行记录。
(3) 如果up_limit_id<=DB_TRX_ID<low_limit_id，则表明对这个行记录最近一次更新的事务可能是活跃列表中的事务也可能是已经成功提交的事务(事务ID号大的事务可能会比ID号小的事务先进行提交)，比如说初始时有5个事务在并发执行，事务ID分别是1001~1005，1004事务完成提交，1001事务进行普通select的时候创建的快照中活跃事务列表就是1002、1003、1005。因此up_limit_id就是1002，low_limit_id就是1006。对于这种情况，我们需要在活跃事务列表中进行遍历(因为活跃事务列表中的事务ID是有序的，因此用二分查找)，确定DB_TRX_ID是否在活跃事务列表中。
(3.1) 若不在，说明对这个行记录最近一次更新的事务是在创建快照之前提交的事务，此行记录对当前事务是可见的，也就是说当前事务有资格访问此行记录，跳到步骤(4)。
(3.2) 若在，说明对这个行记录最近一次更新的事务是当前活跃事务，在快照创建过程中或者之后完成的数据更新，此行记录对当前事务是不可见的(若可见则会造成脏读、不可重复读等问题)。因此当前事务只能访问该行记录的更旧的版本数据。通过该记录行的DB_ROLL_PTR指针，找到更旧一版的行记录，取出更旧一版的行记录的事务号DB_TRX_ID，然后跳到步骤(1)重新判断当前事务是否有资格访问该行记录。
(4) 可以访问，将该行记录的值返回。

MVCC是用来解决 “读-写冲突” 的无锁并发控制：
同一个数据有多个版本，事务开启时看到是哪个版本就是哪个版本，最大的好处就是读写不冲突。

快照读， 读取的是记录的可见版本（可能是历史版本，即最新的数据可能正在被当前执行的事务并发修改），不会对返回的记录加锁。
当前读， 读取的是记录的最新版本，并且会对返回的记录加锁，保证其他事务不会并发修改这条记录。
快照读 是通过MVVC(多版本控制)和undo log来实现的，当前读 是通过加record lock(记录锁)和gap lock(间隙锁)来实现的。
innodb在快照读的情况下并没有真正的避免幻读, 但是在当前读的情况下避免了不可重复读和幻读!!!

MVCC如何实现RC和RR
MVCC对两个隔离级别实现的差异在其产生的read view(快照)的次数不同。

RC:读取已提交隔离级别，避免了脏读，存在不可重复读、幻读问题。MVCC对该级别的实现就是每次进行普通的select查询，都会产生一个新的快照(不同时间，当前活跃的事务不同，行记录最近一次更新的事务ID也可能不同)。就相当于二级锁协议，进行读操作需要加读锁，读完就释放锁，虽然并发性更好且避免了脏读，但会存在不可重复读。

RR:可重复读隔离级别，避免了脏读和不可重复读，存在幻读问题。MVCC对该级别的实现就是在当前事务中只有第一次进行普通的select查询，才会产生快照，此后这个事务一直使用这一个快照进行快照查，相当于三级锁协议，进行读操作需要加读锁，事务结束才释放。避免了不可重复读。但存在幻读，禁止幻读可以通过Next-Key Locks算法的间隙锁和记录锁实现。

3、分析例子

1、下面这个sql 执行的是快照读，读的是数据库记录的快照版本，是不加锁的。

select * from table where id = ?;

2、那么这个sql 是当前读，会对读取记录加S锁 (共享锁)。

select * from table where id = ? lock in share mode;

3、最后下面这个sql 会对读取记录加X锁（排它锁），这是悲观锁的一种实现形式。

select * from table where id = ? for update

上面两个加锁查询的sql，是加的表锁（将整个表锁住）还是加的行锁（将行记录锁住）呢？

针对这点，我们先回忆一下事务的四个隔离级别，他们由弱到强如下所示:

Read Uncommited(RU)：读未提交，一个事务可以读到另一个事务未提交的数据！
Read Committed (RC)：读已提交，一个事务可以读到另一个事务已提交的数据!
Repeatable Read (RR):可重复读，加入间隙锁，一定程度上避免了幻读的产生！
Serializable：串行化，该级别下读写串行化，且所有的select语句后都自动加上lock in share mode，即使用了共享锁。因此在该隔离级别下，使用的是当前读，而不是快照读。

前面介绍过这样一个说法：InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！但要注意的是InnoDB使用表锁其实是使用了Next_key Locks实现了锁表的操作。 接下来分析下，假设有表数据如下，pId为主键索引

执行语句(name列无索引) select * from table where name =aaafor update
那么此时在pId=1,2,7这三条记录上存在行锁(把行锁住了)。另外，在(-∞,1)(1,2)(2,7)(7,+∞)上存在间隙锁(把间隙锁住了)。因此， 给人一种整个表锁住的错觉！此外，之所以能够锁表，是通过行锁+间隙锁来实现的。还有，RU和RC都不存在间隙锁，因此，该说法只在RR和Serializable中是成立的。如果隔离级别为RU和RC，无论条件列上是否有索引，都不会锁表，只锁行！

后续会再补充一些加锁分析的例子。。。