最全MySQL事务与锁机制解析

1. 什么是事务？

1.1 数据库事务的典型场景

例如金融系统： 订单：同时需要操作订单表和资金表和物流表，这个时候我们就需要他们在一个事务里面完成； 转账：同时一个账户的金额增加一个账户的金额减少

1.2 什么是事务？

---

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

1.3 那些存储引擎支持事务？

---

InnoDB，这也是它成为mysql的默认引擎的原因之一

1.4 事务的四大特性

(1) 原子性 Atomicity: 最小无法分割的操作单位 要么全都成功 要么全部失败，部分失败部分成功如何让其回滚(undo log) (2) 一致性 Consistent: 指数据库的本身的约束执行前后一致，另外业务数据本身的约束 (3) 隔离性 Isolation: 大量的事务去同时去操作表，存在并发问题，隔离性保证所以的对表的行或列的操作数据的透明性，互相不干扰。 (4) 持久性 Durable: 不能因为数据库的重启等操作引起,数据库的崩溃恢复的能力由（由事务日志redo log实现：操作数据的时候会先写入bufferPool缓冲池当中，并且记录redo log；如果发现刷盘也就是同步到磁盘之前出现异常，那么在数据库重启之后读取redo log恢复重新写入磁盘)。

1.5 数据库什么时候会出现事务

我们在执行什么命令的时候 数据库服务端会出现事务呢？这些事务跟一个配置参数有关autocommit default ON;

如果要手动的开始事务和提交事务，开始事务： start 和 begin(这个命令参数稍微少点) 结束事务：commit 或者 rollback； 补充: 如果事务并没有提交时候 比如在Navicat的可视化操作客户端 关掉窗口时候 会自动rollback；一个事务所持有的锁 什么时候会释放掉呢？ 事务什么时候结束呢？事务和锁关系？

2. 事务并发带来的问题？

如果说没有事务隔离性这个机制的话？ 会出现什么问题：

2.1 脏读(dirty read)

一个事务读取到了另一个事务未提交的数据！：然后这个未提交的事务如果发生rollback会导致相同查询的结果不一致！

2.2 不可重复读(Non repeatable read)

事务A并发连续读取，在连续相同的条件下读取到了不同的数据，因为在并发读时候事务B提交了数据update或delete修改的操作！

2.3 幻读(Phantom read)

事务A并发连续读取，在连续相同的条件下读取到了数据的条数发生了变化，因为并发读时候事务B提交了操作insert数据！

---

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

2.4 事务的隔离级别(SQL92 ANSI/ISO标准)

2.4.1 Read Uncommitted(未提交读) – 未解决任何并发问题

事务未提交的数据对其他事务也是可见的，会出现脏读

2.4.2 Read committed(已提交读) – 解决了脏读问题

一个事务开始之后只能看到已提交的事务所做的修改，会出现不可重复读and幻读

2.4.3 Repeatable Read(可重复读) – 解决不可重复读问题

在同一个事务当中多次读取同样的数据结果是一样的，这种隔离级别未定义解决幻读的问题

2.4.4 Serialization(串行化) – 解决所有问题

最高的隔离级别，通过强制事务串行执行！造成并发效率下降 通常不会使用？

---

MySQL InnoDB对事物隔离级别的支持程度： 事务隔离级别脏读不可重复读幻读未提交读 Read Uncomitted可能可能可能已提交读 Read Comitted不可能可能可能可重复读 Repeatable Read不可能不可能Oracle可能，InnoDB不可能串行化 Serializable不可能不可能不可能 Oracle默认RC，支持RC和串行化； MySQL InnoDB默认RR，都支持其他隔离级别；

3. MVCC

思考： 如果要解决读一致性问题，保证一个事务前后两次读取的数据结果一致，如何实现事务隔离？ 事务隔离级别的解决方案：

在读取数据前，对其加锁，阻止其他事物对数据进行修改(LBCC) Lock Based Concurrency Control.生成一个数据请求时间点的一致性数据快照(Snapshot)，并用这个快照来提供一定级别(语句级或事务级)的一致性读取(MVCC) Multi Version Concurrency Control.

