【mysql】锁和事务

数据库使用锁是为了提供数据一致性和完整性，本文只讨论lock（对象是事务）,不讨论latch（对象是线程，保护内存数据结构，无死锁）。

事务可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

事务四个特性：原子性、一致性、隔离性、持久性（ACID）

原子性：一个事务是一个不可分割的工作单位，事务中包括的一批操作要么都做，要么都不做。

一致性：事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

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

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

事务隔离级别：

未提交读：A事务可以读取到B事务未提交的数据（脏数据），产生脏读。(但可以应用于主从架构中slave节点，不需要特别精确返回值的情况)。

*脏读：不管事务B后来是否回滚，只要A读取到B未提交的数据，即为脏读，它违反了隔离性原则。

已提交读：A事务可以读取到B事务已提交修改的数据，但会产生不可重复读。

*不可重复读：事务A第一次查询得到一行记录row1，事务B提交修改后，事务A第二次查询得到B修改后提交的row1_updated，但列内容发生了变化，即事务A在开始和结束读到的数据不一致，违反了事务一致性的要求。

可重复读：确保事务执行过程中不会读到其他事务对已有数据的修改，但会读到其他事务新插入的数据，即产生幻读(mysql默认隔离级别，但是mysql用innodb的Next-Key Locking间隙锁避免了产生幻读)。

*Next-Key Locking只针对范围查询，在查询具体行时不需要；在使用唯一索引查询时也不需要间隙锁（使用组合唯一索引中的某一列时还会用到）

*幻读：事务A第一次根据某条件查询到一行记录row1，事务B新增记录row2后，事务A第二次查询得到两行记录row1和row2。

串行化：强制所有事务都是串行执行，不会产生幻读（使用不当会产生大量阻塞）。

举例说明脏读、不可重复读、幻读：

如图表t存在1、2、3、5三条记录，有两个事务，1开启后，2开启并提交，之后1再提交。

脏读：事务1需要读取id>1的记录，在事务2开启但并未提交之前，事务1就读取到4，即为脏读。

不可重复读：事务1需要读取id=2的数据，在事务2提交前读到的是刘二，事务2把id=2的数据修改为赵二后，事务1再读，取到的数据是赵二。事务执行过程中没有保证一致性。

幻读：事务1需要修改id=2的数据为吴二，事务2新增了一条id=6,name='吴二'的数据并提交后，事务1读到的是两条name='吴二'的数据。

不可重复读重点在update和delete，幻读重点在insert。

要了解各种隔离级别实现原理，需要首先弄清楚各种“锁”。

锁：

 

innodb中有两种标准行级锁：

共享锁（S）：允许事务读取一行数据。

排他锁（X）：允许事务删改一行数据。

*例：事务T1获取了行的S锁，事务T2也可以获取本行的S锁，事务T3想获得本行的X锁，需要等待T1、T2释放。

意向锁：为了支持在不同粒度进行加锁操作，即：事务希望在更细粒度上加锁。

innodb支持的意向锁为表级锁，分意向共享锁（IS）、意向排他锁（IX），为了在一个事务中揭示下一行将被请求的锁类型。

意向锁只能加在表上，在执行增删改等需要加X锁前会自动在当前表添加IX锁，在需要

lock table table_name read/write 对表加读/写锁

unlock tables  释放锁 同时会隐式提交事务

innodb支持行级别的锁，因此意向锁不会阻塞除全表扫描以外的请求（无命中索引等情况会引发全表扫描）。

在innodb默认设置下（已提交读、可重复读），例1中事务T3加上X锁后，事务T4来读取本行时并不需要等待T3释放，而是采用一致性非锁定读，来读取行的一个快照，快照通过undo段完成，undo用来在事务中回滚数据，所以不会造成额外开销，而非锁定读极大提高了数据库并发性。

在已提交读级别，读取被锁定行最新的快照，在可重复读级别，读取事务最开始时的快照（所以才能重复读，但是不能读取到其他事务新增数据行的快照，因此会出现幻读）。

这种读取快照的方式，叫做快照读，它是不加锁的非阻塞读，而需要对所读数据加锁的为当前读，它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录。快照读是mysql实现MVCC（多版本并发控制：维持一个数据的多个版本，使得读写操作没有冲突）的方法。

在当前读时，需要用户设定一致性锁定读以保障数据的严格一致性，也分X、S两种锁：

select...for update：对读取的行记录加一个X锁。

数据库执行增删改时会自动加X锁，但是在某些时候，select * from table for update 可以主动对读操作加X锁

select...for share（mysql8.0.1之前select...lock in share mode，但它不支持nowait、 skip locked）：对读取的行记录加一个S锁。

用 SELECT … IN SHARE MODE 获得共享锁，主要用在需要数据依存关系时确认某行记录是否存在，并确保没有人对这个记录进行 UPDATE 或者 DELETE 操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用 SELECT … FOR UPDATE 方式获取排他锁。

*在上述表t中，事务1执行：select * from t where a>3 for update;在事务1提交之前，事务2插入了4这个值，但是事务1并未读到4，这是因为innodb并不是只锁定了5这个查询结果，而是用X锁锁定了（3，+∞）这个范围，所以在事务1执行完成前，事务2的插入不会成功。这就是Innodb避免产生幻读的方法--Next-Key Locking。

