mysql事务和锁详解_mysql锁与事务

引言

这里我们主要讲解事务的基本概念和事务的特性，并且分析各种隔离级别是如何解决脏读、不可重复读、幻读的。mysql中innodb是如何解决幻读的，还有innodb中锁的类型及作用

一.事务基本概念

事务： 数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；事务是一组不可再分割的操作集合（工作逻辑单元）；
典型事务场景(转账)：

update account set balance = balance - 1000 where userID = 3;
update account set balance = balance + 1000 where userID = 1;
1
2

mysql中的事务是什么情况：
手工开始事务:
begin / start transaction
事务提交或回滚:
commit / rollback
查看数据库目前的事务是否自动开启
show variables like 'autocommit';
设定事务是否自动开启
set session autocommit = on/off;

二.事务的四大特性详解

原子性 (Atomicity)
最小的工作单元，整个工作单元要么一起提交成功，要么全部失败回滚

一致性(Consistency)
事务中操作的数据改变是一致的

隔离性（Isolation）
事务内所操作的数据在提交之前，对其他事务的可见性设定

持久性(Durability)
事务所做的修改会永久保存，不会因为系统意外导致数据的丢失

四个特性，最重要的就是一致性。而一致性由原子性，隔离性，持久性来保证。

• 原子性由 Undo log 保证。Undo Log 会保存每次变更之前的记录，从而在发生错误时进行回滚。
• 隔离性由 MVCC 和 Lock 保证。这个后面说。
• 持久性由 Redo Log（重做日志） 保证。每次真正修改数据之前，都会将记录写到 Redo Log 中，只有 Redo Log 写入成功，才会真正的写入到 B+ 树中，如果提交之前断电，就可以通过 Redo Log 恢复记录。
  关于redo log和undo log的详细内容可以参考：
  详解redolog、undolog日志

三、深入事务隔离级别及问题

事务并发问题：

脏读：

事务B将tom的年龄修改成了25但是没有提交，然后事务A读取到了tom的25，接着事务B进行了回滚，tom的年龄还是20，这样，事务A读取到的数据就是脏数据，这就是脏读

不可重复读

事务A读取到了tom的年龄是20，然后事务B将tom的年龄改为了25，然后事务又读取了一次数据，两次的数据不一致，这就是不可重复读

幻读

进行年龄大于18岁的范围查找，事务A查到了一条数据，接着事务B又插入了一条数据，然后事务A查询到了两条数据，这就是造成了幻读

官方提供了4种事务隔离级别来解决：

SQL92 ANSI/ISO标准：http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txtRead

• Uncommitted 未提交读
  事务未提交对其他事务也是可见的，–会出现脏读、不可重复读、幻读

• Read Committed 提交读
  一个事务开始之后，只能看到自己提交的事务所做的修改  --解决了脏读问题，还会出现不可重复读、幻读

• Repeatable Read 可重复读

  在同一个事务中多次读取同样的数据结果是一样的   —解决了不可重复读问题，未解决幻读问题

• Serializable 串行化
  最高的隔离级别，通过强制事务的串行执行        —解决了所有问题，但是没有并发，不推荐 

mysql默认隔离级别是可重复读(Repeatable Read)
查询命令：
show variables like 'transaction_isolation'
Innodb引擎对标准的支持

按照之前的说法，可重复读是无法解决幻读的问题的，但是innodb却不会发生

思考：

在Innodb引擎中隔离级别到底如何实现？
为什么Innnodb引擎的可重复读级别会解决幻读的问题？
上面有提到，隔离性是由mvcc + lock来实现的，那mvcc究竟是什么呢？为什么既然有了mvcc还要用lock呢？

MVCC

这里参考博客：
MySQL的可重复读级别能解决幻读吗

基本概念

多版本并发控制(Multi-Version Concurrency Control, MVCC)是MySQL中基于乐观锁理论实现隔离级别的方式，用于实现读已提交和可重复读取隔离级别的实现。

在说到如何实现前先引入两个概念：
系统版本号：一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
事务版本号：事务开始时的系统版本号。
在MySQL中，会在表中每一条数据后面添加两个字段：
创建版本号：创建一行数据时，将当前系统版本号作为创建版本号赋值
删除版本号：删除一行数据时，将当前系统版本号作为删除版本号赋值

具体实现：

• SELECT

select时读取数据的规则为：
创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。
创建版本号<=当前事务版本号保证取出的数据不会有后启动的事物中创建的数据。
删除版本号为空或>当前事务版本号保证了至少在该事物开启之前数据没有被删除，是应该被查出来的数据。

• INSERT

insert时将当前的系统版本号赋值给创建版本号字段。

• UPDATE

插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行，实际上这里的更新是通过delete和insert实现的。

• DELETE

