MySQL事务隔离级别与锁机制

数据库一般都会并发的执行多个事务，多个事务可能会对同一批数据进行增删改查的操作，有可能会导致脏写、脏读、不可重复读、幻读问题，这些问题本质是数据库多事务的并发问题，为了解决并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制解决多事务并发问题。

1.MySQL事务及ACID特性

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性（ACID）

• 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全不执行。
• 一致性（Consistent）：在事务开始和完成时，数据必须保持一致状态，所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性。
• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“”环境执行，事务处理过程中的中间状态对外部是不可见的。
• 持久性（Durable）：事务完成之后，对于数据的修改是永久性的，即使系统出现故障也能够保持。

并发事务处理带来的问题

• 更新丢失（Lost Update）脏写：当两个或多个事务选择同一行，然后基于最初选定的值更新时，由于每个事物都不知道其他事务的存在，就会发生更新丢失问题：最后的更新覆盖了其他事务所做的更新。
• 脏读（Dirty Reads）：一个事务正在对一条记录进行修改，在这个事务完成并提交前，这条记录就处于不一致状态；此时，另一个事务也来读取同一条数据，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步处理，就会产生未提交数据依赖关系。这种现象叫做“脏读”。事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。
• 不可重读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔性。
• 幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。事务A读取到了事务B提交的新增数据，不符合隔离性。

2.MySQL事务隔离级

3.MySQL锁机制

• 从性能上分为乐观锁和悲观锁
• 从数据操作的力度分，分为表锁和行锁
• 从对数据库操作类型分，分为读锁、写锁（读锁和写锁都属于悲观锁）和意向锁

 乐观锁：每次读取数据的时候，认为不会有其他事务进行修改，不会上锁，但在修改的时候会判断一下是否有人修改数据（可以使用版本号和CAS机制判断），如果有冲突，要么再重试一次，要么切换为悲观的策略。

悲观锁：每次读取数据的时候，认为会有其他事务对数据进行修改，在读取的时候就上锁，如果其他事务想进行数据访问，需要等待当前事务完成并释放锁。

表锁：每次操作锁住整张表。开销小，加锁快。锁的力度比较大，发生冲突概率高，不会出现死锁。一般用在整表数据迁移的场景。

lock table 表名称 read(write),表名称2 read(write);

show open tables;

删除表锁

unlock tables;

行锁：每次操作锁住一行数据。开销大，加锁慢。锁的力度最小，发生锁冲突的概率最低。InnoDB支持行锁，支持事务（TRANSACTION）,MyIsam不支持行锁和事务。 

读锁(共享锁，S锁（Shared）)：事务T对A设置S锁，T可以对A进行读操作，不可对A进行修改，其他事务可以对A设置S锁，不能设置X锁（写锁），即：其他事务对A也可以进行读操作，不能进行修改。

写锁(排它锁，X锁（eXclusive）)：事务T对A设置X锁，T可以对A进行读和修改操作，其他事务不能对A设置任何锁，即：T事务对A上X锁后，其他事务对A不能进行任何操作，包括读取。

意向锁，I锁（Intention）：属于表锁，标识当前表是否存在行锁，分为意向共享锁（IS）和意向排他锁（IX）。意向锁是有存储引擎自己维护的，是内部机制，用户无法操作意向锁。

事务想要对表加锁，需要满足以下两点，不满足则申请锁会失败

• 当前没有其他事务持有该表的排他锁
• 当前没有其他事务持有该表的其中任意一行的排它锁

 如果没有意向锁，当前事务需要对表进行逐行扫描，查看是否有排他锁，如果表的数据量特别大，这种方式的效率特别低。

在对数据行加共享锁的同时加上意向共享锁（IS），加排它锁的同时加上意向排他锁（IX），其他事务在给表加表锁的时候直接看是否存在已上两种意向锁即可，不需要整表扫描。提升加锁效率。

意向锁和行锁可以兼容，与表级别共享锁和排它锁互斥，具体如下：

行锁与事务隔离级别案例分析

CREATE TABLE `stock` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(50) DEFAULT NULL COMMENT '商品名称',
  `stock` int(11) DEFAULT NULL COMMENT '库存',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO test.stock (id, name, stock) VALUES (1, 'A', 500);
INSERT INTO test.stock (id, name, stock) VALUES (2, 'B', 700);
INSERT INTO test.stock (id, name, stock) VALUES (3, 'C', 800);

（1）读未提交

-- 设置数据库事务级别：读未提交，Linux
set tx_isolation='read‐uncommitted';
-- Windows下使用此SQL
set session transaction isolation level read uncommitted;

场景模拟：打开两个客户端，同时连接同一个数据库，使用手动开启和提交事务，模拟事务冲突。

① A客户端开启事务，查询stock表数据，id=1商品A的库存是500。

② B客户端开启事务，查询stock表数据，id=1商品A的库存是500。

③ B客户端更新stock表，id=1商品A的库存  -50  。

④ B客户端查询stock表数据A商品的库存为450，数据更新没问题。

⑤ A客户端查询stock表数据A商品的库存也为450，注意此时的B客户端事务还未提交。

B还没有提交，但是A就可以查到B已经更新的内容，如果B客户端做了事务回滚，库存量又存在变化。那么A查询到的数据就是脏数据，出现脏读现象，同时会出现不可重复读。数据的准确性难以保证，对于数据库事务来讲也不符合事务之间的隔离性。

⑥ 针对①~⑤结果，把B客户端进行回滚操作。

⑦ 此时A不知道B做了回滚操作，更新库存 -50 。

