MySQL（三）事务与锁

1、事务

事务是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

1.1、特性

1. 原子性：必须要全部成功，或者全部失败，使用undo log实现
2. 隔离性：并发操作隔离
3. 持久性：对于数据库的操作只要提交之后就一定是永远存在的，使用redo log和double buffer write实现
4. 一致性：数据库的完整性约束没有被破坏

1.2、数据库事务

1.2.1、自动提交

数据库在执行一条增加，删除，修改等语句时，会自动开启一个事物，而且自动提交完成，这就是自动提交。这是一条SQL一个事务，每个事务都是有编号的，这个编号是一个整数，有递增的特性。

1.2.2、手动提交

当多条SQL放在一个事务里，就要手动开启事务，手动开启事务有两种方式：一种用begin，一种用start transaction。

1.2.3、结束事务

事务的结束既可以是提交（commit），也可以是回滚（rollback）。
在InnoDB中，有一个参数autocommit（分为session级别和global级别），默认为on（开启），作用是是否开启自动提交。

1.3、并发事务

1. 脏读：在一个事务中多次读取，因为其他事务修改数据但还未提交，从而导致读取到了未提交数据的情况，叫做脏读。
2. 不可重复读：在一个事务中多次读取，因为其他事务对这条数据修改并提交导致前后两次数据不一致的情况，叫做不可重复读。
3. 幻读：在一个事务中多次读取，因为其他事务插入一条数据导致前后两次范围查询数据数量不一致的情况，叫做幻读。
4. 不可重复读是修改或删除导致的，幻读是插入导致的。

无论是脏读，幻读还是不可重复读，都是数据库并发事务导致的读一致性问题，即在同一个事务内出现了前后两次读取不一致的情况。

1.4、隔离级别

在SQL92标准中，规定了如下隔离级别：

1. 未提交读（Read Uncommited）：一个事务可以读取到其他事务未提交的数据，会出现脏读。
2. 已提交读（Read Commit）：一个事务只能读取到其他事务已提交的数据，不能读取到未提交的数据，解决脏读问题。
3. 可重复读（Repeatable Read）：一个事务多次读取同样数据的结果是一样的，解决不可重复读
4. 串行化（Serializable）：在这个级别里，所有的事务都是串行操作的，所以不存在任何并发问题。

Oracle支持已提交读和串行化。
MySQL的InnoDB支持未提交读，已提交读，而且在已提交读级别就可以达到可重复读的级别，

1.5、隔离实现方案

1.5.1、LBCC

既然要保持前后两次读取数据一致，那么读取数据的时候，锁定要读取的数据，禁止其他事务对其进行修改就可以达成效果。这种方案为基于锁的并发控制（Lock Based Concurrency Control）。
如果是这种方案，会导致不支持并发读写，这对于以读为主的数据库很不友好。

1.5.2、MVCC

如果要让一个事务前后两次读取的数据一致，那么可以在修改数据之前创建一个备份或者快照，之后都读取这个备份数据就可以了，这种方案被称为多版本并发控制（Multi Version Concurrency Control）

1.5.2.1、MVCC的原则

一个事务能看到的数据版本：

1. 第一次查询之前已经提交的事务的修改
2. 本事务的修改

一个事务不能看见的数据版本

1. 在本事务第一次查询之后创建的事务，即事务ID比本事务ID大的事务。
2. 活跃的事务修改，即未提交的事务修改。

1.5.2.2、MVCC的效果

1. 可以查到这个事物开始之前已经存在的数据，就算它后面被修改或者删除。
2. 这个事务之后新增的数据无法被查询到。

1.5.2.3、MVCC的原理

InnoDB的事务具有编号，且会不断递增。
InnoDB为每行记录都提供了两个隐藏字段：

1. DB_TRX_ID，6字节：事务ID，数据是在哪个事务插入或者修改为新数据，记录为当前事务ID。
2. DB_ROLL_PTR，7字节：回滚指针，数据被删除或记录为旧数据时，记录当前事务ID，没有修改或者删除的时候为空。

第一个事务，初始化数据，此时的数据创建版本是当前事务ID，删除版本为空。（添加id1，id2）

id	data	创建版本	删除版本
1	data1	1	undefined
2	data2	1	undefined

第二个事务，执行一次查询，读取到原始数据，此时事务ID增加1。

事务ID	语句类型	结果
2	查询	data1，data2

第三个事务，插入数据，增加一条数据之后，事务ID再增加1。（添加id3）

id	data	创建版本	删除版本
1	data1	1	undefined
2	data2	1	undefined
3	data3	3	undefined

