一文搞懂mysql事务

事务

作用：事务将数据库从一种一致性状态转换为另一种一致性状态；

组成：事务可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成；

特征：确保要么所有修改都已经保存，要么所有修改都不保存；

手动管理事务代码，保证我们代码区间有事务特征。

set autocommit = 0;

ACID特性

• 原子性（A）: 事务操作要么都做（提交），要么都不做（回滚）； 

• 一致性（C）:在事务执行前后，数据库完 整性约束没有被破坏；一个事务单元需要提交之后才会被其他事务可见。

• 隔离性（I）:要求每个读写事务的对象对其他事务的操作对象能相互分离，并发事务之间不会相互 影响，设定了不同程度的隔离级别，通过适度破环一致性。  （mvcc 快照读）

• 持久性（D）:事务提交后，事务DML操作将会持久化（写入 redolog 磁盘文件 哪一个页 页偏移值 具体数 据）；即使发生宕机等故障，数据库也能将数据恢复。redolog 记录的是物理日志；

 

事务控制语句 

-- 显示开启事务
START TRANSACTION | BEGIN
-- 提交事务，并使得已对数据库做的所有修改持久化
COMMIT
-- 回滚事务，结束用户的事务，并撤销正在进行的所有未提交的修改
ROLLBACK
-- 创建一个保存点，一个事务可以有多个保存点
SAVEPOINT identifier
-- 删除一个保存点
RELEASE SAVEPOINT identifier
-- 事务回滚到保存点
ROLLBACK TO [SAVEPOINT] identifier

 隔离级别

READ UNCOMMITTED
读未提交；该级别下读不加锁，写加排他锁，写锁在事务提交或回滚后释放锁；
特点:只对写操作上锁，表明事务执行期间，事务A修改了数据 事务B执行期间内读取 会获得最新数据。

READ COMMITTED
读已提交（RC）；从该级别后支持 MVCC (多版本并发控制)，也就是提供一致性非锁定读；此时读取操作读取历史快照数据；该隔离级别下读取历史版本的最新数据，所以读取的是已提交的数据；
特点：优化了上级特点，只在事务A提交后 才能读取到修改的数据。事务A修改了数据 事务A修改了数据并提交 事务B执行期间内读取 会获得最新数据。

REPEATABLE READ
可重复读（RR）；该级别下也支持 MVCC，此时读取操作读取事务开始时的版本数据；
特点：对上级优化进行修改，在事务开启前，创建快照表，其他事务来读取这个快照表。
事务A开启事务，修改了数据 事务A修改了数据并提交 
事务B执行期间内读取的数据是快照bat的数据。

SERIALIZABLE
可串行化；该级别下给读加了共享锁；所以事务都是串行化的执行；此时隔离级别最严苛；

 MySQL innodb默认支持的隔离级别是 REPEATABLE READ；

基本命令操作

-- 设置隔离级别
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 或者采用下面的方式设置隔离级别
SET @@tx_isolation = 'REPEATABLE READ';
SET @@global.tx_isolation = 'REPEATABLE READ';
-- 查看全局隔离级别
SELECT @@global.tx_isolation;
-- 查看当前会话隔离级别
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
-- 手动给读加 S 锁
SELECT ... LOCK IN SHARE MODE;
-- 手动给读加 X 锁
SELECT ... FOR UPDATE;
-- 查看当前锁信息
SELECT * FROM information_schema.innodb_locks;

上列常用知识

什么是session? 一个控制台窗口来操作mysql就是一个会话，mysql通过一个会话一个线程的方式来实现高并发，默认的最大会话数是 151。

MySQL 实例的最大会话数限制
SHOW VARIABLES LIKE 'max_connections';

手动加xs锁有什么用，解决了什么问题？

不加 -> 快照读 -> 不可重复读功能 -> 读快照bat

加-> 当前读 ->已提交读功能 ->读最新

锁

锁机制用于管理对共享资源的并发访问；用来实现事务的隔离级别 ；

锁类型 

MySQL当中事务采用的是粒度锁；针对表（B+树）、页（B+树叶子 节点）、行（B+树叶子节点当中某一段记录行）三种粒度加锁；

共享锁和排他锁都是行级锁；

共享锁（S）和排他锁 （X）

共享锁（S）事务读操作加的锁；对某一行加锁；

在 SERIALIZABLE 隔离级别下，默认帮读操作加共享锁；

