Mysql之事务隔离级别与锁机制

事务概述

数据库一般都会并发执行多个事务，多个事务间可能会并发对相同的一批数据进行增删改查操作，就可能会造成一些事务隔离性问题，例如：脏写、脏读、不可重复读、幻读。

事务的特性

事务（Transaction）是由一系列数据库操作语句组成的一个逻辑单元。事务中的操作要么全部成功执行，要么全部失败回滚，保证数据库的数据一致性和完整性.下面是事务的四大特性：

原子性（Atomicity）
事务中的所有操作要么全部成功，要么全部失败回滚，不会只执行其中的一部分操作。

一致性（Consistency）
事务开始前和结束后，数据库的状态保持一致性，即满足预定义的数据完整性约束。

隔离性（Isolation）
多个事务并发执行时，每个事务的操作都好像在独立执行，相互之间不会影响。

持久性（Durability）
一旦事务提交成功，其对数据库的修改就是永久性的，即使在系统故障的情况下也不会丢失。

事务隔离性问题

脏写（Dirty Write）

// 描述
脏写指的是一个事务修改了另一个事务未提交的数据，导致未提交的数据被覆盖.
// 场景
假设同时开启事务A,B,选择数据表中同一行数据进行修改，
A事务先完成了对数据的修改并提交了事务，之后B事务对同一数据也修改提交。
最终数据库里的数据是事务B修改的数据。覆盖了A之前修改的数据。就造成了脏写。

脏读（Dirty Read）

// 描述
脏读指的是一个事务读取了另一个事务未提交的数据，后续该数据被回滚，导致读取的数据是无效的.
// 场景
事务A更新一条记录并未提交，事务B读取了这条未提交的数据。
事务A回滚了更新，导致事务B读取的数据是无效的.

不可重复读（Non-Repeatable Read）

// 描述
不可重复读指的是一个事务在读取同一条记录的过程中，多次读取的数据不一致。
// 场景
事务A读取一条记录，事务B更新了这条记录并提交。
事务A再次读取同一条记录，发现数据已经发生了变化。

幻读（Phantom Read）

// 描述
幻读指的是一个事务在读取一系列记录的过程中，多次读取的记录数不一致。
幻读类似不可重复读;不可重复读针对查询操作;幻读针对读是插入和删除操作。
// 场景
事务A查询了一批数据的总数，事务B插入了一些新的记录并提交。
事务A再次查询相同的数据，发现记录数发生了变化。

事务隔离级别

不同隔离级别下的问题发生情况：
读未提交（Read Uncommitted）：会出现脏写、脏读、不可重复读和幻读问题，因为事务可以读取未提交的数据。

读已提交（Read Committed）：会出现脏写、不可重复读和幻读问题，因为事务只能读取已提交的数据，但其他事务可能在事务读取的过程中提交或回滚。

可重复读（Repeatable Read）：会出现脏写和幻读问题，因为事务在读取数据期间，其他事务可能插入新数据或更新数据。

串行化（Serializable）：可以避免所有并发问题，因为事务之间互相串行执行，但可能导致性能下降。

// Mysql数据库默认的隔离级别是可重复读。
// 查看当前数据库的事务隔离级别
show variables like 'tx_isolation';
// 设置数据库隔离级别
set tx_isolation='read-uncommitted'; // 读未提交
set tx_isolation='read-committed'; // 读已提交
set tx_isolation='repeatable-read'; // 可重复读
set tx_isolation='serializable'; // 串行化

锁机制

MySQL的锁机制是为了保证并发事务的隔离性和数据完整性而设计的。MySQL支持多种锁类型，可以根据业务需求和事务隔离级别进行灵活选择。

锁分类

乐观锁

乐观锁是指在事务提交时，才检查数据是否被其他事务修改过。在读取数据时不会加锁，而是在更新数据时通过版本号或时间戳等机制检查数据是否过期，若未过期则更新成功，否则回滚.