⑧ 再次查询A客户端库存，发现更新前和更新后的值一样。

 ⑨ 在B客户端内新增一条数据不提交事务，在处于事务当中的A客户端内会查到B客户端未提交的新增数据，出现幻读现象，同时也不符合事务的隔离性。 

（2）读已提交

-- 设置数据库事务级别：读已提交，Linux
set tx_isolation='read‐committed';
-- Windows下使用此SQL
set session transaction isolation level read committed;

② 查询A和B客户端id=1库存都是450

③ B客户端做库存 -50，查询库存为400

④ A客户端查询库存，依然为450，解决了脏读问题

⑤ B客户端提交事务，查询库存数量为400

⑥ A客户端进行库存查询，id=1的库存也为400，可查询到已提交事务的数据，存在不可重复读问题，可重复读是在同一个事务中，当前事务没有做修改操作，每一次查出来的结果都是一样的。

⑦ B客户端新增一个商品E，不提交事务

⑧ A客户端查询不到B客户端未提交的事务的商品E。

⑨ B客户端提交事务

⑩ A客户端查询到B客户端已提交的新增商品E，此时A客户端仍然处于事务当中，出现了幻读 ，没有对事务进行隔离。

（3）可重复度读

-- 设置数据库事务级别：可重复度，Linux
set tx_isolation='repeatable-read';
-- Windows下使用此SQL
set session transaction isolation level repeatable read;

② 查询A和B客户端id=1库存都是400

③ B客户端做库存 -50，查询库存为350

④ A客户端查询库存，依然为400，解决了脏读问题

⑤ B客户端提交事务，查询库存为350

⑥ A客户端查询库存，依然为400，解决了不可重复读问题

⑦ 在上面基础上再，对A客户端进行库存 -50 操作。结果确减了100，对于当前事务看数据是错误的，但是A+B客户端的事务加起来数据是正确的，数据的一致性没有破坏。

可重复读的隔离级别下使用了MVCC(multi-version concurrency control)机制，select操作不会更新版本号， 是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）。

⑧ B客户端新增的商品F，在A客户端没有进行更新操作之前查不到，A客户端在未提交的事务内做了一次F商品库存更新，可以查到F商品，此时的A客户端仍处于事务当中， 出现了幻读，同时也不符合事务的隔离性。

间隙锁（Gap Lock）

 间隙锁，锁的是查询范围内存在间隙的数据行，即使它不存在，只要在这个范围内就会被锁定，上面例子中间隙有id为（6,10），（10,20），（20，+∞），上锁的间隙为（6-10）和（10,20）。

临键锁（Next-key Locks）

临键锁是行锁与间隙锁的组合。上面例子中间隙锁锁定了（6，10）和（10,20）然而并没有锁定6、10、20这三行数据，但是这三行数据在操作范围内，被架上了行锁，所以这3行数据也不能进行修改，(5,20]这个区间被称作为临键锁，此区间是左开右闭区间，包含20这个值。

（4）串行化

-- 设置数据库事务级别：串行化，Linux
set tx_isolation='serializable';
-- Windows下使用此SQL
set session transaction isolation level serializable;

① 设置事务隔离级别：串行化

② A和B客户端同时开启事务进行查询id=1的商品存库

③ B客户端做id=1的商品库存 -50 操作，结果被阻塞，时间过了报了超时错误。

④ B客户端查询id=2的商品库存

⑤ B客户端做id=2的商品库存 -50 操作，结果更新成功。

 在串行模式下innodb的查询也会被加上行锁。

 无索引行锁会升级为表锁（REPEATABLE READ会升级为表锁，READ COMMITTED不会升级为表锁）

 ① A客户端更新name='B'的库存-50，B客户端进行其他行（id=5）的更新，被阻断。

 ② 提交A事务之后在进行更新，更新成功。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁

锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)，例如：select * from stock where id = 2 for update; 这样其他session只能读这行数据，修改则会被阻塞，直到锁定行的session提交。 

4.MySQL锁优化建议

通过检查InnoDB_row_lock状态变量来分析系统的行锁争夺情况。

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: 系统启动后到现在总共等待的次数

当Innodb_row_lock_time_avg （等待平均时长）、 Innodb_row_lock_waits （等待总次数）、Innodb_row_lock_time（等待总时长）这三个参数较高时，就要分析系统中为什么会有这么多的等待，根据分析结果进行优化。

分析锁资源会使用如下SQL和命令：

‐‐ 查看事务 
select * from INFORMATION_SCHEMA.INNODB_TRX;

‐‐ 查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;

‐‐ 查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

‐‐ 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到

kill trx_mysql_thread_id

‐‐ 查看锁等待详细信息
show engine innodb status\G;

（1）查看事务

+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 135479 | RUNNING   | 2022-09-27 15:44:11 | NULL                  | NULL             |          1 |                   4 | NULL      | NULL                |                 0 |                 1 |                1 |                  1136 |               2 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
| 135478 | RUNNING   | 2022-09-27 15:38:10 | NULL                  | NULL             |          3 |                   3 | NULL      | NULL                |                 0 |                 1 |                2 |                  1136 |               1 |                 1 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+

（2）查看锁

+----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+
| lock_id        | lock_trx_id | lock_mode | lock_type | lock_table     | lock_index | lock_space | lock_page | lock_rec | lock_data |
+----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+
| 135479:213:3:4 | 135479      | X         | RECORD    | `test`.`stock` | PRIMARY    |        213 |         3 |        4 | 2         |
| 135478:213:3:4 | 135478      | X         | RECORD    | `test`.`stock` | PRIMARY    |        213 |         3 |        4 | 2         |
+----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+

（3）查看等待

+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 135479            | 135479:213:3:4    | 135478          | 135478:213:3:4   |
+-------------------+-------------------+-----------------+------------------+