删除时将当前的系统版本号赋值给删除版本号字段，标识该行数据在那一个事物中会被删除，即使实际上在位commit时该数据没有被删除。根据select的规则后开启懂数据也不会查询到该数据。

MVCC真的解决了幻读吗？
从最开始我们的测试示例和上面的理论支持来看貌似在MySQL中通过MVCC就解决了幻读的问题，那既然这样串行化读貌似就没啥意义了，带着疑问继续测试。
测试前数据：

根据上面的结果我们期望的结果是这样的：

但是实际上我们的结果是：

本来我们希望得到的结果只是第一条数据的部门改为财务，但是结果确实两条数据都被修改了。这种结果告诉我们其实在MySQL可重复读的隔离级别中并不是完全解决了幻读的问题，而是解决了读数据情况下的幻读问题。
而对于修改的操作依旧存在幻读问题，就是说MVCC对于幻读的解决时不彻底的。快照读和当前读出现了上面的情况我们需要知道为什么会出现这种情况。在查阅了一些资料后发现在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，不是数据库最新的数据。这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库最新版本数据的方式，叫当前读 (current read)。

快照读：

当执行select操作是innodb默认会执行快照读，会记录下这次select后的结果，之后select 的时候就会返回这次快照的数据，即使其他事务提交了不会影响当前select的数据，这就实现了可重复读了。快照的生成当在第一次执行select的时候，也就是说假设当A开启了事务，然后没有执行任何操作，这时候B insert了一条数据然后commit,这时候A执行 select，那么返回的数据中就会有B添加的那条数据。之后无论再有其他事务commit都没有关系，因为快照已经生成了，后面的select都是根据快照来的。

当前读：

对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式。在执行这几个操作时会读取最新的记录，即使是别的事务提交的数据也可以查询到。假设要update一条记录，但是在另一个事务中已经delete掉这条数据并且commit了，如果update就会产生冲突，所以在update的时候需要知道最新的数据。也正是因为这样所以才导致上面我们测试的那种情况。
那么，mvcc无法解决当前读时的幻读问题，那innodb是如何解决这类情况的呢？
这里就用到了 MVCC + next-key locks
那么什么是next-key locks呢？后面我们来说一下innodb中常见的锁

四、Innodb的锁

官方关于不同执行引擎的介绍：
https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
innodb:

通过上图，我们看到innoDB不同于myisam和Memory引擎的地方，主要是支持外键、支持行级锁、MVCC，那么行级锁和表级锁有什么区别呢？InnoDB有哪几种锁的类型呢？
锁是用于控制不同事务对共享资源的并发访问

表锁与行锁的区别：

锁的粒度： 表锁 > 行锁  （行锁相当于锁住柜子，表锁相当于将整个房子锁住）
加锁效率： 表锁 > 行锁
冲突概率：表锁 > 行锁
并发性能：表锁 < 行锁(拿不到锁就只能等)
InnDB存储引擎支持行锁和表锁 （所有行的行锁）

InnoDB锁的分类:

1、共享锁(行锁) ：Shared Locks

定义：读锁(S锁)，多个事务对于同一数据可以共享访问，只能读不能修改
使用方式：
加锁：select * from table where id = 1 LOCK IN SHARE MODE
释放锁：commit/rollback

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

定义：写锁(X锁)，互斥锁/独占锁，事务读取了一个数据的X锁，其他事务就不能再获取该行的锁(共享锁、排他锁)，只有获取了排他锁的事务是可以对数据行进行读取和修改
使用方式：
DELETE/UPDATE/INSERT 语句默认加上X锁
加锁：
select * from table where … FOR UPDATE
释放锁：
commit/rollback

思考：那么问题来了，我们说的共享锁，排他锁，锁住的到底是什么呢？是锁住的这条数据本身还是其他的东西？
我们来添加员工表来做个试验：
表结构：

