MySQL系列-03深入理解MySql锁

1.ACID 特性

   在关系型数据库管理系统中，一个逻辑工作单元要成为事务，必须满足这 4 个特性，即所谓的 ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

1.1原子性

原子性：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

1. 每一个写事务，都会修改BufferPool，从而产生相应的Redo/Undo日志。
2. 如果事务提交了，此时Buffer Pool的脏页没有刷盘 ，此时数据库挂了，那在数据库再次启动之后，可以通过 Redo 日志将其恢复出来，以保证脏页写的数据不会丢失。
3. 如果事务没提交，但是Buffer Pool的脏页刷盘了，此时数据库挂了，就需要通过Undo来实现数据撤销  。

1.2持久性

   持久性：指的是一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，后续的操作或故障不应该对其有任何影响，不会丢失。

   一个“提交”动作触发的操作有：binlog落地、发送binlog、存储引擎提交、flush_logs，check_point、事务提交标记等。redo log在系统Crash重启之类的情况时，可以修复数据，从而保障事务的持久性。

1.3隔离性

   隔离性：指的是一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他的并发事务是隔离的。

InnoDB 支持的隔离性有 4 种，隔离性从低到高分别为：读未提交、读提交、可重复读、可串行化。

1.4一致性

一致性：指的是事务开始之前和事务结束之后，数据库的完整性限制未被破坏 。

一致性也可以理解为数据的完整性。数据的完整性是通过原子性、隔离性、持久性来保证的，而这3个特性又是通过 Redo/Undo 来保证的。

2.事务控制的演进

2.1并发事务

事务并发处理可能会带来一些问题，比如：更新丢失、脏读、不可重复读、幻读等。

1. 更新丢失
   当两个或多个事务更新同一行记录，会产生更新丢失现象。可以分为回滚覆盖和提交覆盖。

• • 回滚覆盖：一个事务回滚操作，把其他事务已提交的数据给覆盖了。
  • 提交覆盖：一个事务提交操作，把其他事务已提交的数据给覆盖了。

1. 脏读
   一个事务读取到了另一个事务修改但未提交的数据。
2. 不可重复读
   一个事务中多次读取同一行记录不一致，后面读取的跟前面读取的不一致，影响的是单个数据项的值
3. 幻读

是指在一个事务中，两次执行相同的查询，但由于其他事务的插入或删除操作，导致第二次查询的结果集与第一次不同，多了或少了几行记录。

不可重复读和幻读区别：

1. 不可重复读影响的是单个数据项的值； 幻读影响的是查询结果集中的记录数
2. 不可重复读由其他事务对数据的更新操作引起；幻读由其他事务对数据的插入或删除操作引起

2.2排队

   最简单的方法，就是完全顺序执行所有事务的数据库操作，不需要加锁，简单的说就是全局排队。序列化执行所有的事务单元，数据库某个时刻只处理一个事务操作，特点是强一致性，处理性能低。

2.3排他锁

   引入锁之后就可以支持并发处理事务，如果事务之间涉及到相同的数据项时，会使用排他锁，或叫互斥锁，先进入的事务独占数据项以后，其他事务被阻塞，等待前面的事务释放锁。

注意，在整个事务1结束之前，锁是不会被释放的，所以，事务2必须等到事务1结束之后开始。

2.4读写锁

读和写操作：读读、写写、读写、写读。
读写锁就是进一步细化锁的颗粒度，区分读操作和写操作，让读和读之间不加锁，这样下面的两个事务就可以同时被执行了。

读写锁，可以让读和读并行，而读和写、写和读、写和写这几种之间还是要加排他锁。

2.5MVCC

2.5.1概念

   MVCC（Multi Version Concurrency Control）被称为多版本控制，也就是Copy on Write的思想， 是指在数据库中为了实现高并发的数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本。多版本控制很巧妙地将稀缺资源的独占互斥转换为并发，大大提高了数据库的吞吐量及读写性能。MVCC除了支持读和读并行，还支持读和写、写和读的并行，但为了保证一致性，写和写是无法并行的。