---

在InnoDB当中 为每行记录都实现了三个隐藏的字段： DB_ROW_ID: 6bytes 行标识 DB_TRX_ID: 6bytes 插入或更新行的最后一个事务的事务ID，自动递增（创建版本号） DB_ROLL_PTR: 7bytes 回滚指针（删除版本号）

begin;select * from test; 只能查找到创建时间小于或等于当前事务id的数据 时间大于当前时间的事务id的事务如果进行删除数据，依然会查询到被删的数据

总结：只要另外的事务id大于当前的事务id的进行crud操作， 当前的事务查询只能得要之前的固定的数据结果，无法影响当前事务的读取结果

---

begin;insert into test (…) values (…);

4. 锁的基本类型

InnoDB事务隔离级别的实现：MVCC and 基于锁实现

4.1 锁的粒度

表锁(table-level locking)和行锁(row-level locking)的区别：

锁粒度：表锁>行锁 加锁效率：表锁>行锁 冲突概率：表锁>行锁 并发性能：表锁<行锁 问题：MyIsam和InnoDB分别支持什么粒度的锁？ MyIsam只支持表锁，InnoDB都支持

---

4.2 行锁之共享锁(Shared Locks)

共享锁：又称为读锁，简称S锁，就是多个事务对于同一个数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

加锁释放锁的方式：

select * from student where id=1 LOCK IN SHARE MODE; // 释放锁方式 事务结束时候他持有的锁会释放 commit / rollback;

4.3 行锁之排它锁(Exclusive Locks)

排它锁：又称为写锁，简称X锁，排它锁不能与其他锁并存，如果一个事务获取了一行数据的排它锁，其他数据就不能再获取该行的锁， 只有这个获取到排它锁的事务能对这个数据行进行读取和修改。

当其他锁获取不到该行锁时候 会进入阻塞状态 默认50秒自动放弃获取锁 加锁释放锁方式：

// 自动 默认加上X锁 delete/update/insert // 手动 select * from student where id=1 FOR UPDATE; // 释放 commit/rollback

4.4 表锁之意向共享锁(IS), 意向排它锁(IX)

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

意向共享锁(Intention Shared Lock, 简称IS锁)：表示事物准备给数据行加入共享锁，也就是说一个数据行加入共享锁之前必须获取该表的IS锁。 意向排它锁(Intention Exclusive Lock, 简称IX锁)：表示事物准备给数据行加入排它锁之前必须获得到该表的IX锁。

思考：为什么需要(表级别的)意向锁？ 不是真的锁住一张表 而是提供一个标志 说明这个表至少有一个事务锁定这表里面任意一行或者多行数据 换个姿势思考：我们在给一张表加上表锁 必须有一个前提：没有其他事务锁定表里面任意数据行！

---

思考：锁的作用？ 解决资源竞争问题 并发问题 事务对数据的并发访问的问题 锁到底锁住了什么？ 是一行(row)数据吗？ 是一个字段(column)吗？ 下节知晓

5. 锁的原理！

到底锁住了什么？沃日到底锁的啥啊！！！ 开始探索，分别建立几个测试表：

---

没有索引的：

// 手动给某个数据行加上排它锁 begin;select * from test1 where id=1 for update; // 然后尝试给其他数据行加锁 发现失败了 select * from test1 where id=2 for update; // 然后尝试插入数据 发现也失败了 insert into test1 (3,'angel')values(id,name); // 居然发生了锁表的情况！！！ rollback;

---

有主键索引的

// 手动给某个数据行加锁排它锁 begin;select * from test2 where id=1 for update; // 然后尝试给其他数据行加锁 发现成功了 select * from test2 where id=2 for update; rollback;

只有辅助索引的

// 手动给某个数据行加锁排它锁 待续自己探究 begin;select * from test3 where id=1 for update;

有主键索引的又有辅助索引的