CREATE TABLE `emp` (
  `id` int NOT NULL,
  `emp_no` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_emp_no` (`emp_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
1
2
3
4
5
6
7
8

这里我们除了id主键本来就是索引以外我们又添加了员工编号的btree普通索引
加入三条数据：

接下来我们开启两个窗口，代表两个事务

通过id查询:

-- 事务1：--
BEGIN
select * from emp where id = 1 FOR UPDATE
1
2
3

我们通过共享锁查询id为1的数据，不提交
然后事务2读取id为2的数据

-- 事务2 --
BEGIN
select * from emp where id = 2 FOR UPDATE
1
2
3

结果：

发现事务2读取id为2的数据成功，并没有被阻塞

通过age年龄查询：

当我们查询age年龄为18的时候

-- 事务1：--
BEGIN
select * from emp where age = 18 FOR UPDATE
1
2
3

然后事务2读取age为20的数据

-- 事务2 --
 BEGIN
 select * from emp where age = 20 FOR UPDATE
1
2
3

神奇的事情发生了：

事务二读取的时候发生阻塞了，那当我们查emp_no的时候呢？大家应该可以猜到结果，emp_no也不会阻塞
结论：
InnoDB的行锁是通过给索引上的索引项加锁来实现的通过索引的数据搜索，innoDB才会使用行级锁，不然InnDB将使用表锁，也就锁住了整个表

3、意向共享锁（表锁）：Intention Shared Locks

一个数据行加共享锁前必须先取得该表的意向共享锁，意向共享锁之间是可以相互兼容的

4、意向排他锁（表锁）：Intention Exclusive Locks

一个数据行加共享锁前必须先取得该表的意向共享锁，意向共享锁之间是可以相互兼容的
意向锁
意向锁(IS、IX)是InnoDB引擎操作数据之前自动加的，不需要用户干预意向锁
目的：
当某个事务想去进行锁表时，需要先判断意向锁是否存在，如果存在说明有事务在执行，锁表操作直接会失败
例子：
事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁就会被阻塞
再举一个直观的例子：
比如电影院里有人在看电影，每进去一个人就要买张票(给电影院上一个表级锁)，然后有个土豪来了，说他要包场(锁住整个表)，只能留他一个人，结果发现已经有很多人买了票了(已经有意向锁)，那他就不能包场了
共享锁、排他锁、意向锁的互斥关系

5、临键锁 ：Next-key Locks

当sql执行按照索引进行数据的检索、
且查询条件为范围查找(between and, <,>等)、
且有数据命中时，该SQL语句加上的锁为Next-key locks
锁住命中记录区间 + 下一个区间 (区间：左开右闭)

我们新加一张表，只存id和name，记录为：

当有4条记录时，我们会将这张表的数据划为5大区间[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

当我们进行查询

select * from table where id>4 and id < 8 for update;
1

锁住的是(1,6]  (6,10] 区间

select * from table where id > 6
1

锁住的是(6,10] (10,14],(14,+正无穷)
为什么innoDB默认使用next-key lock作为默认的行锁算法呢？
因为要解决幻读问题，就像我们刚才查的是id > 6，会锁住6到正无穷的全部区间，这样，我们去执行insert 8,insert 10就都会被阻塞，这样就不会出现幻读的问题了

RR隔离级别才有临键锁，因为其他隔离级别不需要解决幻读的问题

6、间隙锁 ：Gap Locks

当SQL执行按照索引进行数据的检索时，且查询条件的数据不存在时，这时sql语句加上的锁即为Gap locks(间隙锁)，左开右开
还是刚才的数据

select * from table where id = 5 for update; 锁住的是(1,6)区间，因为5是不存在的

7. 记录锁：Record Locks

当sql执行按照唯一性(primary key、unique key)索引进行数据的检索时，且查询条件等值匹配且查询的数据命中存在，这时sql语句上加上的锁即为记录锁Record locks，锁住的是具体的索引项

总结：
innodb根据查询条件的不同使用不同的锁，当范围查询命中索引使用临键锁，当查询没有命中时则使用间隙锁，当等值匹配且查询命中则使用记录锁

五. DeadLock死锁

产生原因：
两个并发事务及以上，事务之间产生加锁的循环等待
规避原则：

• 为表添加合理的索引，避免全表加锁
• 类似的业务逻辑以固定的顺序访问表和行
• 如果业务允许，过大的事务可以拆成小的事务
• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率
• 如果业务允许，将隔离级别降低也是很好的选择

查看mysql死锁信息的命令：

mysql>show engine innodb status
1

具体查看方式请参考：
MySQL中show engine innodb status的详解

总结

这里我们从事务的基本概念出发，认识了事务是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；事务是一组不可再分割的操作集合
接着了解了事务的四大特性，原子性、隔离性、持久性、一致性
并且了解了在mysql中，四大特性是如何实现的，还有就是原子性、隔离性、持久性都是为了保证一致性。
然后我们讲了多事务会产生的脏读、幻读、不可重复读的几大问题，并举例说明，引出了事务的四大隔离级别，而innodb的默认隔离级别RR(可重复读) 又神奇地解决了幻读的问题。
紧接着通过我们的测试和学习，了解了innodb是通过mvcc + next locks 来解决幻读的问题，mvcc多版本控制解决了快照读的方式，却无法解决当前读的幻读问题。
所以又引出了mysql的锁，了解了共享锁、排他锁、意向共享锁、意向排他锁、next-locks、gap-locks、records-locks的基本作用和实现方式。
最后我们总结了几条防止死锁的原则和如何查看死锁的sql语句。

谢谢大家，有问题欢迎提问