2.5.1.1当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select ... lock in share mode(共享锁)，select ...for update、update、insert、delete(排他锁)都是一种当前读。

2.5.1.2快照读

简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

隔离级别	快照读
Read Committed	每次select，都生成一个快照读
Repeatable Read	开启事务后第一个select语句才是快照读的地方
Serializable	快照读会退化为当前读

默认的RR隔离级别下，开启事务后第一个select语句才是快照读的地方，后面执行相同的select语句都是从快照中获取数据，可能不是当前的最新数据，这样也就保证了可重复读。


全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的隐式字段、undo log日志、readView。

2.5.2隐藏字段

当我们创建了上面的这张表，我们在查看表结构的时候，就可以显式的看到这三个字段。 实际上除了这三个字段以外，InnoDB还会自动的给我们添加三个隐藏字段及其含义分别是：

隐藏字段	含义
DB_TRX_ID	最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
DB_ROLL_PTR	回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
DB_ROW_ID	隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

而上述的前两个字段是肯定会添加的， 是否添加最后一个字段DB_ROW_ID，得看当前表有没有主键，如果有主键，则不会添加该隐藏字段。 可以通过ibd2sdi查看:

ibd2sdi xxx.ibd

2.5.3undolog

2.5.3.1介绍

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。

而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。
 

2.5.3.2版本链

不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

原始表数据:

有四个并发事务同时在访问这张表:

版本链为:

2.5.4readview

2.5.4.1介绍

ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。
ReadView中包含了四个核心字段：

字段	含义
m_ids	当前活跃的事务ID集合
min_trx_id	最小活跃事务ID
max_trx_id	预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_id	ReadView创建者的事务ID

2.5.4.2规则

在readview中就规定了版本链数据的访问规则 :trx_id 代表当前undolog版本链对应事务ID 。

条件	是否可以访问	说明
trx_id ==creator_trx_id	可以访问该版本	成立，说明数据是当前这个事务更改的。
trx_id < min_trx_id	可以访问该版本	成立，说明数据已经提交了。
trx_id > max_trx_id	不可以访问该版本	成立，说明该事务是在ReadView生成后才开启。
min_trx_id <= trx_id<= max_trx_id	如果trx_id不在m_ids中，是可以访问该版本的	成立，说明数据已经提交。

不同的隔离级别，生成ReadView的时机不同：

1. READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
2. REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

2.5.5原理分析

2.5.5.1RC隔离级别

RC隔离级别下，在事务中每一次执行快照读时生成ReadView。
在事务5中，查询了两次id为30的记录，由于隔离级别为Read Committed，所以每一次进行快照读都会生成一个ReadView，那么两次生成的ReadView如下。

1. 第一次查询

1. 第二次查询

2.5.5.2RR隔离级别

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。 而RR 是可重复读，在一个事务中，执行两次相同的select语句，查询到的结果是一样的

在RR隔离级别下，只是在事务中第一次快照读时生成ReadView，后续都是复用该ReadView，那么既然ReadView都一样， ReadView的版本链匹配规则也一样， 那么最终快照读返回的结果也是一样的。

2.5.6小结

MVCC的实现原理就是通过 InnoDB表的隐藏字段、UndoLog 版本链、ReadView来实现的。而MVCC + 锁，则实现了事务的隔离性。 而一致性则是由redolog 与 undolog保证 。

3.事务隔离级别

3.1隔离级别类型

   前面提到的“更新丢失”、”脏读”、“不可重复读”和“幻读”等并发事务问题，其实都是数据库一致性问题，为了解决这些问题，MySQL数据库是通过事务隔离级别来解决的 。

1. 数据库的事务隔离级别越高，并发问题就越小，效率越低
2. 事务隔离级别，针对Innodb引擎，和MyISAM引擎没有关系 。
3. MySQL默认隔离级别：可重复读 ；Oracle、SQLServer默认隔离级别：读已提交

3.2事务隔离级别和锁的关系