在 REPEATABLE READ 隔离级别下，需手动加共享锁，可解决幻读问题；

在 READ COMMITTED 隔离级别下，没必要加共享锁，采用的是 MVCC；

在 READ UNCOMMITTED 隔离级别下，既没有加锁也没有使用 MVCC；

排他锁(X) 事务删除或更新加的锁；对某一行加锁；

在4种隔离级别下，都添加了排他锁，事务提交或事务回滚后释放锁；

意向共享锁（IS）和意向排他锁(IX) 

目的：为了告诉其他事务，此时这条表被一个事务在访问；

作用：排除表级别读写锁 （防止全面扫描 加锁，性能优化）；

ai锁

自增锁，是一种特殊的表级锁，发生在 AUTO_INCREMENT 约束下的插入操作；采用的一种特殊 的表锁机制（较低概率造成B+树分裂）；完成对自增长值插入的SQL语句后立即释放；在大数据 量的插入会影响插入性能，因为另一个事务中的插入会被阻塞；

从MySQL 5.1.22开始提供一种轻 量级互斥量的自增长实现机制，该机制提高了自增长值插入的性能；

锁的兼容性

记住一点 “写互斥万物，读只与写冲突”

锁的算法

Gap Lock（重点）间隙锁

锁定一个范围，但不包含记录本身；全开区间；REPEATABLE READ级别及以上支持间隙 锁；

Record Lock：记录锁

单个行记录上的锁；

Next-Key Lock记录锁+间隙锁，

锁定一个范围，并且锁住记录本身；左开右闭区间；

Insert Intention Lock 插入意向锁

insert操作的时候产生；在多事务同时写入不同数据至同一索引间隙的时候，并不需 要等待其他事务完成，不会发生锁等待。

假设有一个记录索引包含键值4和7，两个不同的事务分别插入5和6，每个事务都会产生一个加在 4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

 锁的算法兼容

锁的对象 

行级锁是针对表的索引加锁；索引包括聚集索引和辅助索引；

表级锁是针对页或表进行加锁；

重点考虑 InnoDB 在 read committed 和 repeatable read 级别下锁的情况；

举例students 表作为实例，其中 id 为主键，no（学号）为辅助唯一索引，name（姓名）和 age（年龄）为二级非唯一索引，score（学分）无索引。

UPDATE students SET score = 100 WHERE id = 15;
聚集索引：
如果存在id=15 两种模式下都会对叶子节点key=15进行加x锁。
如果没有id=15，只做不可重复读级别会对15加间隙锁，保证k=15这个数据不能被其他事务访问。

UPDATE students SET score = 100 WHERE no = 'S0003'
唯一索引：
如果存在id=15 会对唯一索引树节点加x锁并且回表二次查询聚集索引树节点加x锁
不存在 同理，只做不可重复读级别加间隙锁，保证其他事务不可操作该key。

UPDATE students SET score = 100 WHERE name = 'John';
普通索引：
如果存在name = 'John'（这个值不唯一），对条件成立的节点进行加x锁，并且回表聚集索引节点加x锁
如果不存在name = 'John'，同理，只做不可重复读级别加间隙锁，保证其他事务不可操作该key。

UPDATE students SET score = 100 WHERE score = 22;
无索引:
已提交读级别下加表锁 已提交读级别下加表锁和间隙锁

范围查询： UPDATE students SET score = 100 WHERE id <= 20;
聚集索引情况:
已提交读级别下对条件成立加x锁，不可重复读对条件成立加x锁并对每个值加间隙锁
辅助索引:
已提交读级别：对条件成立加x锁，并回表对聚集索引节点加x锁
不可重复读：条件成立加x锁并对每个值加间隙锁，回表加x锁... 比如 18 19 20 21这三个都被加上了x锁和间隙锁 21上间隙锁

 MVCC

多版本并发控制；用来实现一致性的非锁定读；非锁定读是指不需要等待访问的行上X锁的释放；

已提交读和可重复读实现原理。其中和 undo log 回滚日志和MVCC多版本并发控制，是实现已提交读和可重复读的重要工具。

思考：为什么读取快照数据不需要上锁？ 因为没有事务需要对历史的数据进行修改操作；

redo

redo 日志用来实现事务的持久性；