第二个事务，再执行一次查询，只能查找创建时间小于等于当前事务ID的数据，或者删除时间大于当前事务ID的行，因此只查询事务ID比2小的数据，也就是只有初始化的数据。

事务ID	语句类型	结果
2	查询	data1，data2

第四个事务，删除数据，减少一条数据之后，事务ID再增加1.（删除id1）

id	data	创建版本	删除版本
1	data1	1	4
2	data2	1	undefined
3	data3	3	undefined

第二个事务，再执行一次查询，只能查找创建时间小于等于当前事务ID的数据，或者删除时间大于当前事务ID的行，因此查询事务ID小于2，或者大于4的数据，还是只有初始化的数据。

事务ID	语句类型	结果
2	查询	data1，data2

第五个事务，更新数据，更新一条数据之后，事务ID再增加1。（更新id2）

id	data	创建版本	删除版本
1	data1	1	4
2	data2	1	5
3	data3	3	undefined
2	data4	5	undefined

第二个事务，再执行一次查询，只能查找创建时间小于等于当前事务ID的数据，或者删除时间大于当前事务ID的行，因此更新后的数据版本大于当前事务ID，删除后的数据大于当前事务ID，还是只有初始化的数据。

事务ID	语句类型	结果
2	查询	data1，data2

在InnoDB的数据中，旧版本数据会存储在undo log之中，因为修改多次，会形成一个undo log链条，名为undo log链，DB_ROLL_PTR就是指向undo log链的指针。为了能够判断哪个版本的数据是它应该读取的，有一个对应的数据结构：Read View（可见性视图）。

1.5.2.4、Read View（可见性视图）

Read View保存本事务ID，活跃事务ID，当前系统最大事务ID，每一个事务都会维护一个自己的Read View，以下为生成read view时的需要参数。

m_ids{}	min_trx_id	max_trx_id	creator_trx_id
列表，当前系统活跃的事务ID	m_ids的最小值	系统分配给下一个事务的ID	生成read view事务的事务ID

因此，事务数据版本可见性的判断规则应该是这样的：

1. 从数据的最早期版本开始判断（undo log）
2. 数据版本的trx_id=creator_trx_id，本事务修改，可以访问
3. 数据版本的trx_id<min_trx_id（未提交事务的最小ID），说明该版本在生成Read View已经提交，可以访问。
4. 数据版本的trx_id>max_trx_id（下一个事务ID），这个版本是生成Read View之后才开启的事务建立的，不能访问。
5. 数据版本的trx_id>min_trx_id并且trx_id<max_trx_id，即trx_id在它们之间，则查看trx_id是否在m_ids的列表之内，若是存在，则不可以，反之则可以，因为活跃的事务尚未提交，要避免脏读。
6. 如果当前版本不可见，就去undo log链表中寻找它的下一个版本。

Repeatable Read（可重复读）的Read View是事务第一次查询的时候建立的。
Read Commint（已提交读）的Read View是事务每次查询的时候建立的。

2、锁

2.1、锁的粒度

MyISAM支持表锁

lock table xxx read;
lock table xxx write;
unlock table;

InnoDB支持表锁和行锁

• 锁定粒度：表锁 > 行锁
• 加锁效率：表锁 > 行锁
• 冲突概率：表锁 > 行锁
• 并发性能：表锁 < 行锁

2.2、锁的类型

MySQL的官网有八种锁。
前两个行级别的锁（Shared and Exclusive Lock），和两个表级别的锁（Intention Locks），它们是锁的基本模式。
后面三种（Record Locks，Gap Locks，Next-Key Locks），这些被称为锁的算法，也就是分别在什么情况下锁定什么范围。
插入意向锁：是一个特殊的间隙锁，间隙锁不允许插入数据，但是插入意向锁允许多个事务同时插入数据到同一个范围。
自增锁：是一种特殊的表锁，用来防止自增字段重复，数据插入以后就会释放，不需要等到事务提交才会释放。如果需要选择更快地自增值生成速度或者更加连续的自增值，就要通过修改自增锁的模式来作出改变。

• 0：traditonal（每次都会产生表锁）
• 1：consecutive（会产生一个轻量锁，simple insert 会获得批量的锁，保证连续插入，默认值）
• 2：interleaved（不会锁表，会处理所有请求，并发最高）

Predicate Locks for Spatial Indexes 是5.7版本里面新增的一种数据类型的索引的锁。

2.3、共享锁（S锁）

