Mysql事务与锁

事物的四大特性ACID： 

• Atomicity（原子性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable)，通过 MVCC 和 锁来实现；MVCC 时多版本并发控制，主要解决一致性非锁定读，通过记录和获取行版本，而不是使用锁来限制读操作，从而实现高效并发读性能。锁用来处理并发 DML 操作；数据库中提供粒度锁的 策略，针对表（聚集索引B+树）、页（聚集索引B+树叶子节点）、行（叶子节点当中某一段记录 行）三种粒度加锁。
• Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失，一致性由原子性、隔离性以及持久性共同来维护的。 

如何保证原子性

      Innodb(另一个引擎是不支持事务特性)的undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句。

只有在事务对表中的记录做改动时才会为这个事务分配一个唯一的事务id；InnoDB存储引擎在实际进行增、删、改一条记录时，都需要先把对应的undo日志记下来。一般每对一条记录做一次改动，就对应着一条undo日志，但在某些更新记录的操作中，也可能会对应着2条undo日志。一个事务中增删改产生的一系列 undo log，都有 undo no 编号的。在回滚的时候，就可以应用这个事务中的 undo log，根据 undo no 从大到小开始进行撤销操作。

如何保持持久性

当一个事务里包含多条insert语句时，先执行一条insert，更新数据到缓冲池，然后写日志缓冲(redo log buffer),然后再执行第二条insert，更新数据到缓冲池，然后再写日志缓冲，等所有的insert语句的日志缓冲都写完了，再把日志缓冲写入到redo log file，此时 redo log 处在 prepare阶段。然后写binlog ，最后提交事务（commit），redo log 处在commit状态。

redo log有两个阶段  prepare 和commit 。

Redo log 和bingo 有一个共同的数据字段，叫 XID,崩溃恢复的时候，会按顺序扫描 redo log。

• 假设在写入binlog前系统崩溃，那么数据库恢复后顺序扫描 redo log，碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务，而且binlog也没写入，所以事务就直接回滚了。
• 假设在写入binlog之后，事务提交前数据库崩溃，那么数据库恢复后顺序扫描 redo log，碰到既有 prepare、又有 commit 的 redo log，就直接提交，保证数据不丢失。

这个事务要插入数据的过程中，生成的日志都得先写入redo log buffer ，等到commit的时候，才真正把日志写到 redo log 文件（commit的时候才会刷磁盘）。（当然，这里不绝对，因为redo log buffer可能因为其他原因被迫刷新到redo log）。

而为了确保每次日志都能写入日志文件，在每次将重做日志缓冲 写入 重做日志文件 后，InnoDB存储引擎都需要调用一次fsync操作，确保写入了磁盘。

隔离级别

READ UNCOMMITTED(读未提交)；该级别下读不加锁，写加排他锁，写锁在事务提交或回滚后释放锁；

READ COMMITTED(读已提交（RC))；从该级别后支持 MVCC (多版本并发控制)，也就是提供一致性非锁定读；此时 读取操作读取历史快照数据；该隔离级别下读取历史版本的最新数据，所以读取的是已提交的数据；

REPEATABLE READ(可重复读（RR))；该级别下也支持 MVCC，此时读取操作读取事务开始时的版本数据；

SERIALIZABLE(可串行化)；该级别下给读加了共享锁；所以事务都是串行化的执行；此时隔离级别最严苛；

四种隔离级别中，从上到下隔离级别越来越严格，可串行化最严格，效率最低。其他三个级别在并发下都会存在一些异常（读未提交级别下，异常最多）。

脏读

在READ UNCOMMITTED隔离级别下，事务A能读取到事务B修改但是未提交的数据，这就是脏读。解决办法提升隔离级别 -> READ COMMITTED。

不可重复度

在READ COMMITTED级别下 ，事务（A) 可以读到另外一个事务（ B ）中提交的数据；通常发生在一个事务中两次读到的数据是不 一样的情况这就是 不可重复读 。不可重复读现象 可以接受的，因为读到已经提交的数据，一般不会带来很大的问题，所以很多厂商（如Oracle 、SQL Server）默认隔离级别就是READ COMMITTED， 解决办法提升隔离级别 ->  REPEATABLE READ。

幻读

在 REPEATABLE READ 级别下， 两次读取 同一个范围内的记录 得到的结果集不一样，这就是 幻读， 幻读在隔离级别 REPEATABLE READ 及以下存在；但是可以在 REPEATABLE READ 级别下通过 读加锁 （使用 next-key locking）解决；