1. 事务隔离级别是SQL92定制的标准，相当于事务并发控制的整体解决方案，本质上是对锁和MVCC使用的封装，隐藏了底层细节。
2. 锁是数据库实现并发控制的基础，事务隔离性是采用锁来实现，对相应操作加不同的锁，就可以防
   止其他事务同时对数据进行读写操作。
3. 对用户来讲，首先选择使用隔离级别，当选用的隔离级别不能解决并发问题或需求时，才有必要在开发中手动的设置锁。

3.3MySQL隔离级别控制

• 查看隔离级别

select @@tx_isolation;

• 设置事务隔离级别

set tx_isolation='READ-UNCOMMITTED';
set tx_isolation='READ-COMMITTED';
set tx_isolation='REPEATABLE-READ';
set tx_isolation='SERIALIZABLE';

4.锁机制和实战

4.1概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MySQL中的锁，按照锁的粒度分，分为以下三类：

1. 全局锁：锁定数据库中的所有表
2. 表级锁：每次操作锁住整张表
3. 行级锁：每次操作锁住对应的行数据

4.2全局锁

4.2.1介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

4.2.2语法

1. 加全局锁

flush tables with read lock;

1. 释放锁

unlock tables;

4.2.3特点

数据库中加全局锁，是一个比较重的操作，存在以下问题：

1. 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
2. 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟
    

4.3表级锁

4.3.1介绍

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁，主要分为以下三类:

1. 表锁
2. 元数据锁(meta data lock，MDL)
3. 意向锁

4.3.2表锁

对于表锁，分为两类:

1. 表共享读锁（read lock）
2. 表独占写锁（write lock）

语法:

1. 加锁：lock tables 表名... read/write
2. 释放锁：unlock tables / 客户端断开连接
    

读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

4.3.3元数据锁

meta data lock , 元数据锁，简写MDL。MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与
DDL冲突，保证读写的正确性。这里的元数据，大家可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。
常见的SQL操作时，所添加的元数据锁:

对应SQL	锁类型	说明
lock tables xxx read / write	SHARED_READ_ONLY / SHARED_NO_READ_WRITE
select 、select ... lock in share mode	SHARED_READ	与SHARED_READ、 SHARED_WRITE兼容，与 EXCLUSIVE互斥
insert 、update、delete、 select ... for update	SHARED_WRITE	与SHARED_READ、 SHARED_WRITE兼容，与 EXCLUSIVE互斥
alter table ...	EXCLUSIVE	与其他的MDL都互斥

4.3.4意向锁

4.3.4.1介绍

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

假如没有意向锁，客户端一对表加了行锁后，客户端二如何给表加表锁呢，来通过示意图简单分析一下：首先客户端一，开启一个事务，然后执行DML操作，在执行DML语句时，会对涉及到的行加行锁。

当客户端二，想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就会从第一行数据，检查到最后一行数据，效率较低。

有了意向锁之后 :客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。

而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了。

4.3.4.2分类

1. 意向共享锁(IS): 由语句select ... lock in share mode添加 。 与 表锁共享锁(read)兼容，与表锁排他锁(write)互斥。
2. 意向排他锁(IX): 由insert、update、delete、select...for update添加 。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放

4.4行级锁

4.4.1介绍

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

1. 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持

1. 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支

1. 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

4.4.2行锁

4.4.2.1介绍

InnoDB实现了以下两种类型的行锁:

1. 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
2. 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

两种行锁的兼容情况如下:

常见的SQL语句，在执行时，所加的行锁如下:

SQL	行锁类型	说明
INSERT ...	排他锁	自动加锁
UPDATE ...	排他锁	自动加锁
DELETE ...	排他锁	自动加锁
SELECT（正常）	不加任何 锁
SELECT ... LOCK IN SHARE MODE	共享锁	需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT ... FOR UPDATE	排他锁	需要手动在SELECT之后加FOR UPDATE

4.2.2.2小结

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
2. InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时 就会升级为表锁。

 

4.4.3间隙锁&临键锁

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

1. 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。
2. 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
3. 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。

4.5悲观锁和乐观锁