第一个行级别的锁就是Share Locks（共享锁），获取到一行数据的共享锁以后，可以用来读取数据，所以也叫作读锁，注意不要加上读锁之后再写入数据，这样容易产生死锁。多个事务可以共享一把锁。
共享锁会阻塞其他事务的修改，所以可以用在不允许其他事务修改数据的情况。
手动加锁：select …… lock in share mode
释放锁有两种方式，只要事物结束，锁就会自动解除。

2.4、排他锁（x锁）

第二个行级别的锁就是Exclusive Locks（排他锁），它是用来操作数据的，所以又叫做写锁。只要一个事务获取一行数据的排他锁，其他的事务就不能再获取这一行数据的共享锁和排他锁。
排他锁的加锁方式有两种，第一种是自动加排他锁，在操作数据的时候（增加，删除，修改）都会默认加上一个排他锁。第二种是手动加锁，使用：FOR UPDATE
释放锁有两种方式，只要事务结束，锁就会自动解除。

2.5、意向锁

如果给一行数据加上共享锁，数据库就会自动给整张表加上意向共享锁。
如果给一行数据加上排他锁，数据库就会自动给整张表加上意向排他锁。
反之
如果一张表上面至少有一个意向共享锁，说明有其他事务给其中的某些数据行加上共享锁。
如果一张表上面至少有一个意向排他锁，说明有其他事务给其中的某些数据行加上排他锁。
这意味着，意向锁和意向锁，意向锁和行锁都不冲突，因为意向锁本身的作用就是表示表中有数据被其他事务锁定其中某些行，这样在对表添加表锁的时候，只需要判断意向锁是否存在，如果存在，就失败，如果不存在，就成功。
意向锁可以理解为一个标志，标志此表是否可以添加表锁。

3、行锁的原理

假设有三张表，一张没有索引的t1，一张有主键索引的t2，一张有唯一索引的t3.
此时有一个行锁锁住一条数据。

3.1、无索引表

对于t1无索引表进行加锁操作
第一个事务根据ID对第一条数据添加行锁之后，成功锁住数据。
第二个事务根据ID对第二条数据添加行锁之后，加锁失败，操作被阻塞。
第三个事务对表插入一条新的数据，插入失败，操作被阻塞。
因此可以知道，此时InnoDB的行锁锁住的可能是整张表。

3.2、主键索引表

对于t2主键索引表进行加锁操作（主键索引字段为ID）
第一个事务根据ID对第一条数据添加行锁之后，成功锁住数据。
第二个事务根据ID对第二条数据添加行锁之后，操作成功。
第三个事务根据ID对第一条数据添加行锁之后，加锁失败，操作被阻塞。
因此可以知道，此时InnoDB的行锁锁住的可能是锁住了ID这个字段。

3.3、唯一索引表

对于t3唯一索引表进行加锁操作（主键索引字段为ID，唯一索引字段为XID）
第一个事务根据XID对第一条数据添加行锁之后，成功锁住数据。
第二个事务根据XID对第一条数据添加行锁之后，加锁失败，操作被阻塞。
第三个事务根据ID对第一条数据添加行锁之后，加锁失败，操作被阻塞。
因此可以知道，此时InnoDB的行锁锁住的可能不是字段。

3.4、索引与行锁

经过如上尝试，可以得知其实MySQL的行锁是针对索引加锁

3.4.1、无索引情况

在没有索引的情况下，InnoDB会先对非空唯一索引添加主键索引，如果没有这样的数据，会添加6字节的隐藏字段rowid作为主键索引。
当没有索引的表进行查询时，因为没有用到索引，所以会全表扫描，然后将每一个隐藏的主键索引都锁住。

3.4.2、有索引情况

在有主键索引的情况下，会对主键索引添加行锁。
在有主键索引和二级索引的情况下，对二级索引添加行锁，二级索引会根据叶子节点内存储的主键键值和字段值查到对应的主键键值，然后根据主键键值去主键索引查找到数据，实际上它们针对的是同一行数据，因此对二级索引加锁，主键索引的那条数据同样会被行锁锁住。

4、锁的算法

在讨论锁的算法，Record Locks，Gap Locks，Next-Key Locks之前，要先了解到它们代表的范围

1. Record：数据库里存在的主键值被称为Record（记录），有几个主键就有几个Record
2. Gap：根据主键，这些存在的Record隔开的数据不存在的区间，被称为Gap（间隙），它是一个左开右开的区间，它的个数是主键数+1
3. Next-Key：Gap（间隙）连同它左边的记录（Record），被称为Next-Key（临键的区间），它是一个左开右闭的区间。