// 此时id字段是主键索引 name字段是唯一索引(辅助索引)unique // 通过name字段 手动给某个数据行加锁排它锁 begin;select * from test4 where name='baby' for update; // 然后尝试根据id字段 给当前数据行加锁 发现阻塞失败了 select * from test4 where id=3 for update; // 另外也尝试了 根据id字段给其他数据行加锁 发现成功了 select * from test4 where id=1 for update; //

通过上面的探索 我们发现了什么？

既不是锁住的字段 也不是数据行 那到底锁住的是啥？有索引和没索引 有主键索引和只有唯一索引

答案就在这里 ：InnoDB锁住了主键索引！

5.1 什么是聚集索引！

此小节属于知识点补充，具体在mysql的索引专题会有介绍！ 索引中键值的逻辑顺序和索引中的数据物理存放地址的顺序是一致的

哪些情况下会被作为聚集索引呢？ 1. 当手动添加了主键时候 这个主键就是聚集索引 2. 如果没有主键 那么把该表的第一个非空唯一索引作为聚集索引 3. 如果以上都没有，InnoDB会自动每行数据隐藏生成一个_rowid作为聚集索引 4. InnoDB引擎会为每张表都加一个聚集索引，而聚集索引指向的的数据又是以物理磁盘顺序来存储的，自增的主键会把数据自动向后插入，避免了插入过程中的聚集索引排序问题。如果对聚集索引进行排序，这会带来磁盘IO性能损耗是非常大的。

思考

为什么会锁表？ 对于第一张表没有手动加索引，但是并不是代表没有索引，聚集索引必然有！ 所以对于增删改查加锁时候where条件语句 会走全表扫描 从而造成锁表！为什么通过唯一索引（辅助索引）加锁会跟主键索引冲突呢？ 这个辅助索引和主键索引的关系有关了 所以在用辅助索引字段加锁时候 会锁住对应的主键索引！ 补充：InnoDB当中辅助索引的叶子结点上存储的是对应的主键索引的键值， 当使用辅助索引查询的时候 先根据辅助索引的键值查询到对应的叶子结点的主键值 然后去主键索引的B+树上找到对应的叶子结点上的数据！！！

---

6. InnoDB行锁算法！

6.1 聚集索引键值区间的划分

此时在表里已经有record id分别是 1 4 7 10

记录（record）：就是insert的数据对应主键id 间隙（gap）：由record记录划分出来的间隙，两边都是开区间(-endless,1) (1,4) (4,7) (7,10) (10,+endless) 临键（Next-key）：是间隙+右边的record形成的区间，是左开右闭的区间(-endless,1] (1,4] (4,7] (7,10] (10,+endless)

思考：字符可以排序吗？ 如果是字符也能排序 用是字符的ASCII码进行排序

6.2 记录锁(Record Lock): 锁定记录

唯一性索引（唯一/主键）等值查询，精准匹配select * from test where id=1 for update;此时会锁住id=4

6.3 间隙锁(Gap Lock): 锁定范围

注意：Gap锁之间不冲突

begin;select * from test where id=6 for update;锁住(4,7) 然后insert into test (5,‘name’) values (id,name);会失败 然后Gap Lock不存在冲突 与排它锁区别大 select * from test where id=6 for update；会成功 select * from test where id>1 and id<7 for update;锁住(1,7] select * from test where test id>10 for update;锁住(10, +endless)

6.4 临键锁(Next-key Lock)

select * from test where id>5 and id<9 for update;锁住了 (4,7] and (7,10] select * from test where id=3 for update;锁住(1,4] ，此时id=3没有匹配到对应的record，但是锁住对应的间隙区间+临键！！！ 最后一个key的下一个左开右闭的区间！

---

RRRC普通的selectMVCCMVCC加锁的select select…in share mode or for update /insert update deleteRecord Lock/Gap Lock/Next-key LockRecord Lock

这就是为什么RR解决InnoDB的幻读问题！！！Record Lock+Gap Lock+Next-key Lock RC里面只有Record Lock