4.5.1悲观锁

   悲观锁（Pessimistic Locking），是指在数据处理过程，将数据处于锁定状态，一般使用数据库的锁机制实现。从广义上来讲，前面提到的行锁、表锁、读锁、写锁、共享锁、排他锁等，这些都属于悲观锁范畴。

1. 表级锁

lock table 表名称 read|write,表名称2 read|write; -- 手动增加表锁
show open tables;    -- show open tables;
show open tables; -- show open tables;

   表级读锁：当前表追加read锁，当前连接和其他的连接都可以读操作；但是当前连接增删改操作会报    错，其他连接增删改会被阻塞。

   表级写锁：当前表追加write锁，当前连接可以对表做增删改查操作，其他连接对该表所有操作都被    阻塞（包括查询）。

   总结：表级读锁会阻塞写操作，但是不会阻塞读操作。而写锁则会把读和写操作都阻塞。

1. 共享锁（行级锁-读锁）
   共享锁又称为读锁，简称S锁。共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数
   据，但是只能读不能修改。使用共享锁的方法是在select ... lock in share mode，只适用查询语句。
   总结：事务使用了共享锁（读锁），只能读取，不能修改，修改操作被阻塞。
2. 排他锁（行级锁-写锁）
   排他锁又称为写锁，简称X锁。排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排
   他锁，其他事务就不能对该行记录做其他操作，也不能获取该行的锁。
   使用排他锁的方法是在SQL末尾加上for update，innodb引擎默认会在update，delete语句加上
   for update。行级锁的实现其实是依靠其对应的索引，所以如果操作没用到索引的查询，那么会锁
   住全表记录。
   总结：事务使用了排他锁（写锁），当前事务可以读取和修改，其他事务不能修改，也不能获取记录锁（select... for update）。如果查询没有使用到索引，将会锁住整个表记录。

4.5.2乐观锁

   乐观锁是相对于悲观锁而言的，它不是数据库提供的功能，需要开发者自己去实现。在数据库操作时，想法很乐观，认为这次的操作不会导致冲突，因此在数据库操作时并不做任何的特殊处理，即不加锁，而是在进行事务提交时再去判断是否有冲突了。

乐观锁实现原理 :

1. 使用版本字段（version） :先给数据表增加一个版本(version) 字段，每操作一次，将那条记录的版本号加 1。version是用来查看被读的记录有无变化，作用是防止记录在业务处理期间被其他事务修改。

   

2. 使用时间戳（Timestamp）

乐观锁案例:

update products set quantity=quantity-1,version=version+1 where id=1 and version=#{version};

5.死锁与解决方案

5.1表锁死锁

1. 产生原因
      用户A访问表A（锁住了表A），然后又访问表B；另一个用户B访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要
   等用户A释放表A才能继续，这就死锁就产生了。
2. 解决方案
      这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

5.2行级锁死锁

1. 产生原因1：
      如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁。
2. 解决方案1 :
      SQL语句中不要使用太复杂的关联多表的查询；使用explain“执行计划"对SQL语句进行分析，对于
   有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化。
3. 产生原因2：
      两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁
      

   

4. 解决方案2 :

1. 1. 在同一个事务中，尽可能做到一次锁定所需要的所有资源
   2. 按照id对资源排序，然后按顺序进行处理

5.3死锁排查

MySQL提供了几个与锁有关的参数和命令，可以辅助我们优化锁操作，减少死锁发生。

1. 查看死锁日志

show engine innodb status;

使用方法：

1. 查看近期死锁日志信息；
2. 使用explain查看下SQL执行计划

• 查看锁状态变量

show status like'innodb_row_lock%'

参数	解释
Innodb_row_lock_current_waits	当前正在等待锁的数量
Innodb_row_lock_time	从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg	每次等待锁的平均时间
Innodb_row_lock_time_max	从系统启动到现在等待最长的一次锁的时间
Innodb_row_lock_waits	系统启动后到现在总共等待的次数

   如果等待次数高，而且每次等待时间长，需要分析系统中为什么会有如此多的等待，然后着
手定制优化