整形的主键索引，可以自然排序，字符集的主键索引，具有相应的排序规则。

4.1、记录锁（Record Locks）

当对唯一的索引（主键索引或唯一索引）使用等值查询精准匹配到一条数据，此时使用的锁，就是记录锁。
此时使用不同的key去加锁，不会冲突，它只锁住这个Record。

4.2、间隙锁（Gap Locks）

当对条件的查询记录不存在，没有命中任何一个Record，无论用的是等值查询还是范围查询，此时使用的锁，就是间隙锁。
间隙锁阻塞插入（insert），相同的间隙锁之间不冲突。

4.3、临键锁（Next-Key Locks）

当使用范围查询，不禁命中了Record，还包含Gap，此时使用的锁，就是临键锁，临键锁是默认的行锁算法，作用相当于记录锁加间隙锁。
唯一性索引，等值查询匹配到一条记录后，退化成记录锁。
没有匹配到任何记录后，退化为间隙锁。
之所以要锁住一个左开右闭的区间，是为了解决幻读。

4.4、隔离级别的实现

4.4.1、快照读

普通的select

4.4.1、当前读

加锁的select与update

4.4.3、Read Uncommit（未提交读）

在此级别下，不加锁。

4.4.4、Read Commit（提交读）

在此级别下，不加锁的select使用快照读，使用MVCC实现。
加锁的select使用记录锁，因为没有Gap Locks。
外键约束检查和重复键检查时会使用间隙锁封锁区间。

4.4.5、Repeatable Read（可重复读）

在此级别下，不加锁的select使用快照读，使用MVCC实现。
加锁的select以及update，delete等语句使用当前读，使用记录锁，间隙锁或临键锁。

4.4.6、Serializable（串行化）

在此情况下，所有的select都会被转化为select……in share mode，和其他的update，delete互斥。

4.4.7、RC和RR的区别

1. RR的间隙锁会导致锁定范围扩大
2. 条件列未使用到索引，RR锁表，RC锁行
3. RC的“半一致性”读可增加update操作的并发性。

RC中，一个update语句，如果读到一行已经加锁的记录，此时InnoDB返回记录最近提交的版本，由MySql上层判断此版本是否满足update的where条件，若满足（需要更新），则会重新发起一次读操作，此时会读取行的最新版本，并加锁。
如果能够正确的使用锁，只锁定需要的数据，RR级别就足够了。

6、死锁

6.1、锁的释放与阻塞

锁的释放有两种，事务的提交或回滚，客户端连接断开。
如果有大量的并发请求，大量的事务因为无法立即获取所需的锁而挂起。会占用大量计算机资源，甚至会拖垮数据库。

6.2、死锁的发生和检测

死锁的发生需要满足一定的条件，因此InnoDB一般都可以通过算法自动检测到死锁。

1. 同一时刻只能有一个事务持有这把锁
2. 其他的食物需要在这个事务释放锁之后才能获取锁，不可以强行剥夺。
3. 当多个事务形成等待环路的时候，即发生死锁。

6.3、查看死锁日志

SHOW STATUS 命令包含了一些行锁信息。

show status like 'innodb_row_lock_%'

• innodb_row_lock_current_waits：当前正在等待锁定的数量
• innodb_row_lock_time：从系统启动到现在锁定的总时间长度，单位ms
• innodb_row_lock_time_avg：每次等待所花平均时间
• innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间
• innodb_row_lock_waits：从系统启动到现在总共等待的次数。

除此之外，还有三张表分析事务与锁：

select * from information_schema.INNODB_TRX; --当前运行的所有事务，还有具体的语句
select * from information_schema.INNODB_LOCKS; --当前出现的锁
select * from information_schema.INNODB_LOCKS_WAITS; --所等待的对应关系

如果想要查看更加具体的锁的情况，可以开启标准监控与锁监控

set global innodb_status_output = on;
set global innodb_status_output_locks = on;

然后查看对应的加锁情况

show engine innodb status;

6.4、死锁的避免

1. 在程序中，操作多张表时，尽量以相同的顺序来访问（避免形成等待回路）
2. 批量操作单张表数据的时候，先对数据进行排序（避免形成等待回路）
3. 申请足够级别的锁，如果要操作数据，就申请排他锁。
4. 尽量使用索引访问数据，避免没有where条件的操作，避免锁表。
5. 如果可以，细分事务
6. 使用等值查询而不是范围查询查询数据，确保对记录的命中，避免间隙锁的影像。