mysql8为一致性锁定读新增了nowait、skip locked，它们可以不等待行锁释放就立刻执行，select ...for share/update nowait如果获取不到锁就报错，select ...for share/update skip locked如果获取不到锁就返回跳过锁定行的结果。很显然，这种会返回不一致结果的做法是不可靠的，但它在某些时候可以用来避免争抢锁。

（innodb会为索引创建维护一套B+树，用来快速定位行数据，B+树是有序的，每个表的索引可分为几个区间）。

/*官方示例
session 1 建表并且执行查询加X锁:*/
CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
INSERT INTO t (i) VALUES(1),(2),(3);
START TRANSACTION;
SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+
/* Session 2 nowait发现所查询行上锁，返回报错信息:*/
START TRANSACTION;
SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.
/* Session 3 skip locked 返回跳过了上锁行2的信息:*/ 
START TRANSACTION;
SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+

行锁的三种算法：

Record Lock

单个行记录上的锁，只锁记录本身。

Gap Lock

间隙锁，锁定一个不包含记录本身的范围，遵循左开右闭原则（ ]，为了阻止多个事务将记录插入同一范围内，用户将事务隔离级别降为已读提交，Gap Lock会被关闭，此外还可通过参数设定关闭，但此设置会破坏事务的隔离性。

间隙锁可以共存。一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。共享和排他间隙锁之间没有区别。它们彼此不冲突，并且执行相同的功能。

事务隔离级别为可重复读才会使用间隙锁，在已提交读是禁止，在串行化时不需要。

Next-Key Lock

Record Lock + Gap Lock，锁定一个范围和记录本身。

*Next-Key Lock降级：为了提高应用并发性，在查询列为唯一索引时，Next-Key Lock降级为Record Lock，若唯一索引由多个列组成，仅查询其中一个，此查询为range而不是point类查询，依然采用Next-Key Lock。

例：

select * from test where id=5 for update

当id=5的记录不存在 / id是联合唯一索引中的一个 / id不是唯一索引时，此查询加的是间隙锁

当存在并且是唯一索引时，加的是记录行锁

已提交读会禁用间隙锁

间隙锁可以共存，即可以在同一个记录上加不同的间隙锁，只是执行时间有先后。

隔离级别为串行化时，用户读取的记录也加上X锁，这样就不存在并行，每个查询操作要等待其他查询/操作完成，释放X锁才能继续。

阻塞：例2中一个事务中的锁需要等待另一个事务的锁释放，这就是阻塞。

死锁：两个以上的事务在执行过程中，因争夺资源造成互相等待，若无外力，事务都无法推进下去，只存在于并发情况。比如事务A锁了t1表接下来想去更新t2表，事务B锁了t2表接下来想去更新t1表，因此应用程序逻辑无误是不能避免死锁的，它的产生概率与事务数量、每个事务中的操作数量成正比，与所操作数据集中度成反比。除非死锁频繁到根本无法运行某些事务，否则它们并不危险。

解决方法：

超时innodb_lock_wait_timeout：事务复杂时回滚占用时间也很多。

死锁检测：可以自动检测何时发生 死锁，并自动回滚所涉及的事务之一（ 受害方），innodb一般会自动回滚代价较小的事务。在高并发系统上，当多个线程等待相同的锁时，死锁检测会导致速度变慢。此时直接等待超时回滚可能会更有效 ，可用innodb_deadlock_detect 禁用死锁检测。

事务调度：InnoDB使用竞争感知事务调度（CATS）算法对等待锁的事务进行优先级排序。

记录原因：查看错误日志，启用innodb_print_all_deadlocks，根据记录的死锁原因，对应调整应用程序。

重试：如果由于死锁而失败，请始终准备重新发出事务。死锁并不危险。请再试一次。

降低占用：尽量减少事务所占用的锁的范围和执行时长，及时提交事务，适当降低隔离级别。（innodb默认会自动提交事务的，如果非业务需要建议不要轻易改此设定，免得忘了提交导致锁一直在）

在某些时候部分锁由行级锁升级到表级锁，可以避免其他锁争抢到此表内部分行资源造成死锁。（对于MySQL表级锁，必须使用超时方法来解决死锁）。

信号量表：序列化事务的另一种方法是创建一个仅包含一行的辅助“信号量”表。在访问其他表之前，让每个事务更新该行。这样，所有交易都以串行方式发生。请注意，InnoDB 在这种情况下，即时死锁检测算法也适用，因为序列化锁是行级锁。对于MySQL表级锁，必须使用超时方法来解决死锁。

innodb不会回滚大部分错误异常，但会回滚死锁。

innodb会对所引用的外键自动添加索引，以避免锁定整张表而出现死锁。

锁升级：将当前锁粒度变粗，例：1000个行锁变为一个页锁，100个页锁变为一个表锁。可以防止系统用太多资源来维护锁，提高一些效率。

myisam是一次性获取所有的锁，所以不会产生死锁，innodb是逐步加锁，所以会死锁。

表锁是由mysql server管理，在一定设定下innodb可感知，行锁是innodb管理。

参考：

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

https://haicoder.net/note/mysql-interview/mysql-interview-mysql-lock.html