MYSQL中给出的定义是 The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a [SELECT] is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

我看网上还有很多人说在RR级别下，mysql已经解决了幻读问题，但是在我服务器上的确出现这种现象

 select * from teacher where id <= 3 lock in  share mode;与 select * from teacher where id <= 3 ;查出来结果的确不一样。

 MVCC

多版本并发控制；用来实现一致性的非锁定读；非锁定读是指不需要等待访问的行上X锁的释放； 在 read committed 和 repeatable read下，innodb使用MVCC；然后对于快照数据的定义不同； 在 read committed 隔离级别下，对于快照数据总是读取被锁定行的最新一份快照数据；而在 repeatable read 隔离级别下，对于快照数据总是读取事务开始时的行数据版本；

当前读也称锁定读(locking read),通过对读取到的数据(索引记录)加锁来保证数据一致性，当前读会对所有扫描到的索引记录进行加锁，无论该记录是否满足WHERE条件都会被加锁。

快照读MySQL使用MVCC (Multiversion Concurrency Control)机制来保证被读取到数据的一致性，读取数据时不需要对数据进行加锁，且快照读不会被其他事物阻塞。

在读提交和可重复读两种事务隔离级别下，普通的SELECT操作使用“快照读”，不会对数据加锁，也不会被事务阻塞。

在读提交和可重复读两种事务隔离级别下，使用“当前读”的操作包括：

1、SELECT LOCK IN SHARE MODE

2、SELECT FOR UPDATE

3、DELETE\UPDATE\INSERT INTO\REPLACE INTO

在可重复读事务隔离级别下，“当前读”读取到的时数据库最新的数据，而“快照读”读取到的数据是事务中第一次建立ReadView时的数据。

在RC隔离级别下，是每个SELECT都会获取最新的read view；而在RR隔离级别下，则是当事务中的第一个SELECT请求才创建read view。

redo

redo 日志用来实现事务的持久性；内存中包含 redo log buffer ，磁盘中包含 redo log file ；

当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的 commit 操

作完成才完成了事务的提交；

redo log 顺序写，记录的是对每个页的修改（页、页偏移量、以及修改的内容）；在数据库运行

时不需要对 redo log 的文件进行读取操作；只有发生宕机的时候，才会拿 redo log 进行恢复；

undo

undo 日志用来帮助事务回滚以及 MVCC 的功能；存储在共享表空间中； undo 是逻辑日志，回滚

时将数据库逻辑地恢复到原来的样子，根据 undo log 的记录，做之前的逆运算；比如事务中有

insert 操作，那么执行 delete 操作；对于 update 操作执行相反的 update 操作；

同时 undo 日志记录行的版本信息，用于处理 MVCC 功能；

mvcc的原理

聊聊 MySQL的 MVCC - 知乎

锁

共享锁和排他锁都是行级锁；MySQL当中事务针对表（B+树）、页（B+树叶子 节点）、行（B+树叶子节点当中某一段记录行）三种粒度加锁；

行级锁
共享锁：S锁，允许事务读一行数据
排他锁：X锁，允许事务删除或更新一行数据

在 SERIALIZABLE 隔离级别下，默认帮读操作加共享锁；

在 REPEATABLE READ 隔离级别下，需手动加共享锁，可解决幻读问题；

在 READ COMMITTED 隔离级别下，没必要加共享锁，采用的是 MVCC ；

在 READ UNCOMMITTED 隔离级别下，既没有加锁也没有使用 MVCC ；

X锁与任何的锁都不兼容，而S锁仅和S锁兼容。注意：行锁实际上是索引记录锁，对索引记录的锁定。即使表没有建立索引，InnoDB也会创建一个隐藏的聚簇索引，并使用此索引进行记录锁定。

意向锁
意向锁定是表级锁定，标识事务稍后对表中的行做哪种类型的锁定(共享或独占)

意向共享锁(IS)：事务想要获得一张表中某几行的共享锁
意向排他锁(IX)：事务想要获得一张表中某几行的排他锁