悲观锁

悲观锁是指在事务读取数据时就对数据进行加锁，避免其他事务对同一数据进行修改.悲观锁常用于数据写操作，防止数据被其他事务并发修改。

读锁

读锁是一种共享锁，多个事务可以同时对同一资源进行读取，但不允许有事务修改数据。

// 添加读锁
select * from T where id=1 lock in share mode;

写锁

写锁是一种排他锁，当一个事务对某资源加写锁后，其他事务不能对该资源进行读或写操作.

// 添加写锁
select * from T where id=1 for update;

行锁

行锁是对单行数据进行加锁，一个事务对某行加锁后，其他事务不能修改该行数据，但可以修改其他行数据. （行锁是一种写锁）.

// InnoDB的行锁实际上是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁。
// 所以查询sql索引不存在时，从行锁升级为表锁，但是只有不可重复读隔离级别才会这样
// 因为不可重复读隔离级别需要解决可重复读或幻读问题，所以在遍历扫描聚集索引记录时，为了防止扫描过的索引被其它事务修改(不可重复读问题) 或 间隙被其它事务插入记录(幻读问题)，从而导致数据不一致，所以MySQL的解决方案就是把所有扫描过的索引记录和间隙都锁上.

表锁

表锁是对整个表进行加锁，当一个事务对表加锁后，其他事务不能对该表进行读写操作.表锁一般用于整表操作，适用于对表进行DDL操作等场景.

页锁

页锁是对数据页进行加锁，一个页包含多行数据。只有BDB存储引擎支持页锁.

意向锁

意向锁是一种间接锁，用于在表级别上指示某一行或某一页将要被锁定，以避免不必要的冲突。

假设A事务已经给T表读某行记录增加了行锁，这时B事务要来给T表上表锁，正常B需要遍历整张表看有没有行记录被锁住，
锁住则不能添加表锁，这过程太消耗性能，所以在A事务添加行锁后，同时会给表设置一个标识，这个标识就是意向锁，
这样B事务过来一看有这个标识就说明已经有锁了，避免了锁冲突.

间隙锁

间隙锁是用于在索引范围之间的间隙上设置的锁，用于防止其他事务在索引范围内插入新行.

// 间隙锁是在可重复读隔离级别下才会生效
// 下图表中的间隙为（id为主键）：
(3,10)、(10,30)、(30,50)、(50,100)、(100,+∞)
// 举例：
// 开启事务A -> 执行sql1
// begin;
// select * from user where id = 7 for update;

// 开启事务B -> 执行sql2
// begin;
// insert user(id, name, age, job, skill) VALUES (7,'宇智波斑',100,'SSS','轮回眼');

// -> 会发现sql2一直被堵塞,直到事物A提交后，sql2才执行成功.

Mysql默认级别是repeatable-read，有幻读问题，间隙锁是可以解决部分幻读问题的。

临键锁

临键锁是行锁和间隙锁的结合.

锁等待分析

// 执行查看所状态
show status like 'innodb_row_lock%';
// 执行结果
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数

//  查看INFORMATION_SCHEMA系统库锁相关数据表
//  查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
// 查看锁，8.0之后需要换成这张表performance_schema.data_locks
select * from INFORMATION_SCHEMA.INNODB_LOCKS;  
// 查看锁等待，8.0之后需要换成这张表performance_schema.data_lock_waits
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;  

// 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id
// 查看近期死锁日志信息
show engine innodb status;

总结

隔离级别决定了事务之间的隔离程度，不同隔离级别对应不同的并发控制方式，可以根据业务需求选择合适的隔离级别。锁机制则是实现隔离级别的具体手段，悲观锁适用于并发写较多的场景，而乐观锁适用于并发写较少的场景。在实际应用中，需要根据业务场景和性能需求选择合适的隔离级别和锁机制，以保证数据的一致性和并发性。