MySQL事务与锁

本文主要参考于B站视频，记录了事务和锁机制，解释了并发事务带来的问题（脏读、幻读、不可重复读），以及面对这些问题，MySQL InnoDB又是如何处理的。

一、事务的定义

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

二、事务四大特性（ACID）

• 原子性（Atomicity）
• 一致性（Consistency）
• 隔离性（Isolation）
• 持久性（Durability）

三、并发事务问题

1、脏读（Dirty Read）

简单讲，就是事务A查询到了事务B修改后未提交的数据，若事务B回滚了，则事务A之前读到的数据就变成了脏数据。

2、幻读（Phontom）

简单讲，就是事务A对表中某个条件下数据进行修改，读取到10条数据，在修改前，事务B也对这个表中数据进行了修改，如插入或删除了某条数据，导致事务A再次用相同条件查询时，得到的数据不是10条，像是发生了幻觉一样。

3、不可重复读（Non-repeatable Read）

简单讲，就是事务A查询到了事务B修改提交后的数据，导致与事务A前后查询结果不一致的情况。

并发事务的三大问题其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

四、事务的四种隔离标准（SQL92 ANSI/ISO标准）

1、未提交读（Read Uncommitted）

最低级的事务级别，可以读取到其他事务未提交的数据。未解决任何并发事务问题。

2、已提交读（Read Committed）

只能读取已提交事务数据，不能读取未提交事务数据，解决了脏读

3、可重复度（Repeatable Read）

解决脏读、不可重复读，是mysql默认的事务隔离级别。

4、串行化（Serializable）

所有事务串行化执行，解决事务并发的所有问题（直接不存在并发了）

五、mysql IndoDB对事务隔离级别的支持程度

mysql InnoDB对事务的隔离级别默认是可重复读。

mysql> show global variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.04 sec)

mysql InnoDB完全解决了幻读，但它是如何解决的？请接着往后看。

mysql手动开启事务

START TRANSACTION;		-- 开启事务

INSERT INTO `t1` (t, t1) VALUES('124', NOW()); -- do something

ROLLBACK;			   -- 事务回滚

COMMIT;				   -- 事务提交

六、事务隔离级别的解决方案

1、LBCC—基于锁的并发控制

在读取数据前，对其加锁，阻止其他事务对数据进行修改：Lock Based Concurrency Control

会降低并发量

2、MVCC—多版本并发控制

生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别（语句级或事务级）的一致性读取：Multi Version Concurrency Control

MVCC部分请参考博主SnailMann的这篇博客

七、锁分类

锁的作用：解决资源的竞争问题。

1、行锁—共享锁（Shared Locks）

S锁，又称为读锁，即多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能改（其他事务无法对加了共享锁的数据进行修改）。

加锁方式：select * from user where id=1 LOCK IN SHARE MODE

释放锁：commit、rollback

2、行锁—排他锁（Exclusive Locks）

X锁，又称为写锁，排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁（共享锁、排他锁），只有该获取了排他锁的事务才可以对数据进行读取和修改。

自动加锁：delete、update、insert，默认会自动加锁

手动加锁：select * from user where id=1 FOR UPDATE

3、表锁—意向共享锁（IS）与意向排他锁（IX）

加表锁成功的前提：没有任何事务已经锁定了这张表的任意一行数据，加锁时会进行全表扫描检测。

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁。

意向共享锁（Intention Shared Locks，简称IS锁），表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁（Intention Exclusive Locks，简称IX锁），表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

思考：为什么需要（表级别的）意向锁？

答案：提高加表锁的效率，因为在加表锁之前，如果没有意向锁，则需要扫描全表的每一行数据判断能不能加表锁成功，而有了意向锁，则只需要看这个表上有没有意向锁，即可知道能不能加锁成功。

八、锁的本质

思考：加锁后锁住的是什么？是整张表吗？还是一行数据吗？又或是对应字段？

答案：如果是行锁，则锁住的是记录对应的索引项，InnoDB行锁就是通过给索引上的索引项加锁来实现的，而如果未走索引或索引失效，则会从行锁升级为表锁。

案例一：

start transaction;

select * from user where id=1 for update;

-- rollback;