内存中包含 redo log buffer，磁盘中包含 redo log file； 当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的commit操 作完成才完成了事务的提交； redo log 顺序写，记录的是对每个页的修改（页、页偏移量、以及修改的内容）；在数据库运行 时不需要对 redo log 的文件进行读取操作；只有发生宕机的时候，才会拿redo log进行恢复；

 

undo

undo 日志用来帮助事务回滚以及 MVCC 的功能；存储在共享表空间中；undo 是逻辑日志，回滚 时将数据库逻辑地恢复到原来的样子，获取事务的 undo log ，记录解析 undo log ，记录执行逆操作。

DB_TRX_ID:事务ID DB_ROLL_PTR:回滚指针 

 

 并发读异常

 脏读

事务（A）可以读到另外一个事务（B）中未提交的数据；

 不可重复读

事务（A) 可以读到另外一个事务（B）中提交的数据；

幻读

两次读取同一个范围内的记录得到的结果集不一样；解决方法也很简单，搞清楚本地读和快照读。

丢失更新

脏读、不可重复读、幻读都是一个事务写，一个事务读，由于一个事务的写导致另一个事务读到了 不该读的数据；丢失更新是两个事务都是写；丢失更新分为提交覆盖和回滚覆盖； 回滚覆盖数据库 拒绝不可能产生，重点关注提交覆盖；

解决方法 对select加上x或s锁，防止B事务访问name=A，当B访问时会阻塞直到session A提交事务才继续执行。 

隔离级别下并发读异常 

 并发死锁

相反加锁顺序死锁

由于事务12 菱形上锁，导致死锁

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
-- 死锁事务1
UPDATE  `account_t` SET `money` = `money` - 100 WHERE `id` = 1 FOR UPDATE;
-- 死锁事务2
-- UPDATE FROM `account_t` SET `money` = `money` - 100 WHERE `id` = 2;
-- 死锁事务1
UPDATE  `account_t` SET `money` = `money` + 100 WHERE `id` = 2;
-- 死锁事务2
-- UPDATE FROM `account_t` SET `money` = `money` - 100 WHERE `id` = 1;
ROLLBACK

dml语句范围查询，会对条件成立的上锁。

BEGIN
-- 死锁事务1 
UPDATE  `account_t` SET `money` = `money` + 100 WHERE `name` >= 'A';
-- 死锁事务2
-- DELETE FROM `account_t` WHERE `id` >= 1;

 dml语句范围查询，会对条件成立的间隙锁。

-- 锁冲突死锁
BEGIN
-- 死锁事务1
UPDATE  `account_t` SET `money` = `money` + 100 WHERE `name` = 'C';
-- 死锁事务2
-- UPDATE FROM `account_t` SET `money` = `money` + 100 WHERE `name` = 'A';
-- 死锁事务1
INSERT INTO `account_t` (`id`,`name`,`money`) VALUES (4, 'BB', 1000);
-- 死锁事务2
-- INSERT INTO `account_t` (`id`,`name`,`money`) VALUES (5, 'CC', 1000);

避免死锁

查看死锁

-- 开启标准监控
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
-- 关闭标准监控
DROP TABLE innodb_monitor;
-- 开启锁监控
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
-- 关闭锁监控
DROP TABLE innodb_lock_monitor

系统参数

-- 开启标准监控
set GLOBAL innodb_status_output=ON;
-- 关闭标准监控
set GLOBAL innodb_status_output=OFF;
-- 开启锁监控
set GLOBAL innodb_status_output_locks=ON;
-- 关闭锁监控
set GLOBAL innodb_status_output_locks=OFF;
-- 将死锁信息记录在错误日志中
set GLOBAL innodb_print_all_deadlocks=ON;

命令

-- 查看事务
select * from information_schema.INNODB_TRX;
-- 查看锁
select * from information_schema.INNODB_LOCKS;
-- 查看锁等待
select * from information_schema.INNODB_LOCK_WAITS;

死锁解决

对于顺序相反型，调整执行顺序；

对于锁冲突型，更换语句或者降低隔离级别；

避免死锁 

尽可能以相同顺序来访问索引记录和表；

如果能确定幻读和不可重复读对应用影响不大，考虑将隔离级别降低为RC；

添加合理的索引，不走索引将会为每一行记录加锁，死锁概率非常大；

尽量在一个事务中锁定所需要的所有资源，减小死锁概率；

避免大事务，将大事务分拆成多个小事务；大事务占用资源多，耗时长，冲突概率变高；

避免同一时间点运行多个对同一表进行读写的概率；