意向锁遵循如下协议：
在事务获取表中某行的共享锁之前，它必须首先在表上获取IS锁或更强的锁。
在事务获取表中某行的独占锁之前，它必须首先在表上获取IX锁。

 由于innodb支持的是行级别的锁，意向锁并不会阻塞除了全表扫描以外的任何请求； 意向锁之间是互相兼容的； IS 只对排他锁不兼容；当想为某一行添加 S 锁，先自动为所在的页和表添加意向锁 IS，再为该行添加 S 锁； 当想为某一行添加 X 锁，先自动为所在的页和表添加意向锁 IX，再为该行添加 X 锁； 当事务试图读或写某一条记录时，会先在表上加上意向锁，然后才在要操作的记录上加上读锁或写 锁。这样判断表中是否有记录加锁就很简单了，只要看下表上是否有意向锁就行了。意向锁之间是 不会产生冲突的，也不和 AUTO_INC 表锁冲突，它只会阻塞表级读锁或表级写锁，另外，意向锁 也不会和行锁冲突，行锁只会和行锁冲突。

锁的算法

Record Lock 记录锁，在单个行记录上的锁

Gap Lock   间隙锁，锁定一个区间，但是不包含记录本身，全开区间，可重复读隔离级别以上支持间隙锁。

Next-key Lock  记录锁+间隙锁，锁定一个范围，并且所著记录本身；左开右闭区间；Insert Intention Lock 插入意向锁，insert操作的时候产生，假如有一个记录索引包含键值 4和7，在两个不同事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁获取在插入行上的排他锁，但是不会被相互锁住，因为数据行并不冲突。

锁的对象

行级锁是针对表的索引加锁；索引包括聚集索引和辅助索引；

表级锁是针对页或表进行加锁；

重点考虑 InnoDB 在 read committed 和 repeatable read 级别下锁的情况；

如下图 students 表作为实例，其中 id 为主键， no （学号）为二级唯一索引， name （姓名）和

age （年龄）为二级非唯一索引， score （学分）无索引。

       聚集索引，查询命中： UPDATE students SET score = 100 WHERE id = 15 ;

辅助非唯一索引，查询未命中： UPDATE students SET score = 100 WHERE name = 'John' ;

死锁

死锁：两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象；

异常报错： deadlock found when trying to get lock;

相反加锁顺序死锁

不同表的加锁顺序相反或者相同表不同行加锁顺序相反造成死锁；其中相同表不同行加锁顺序相反

造成死锁有很多变种，其中容易忽略的是给辅助索引行加锁的时候，同时会给聚集索引行加锁；同

时还可能出现在外键索引时，给父表加锁，同时隐含给子表加锁；触发器同样如此，这些都需要视

情况分析；

锁冲突死锁

innodb 在 RR 隔离级别下，最常见的是插入意向锁与 gap 锁冲突造成死锁；主要原理为：一个事

务想要获取插入意向锁，如果有其他事务已经加了 gap lock 或 Next-key lock 则会阻塞；

查看死锁

系统表

-- 开启标准监控

CREATE TABLE innodb_monitor ( a INT ) ENGINE = INNODB ;

-- 关闭标准监控

DROP TABLE innodb_monitor;

-- 开启锁监控

CREATE TABLE innodb_lock_monitor ( a INT ) ENGINE = INNODB ;

-- 关闭锁监控

DROP TABLE innodb_lock_monitor

系统参数

-- 开启标准监控

set GLOBAL innodb_status_output = ON ;

-- 关闭标准监控

set GLOBAL innodb_status_output = OFF;

-- 开启锁监控

set GLOBAL innodb_status_output_locks = ON ;

-- 关闭锁监控

set GLOBAL innodb_status_output_locks = OFF;

-- 将死锁信息记录在错误日志中

set GLOBAL innodb_print_all_deadlocks = ON ;

-- 查看事务

select * from information_schema .INNODB_TRX ;

-- 查看锁

select * from information_schema .INNODB_LOCKS ;

-- 查看锁等待

select * from information_schema .INNODB_LOCK_WAITS ;

死锁解决

对于顺序相反型，调整执行顺序；

对于锁冲突型，更换语句或者降低隔离级别；

如何避免死锁

尽可能以相同顺序来访问索引记录和表；

如果能确定幻读和不可重复读对应用影响不大，考虑将隔离级别降低为 RC ；

添加合理的索引，不走索引将会为每一行记录加锁，死锁概率非常大；

尽量在一个事务中锁定所需要的所有资源，减小死锁概率；

避免大事务，将大事务分拆成多个小事务；大事务占用资源多，耗时长，冲突概率变高；

避免同一时间点运行多个对同一表进行读写的概率；