如上面的sql语句，如果id字段有索引，则查询会走索引，上面的语句锁住的是id=1的那条数据的索引项；如果id字段没有索引，则上面的语句会锁住整个表。

案例二：

alter user add index idx_nae(name,age,email); -- 加索引

start transaction;

select * from user where age=16 for update;

-- rollback;

如上面的语句，age在组合索引中，但上面的语句不会走索引（最左匹配原则），所以上面的语句最终会锁的是整张表。

案例三：

alter user add index idx_nae(name,age,email); -- 加索引

start transaction;

select * from user where name='Jack' for update;

-- rollback;

假设user表中有id=1，name='Jack’的记录，则上面的语句执行后，最终锁住的是主键索引中id=1的索引项，因为这里的辅助索引在查询时会涉及到回表。

九、死锁

本节参考自Eternal_yys的博客，以下面的图来举例，当处理完事务B的最后一行update，回车会出现下面的报错：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

1、如何处理死锁

（1）直接进入等待，直到超时，InnoDB默认超时时间为50秒

-- 查询超时时间
show variables like 'innodb_lock_wait_timeout';

-- 设置超时时间
set innodb_lock_wait_timeout = 50;

（2）发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以执行。InnoDB默认开启死锁检测。

-- 查询死锁检测开关状态
show variables like 'innodb_deadlock_detect';

-- 开启
set global innodb_deadlock_detect = on;

死锁检测的条件：当前事务需要加锁访问的行被其他事务锁住时，才会进行死锁检测。

十、InnoDB是如何解决幻读的

间隙锁（Gap Lock）

锁定的是一个范围内（区间）的索引项，在这个被锁定的范围内，无法新增数据，也无法对已有数据进行更新或删除操作。

假设有个user表，表字段id为自增主键，现有10条数据，id从1到10。

事务A

start transaction;select * from user where id > 5 for update; -- 会将id范围为6到无穷大的锁住-- rollback;

事务B

start transaction;select * from user where id = 6 for update; -- 会被阻塞，如果不回滚或不关闭会话，则会阻塞直到超时（默认50秒）

select * from user where id = 11 for update; -- 不会阻塞，因为没有id=11的数据

update user set name = 'Jack' where id = 6; -- 会被阻塞

delete from user where id = 6; -- 会被阻塞

insert user(id,name) values(11,'Mary'); -- 会被阻塞

select * from user where id = 6; -- 不会阻塞，可以正常查询-- rollback;

如上，被事务A锁住的数据（6~10），另一个事务无法获取对应的锁，无法对锁住的数据进行更新、删除操作，也无法将数据插入到这个范围内。

需要注意的是，如果将事务A中锁的条件范围修改为 id > 20，最终生效的还是 id > 10

事务A

start transaction;select * from user where id > 20 for update; -- 由于当前表中id最大为10，所以这个锁锁住的数据范围是11到正无穷

-- rollback;

事务B

start transaction;

select * from user where id = 10 for update; -- 不会阻塞

select * from user where id = 11 for update; -- 不会阻塞，因为没有id=11的数据

insert user(id,name) values(11,'Mary'); -- 会被阻塞，因为事务A的锁从11开始

-- rollback;

临键锁（Next-key Lock）

锁定的也是一个范围，但它的范围与间隙锁略有不同，它锁定的是最后一个索引记录的下一个左开右闭区间。

还是使用上面的user表为例，在上面id从1到10的基础上，新增一条id为20的记录，即总的有11条数据，分别是id从1到10的10条，外加一条id为20的数据。

事务A

start transaction;

select * from user where id > 13 and id < 18 for update; -- 会锁住范围 (10,20]，不包含10，但包含20

-- rollback;

事务B

start transaction;select * from user where id = 10 for update; -- 不会阻塞

select * from user where id = 11 for update; -- 不会阻塞，因为没有id=11的数据

insert user(id,name) values(11,'Mary'); -- 会被阻塞，锁的范围 (10,20]

select * from user where id = 20 for update; -- 会被阻塞，左开右闭包含20 

-- rollback;

参考

细谈数据库表锁和行锁

bilibili：MySQL事务和锁机制详解

【MySQL笔记】正确的理解MySQL的MVCC及实现原理