八、MySQL 事务与锁

骑士梦

已于 2023-09-16 11:41:03 修改

阅读量875

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

于 2021-12-12 17:32:02 首次发布

骑士梦

本文链接：https://blog.csdn.net/qq_38667427/article/details/121888998

版权

MySQL 专栏收录该内容

13 篇文章 2 订阅

订阅专栏

本文详细介绍了MySQL的事务处理，包括事务的ACID特性、隔离级别以及死锁分析。同时，讨论了行级锁、表级锁、间隙锁和MVCC机制，解释了如何避免和解决死锁，并探讨了乐观锁和悲观锁的区别。此外，还强调了索引优化在提高并发性能中的重要性。

摘要由CSDN通过智能技术生成

一、MySQL 操作集
二、MySQL 数据类型
三、MySQL 函数
四、MySQL Explain
五、MySQL 索引优化一
六、MySQL 索引优化二
七、SQL 优化
八、MySQL 锁与事务
九、MySQL 索引数据结构
十、MySQL 外键

一、InnoDB 支持事务

InnoDB 和 MyISAM 的不同点：

InnoDB 支持事务（Transaction）。
InnoDB 支持行级锁（行锁支持事务）。

二、Transaction 事务

事务（Transaction）是由一组 SQL语句组成的逻辑处理单元。

事务具有四个属性：ACID

原子性（Atomicity）：
事务是一个原子操作单元。
其对数据的修改，要么全都执行，要么全都不执行。

一致性（Consistent）：
在事务开始和完成时，数据都必须保持一致状态。
这意味着所有相关的数据规则，都必须应用于事务的修改，以保持数据的完整性。

事务结束时，所有的内部数据结构（如：B树索引或双向链表）也都必须是正确的。

隔离性（Isolation）：
数据库系统提供一定的隔离机制。
保证事务在独立环境执行，不受外部并发操作的影响。

这意味着事务处理过程的中间状态，对外部是不可见的，反之亦然。

持久性（Durable）：
事务完成之后，它对于数据的修改是永久性的。
即使出现系统故障也能够保持。

-- 查询`MySQL`事务处理表。
select * from information_schema.INNODB_TRX;

1. 并发事务带来的问题

更新丢失（Lost Update）：
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新的问题。

最后的更新会覆盖了由其他事务之前所做的更新。

脏读（Dirty Reads）：
一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态。
这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些脏数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。

这种现象被形象的叫做脏读。
简单说事务A 读取到了事务B 已经修改但尚未提交的数据，还在这个数据基础上做了操作。
此时，如果事务B 回滚，事务A 读取的数据无效，不符合一致性要求。

不可重读（Non-Repeatable Reads）：
一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变或某些记录已经被删除。

这种现象就叫做 不可重复读。
简单说事务A 读取到了事务B 已经提交的修改数据，不符合隔离性。

幻读（Phantom Reads）：
一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为幻读。

简单说事务A 读取到了事务B 提交的新增数据，不符合隔离性。

2. 事务隔离级别

脏读、不可重复读和幻读，其实都是数据库读一致性问题。
必须由数据库提供一定的 事务隔离机制 来解决。

隔离级别	脏读（Dirty Reads）	不可重读（Non-Repeatable Reads）	幻读（Phantom Reads）
读未提交（Read-uncommitted）	可能	可能	可能
读已提交（Read-committed）	不可能	可能	可能
可重复读（Repeatable-read）	不可能	不可能	可能
可串行化（Serializable）	不可能	不可能	不可能

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大。

因为事务隔离，实质上就是使事务在一定程度上 串行化 进行，这显然与并发是矛盾的。

不同的应用对 读一致性 和 事务隔离 程度的要求也是不同的。

比如许多应用对 不可重复读 和幻读并不敏感，可能更关心数据并发访问的能力。

3. 设置事务隔离级别（默认 `REPEATABLE-READ` 可重复读）

# 查看当前数据库的`事务隔离级别`（默认`REPEATABLE-READ`可重复读）。
show variables like 'tx_isolation';

# 设置`事务隔离级别`。
set tx_isolation ='REPEATABLE-READ';

可重复读。

show variables like 'tx_isolation';
show variables like 'transaction_isolation';
-- REPEATABLE-READ

set transaction_isolation="READ-COMMITTED";
set tx_isolation="READ-COMMITTED";

三、锁定义

锁是计算机协调多个进程或线程 并发访问某一资源的机制。

在数据库中，除了传统的计算资源（如：CPU、RAM、I/O 等）的争用以外，数据也是一种需要用户共享的资源。

如何保证数据并发访问的 一致性、有效性，是所有数据库必须解决的一个问题。
锁冲突，也是影响数据库并发访问性能的一个重要因素。

四、锁分类

类型	特性
表级锁（table-level locking）	开销小，加锁快。不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发度最低。 MyISAM 存储引擎。
行级锁（row-level locking）	开销大，加锁慢。会出现死锁。锁定粒度最小，发生锁冲突的概率最低，并发度最高。 InnoDB 存储引擎。
页面锁（page-level locking）	开销和加锁时间，界于表锁和行锁之间。会出现死锁。锁定粒度界于表锁和行锁之间，并发度一般。 BDB 存储引擎。

从性能上分为：乐观锁（用版本对比来实现）和 悲观锁。
从对数据库操作的类型分为：读锁和写锁（都属于悲观锁）。

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁（排它锁）：当前写操作没有完成前，它会阻断其他 ~~读锁和写锁~~ 。

从对数据操作的粒度分为：表锁和行锁。

CREATE TABLE `mylock` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`NAME` VARCHAR (20) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;

# 插入数据
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('4', 'd');

1. 表锁

每次操作锁住整张表，InnoDB 和 MyISAM 都支持。
一个 MySQL 数据库，同一时间只能有一个表被锁住。

开销小，加锁快。
不会出现死锁。
锁定粒度大，发生锁冲突的概率最高，并发度最低。

# 手动增加表锁（当前库）
lock table '表名1' read(write), '表名2' read(write);

# 查看表上加过的锁（所有库）
show open tables;
show open tables where in_use > 0;

# 删除表锁（当前库）
unlock tables;

2. 读写锁

# 加读锁
lock table mylock read;

加读锁。

当前 Session 和其他 Session 都可以读取该表。
当前 Session ~~插入或更新~~ 被锁定的表都会报错。
其他 Session ~~插入或更新~~ 则会等待。

# 加写锁
lock table mylock write;

加写锁。

当前 Session 对该表的 ~~增、删、改、查~~ 都没有问题。
其他 Session 对该表的所有操作被阻塞。

总结：读锁会阻塞写，但是不会阻塞读。
而 写锁会把读和写都阻塞。

MyISAM 在执行查询（SELECT）语句前，会自动给涉及的所有表 加读锁。
MyISAM 在执行 ~~增、删、改~~ 操作前，会自动给涉及的表 加写锁。
对 MyISAM 表的读操作（加读锁）。
不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。
只有当读锁释放后，才会执行其它进程的写操作。
对 MylSAM 表的写操作（加写锁）。
会阻塞其他进程对同一表的 ~~读和写~~ 操作。
只有当写锁释放后，才会执行其它进程的读写操作。

3. 行锁分类

每次操作只锁住一行数据
InnoDB 支持行级锁

开销大，加锁慢
会出现死锁
锁定粒度最小，发生锁冲突的概率最低，并发度最高

CREATE TABLE `account` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`name` varchar(255) DEFAULT NULL,
	`balance` int(11) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `account` (`name`, `balance`) VALUES ('lilei','450');
INSERT INTO `account` (`name`, `balance`) VALUES ('hanmei','16000');
INSERT INTO `account` (`name`, `balance`) VALUES ('lucy','2400');

Session_1 开启事务更新不提交
Session_2 更新同一条记录会阻塞（更新不同记录不会阻塞）

1. read uncommitted 读未提交【脏读】

会产生脏读的问题
客户端A 查询到了客户端B，更新了但未提交的脏数据

1. 客户端A—开启事务 > 查询所有

# 客户端A

# 1. 设置 当前事务隔离级别 为 `read uncommitted`读未提交
#set tx_isolation='read-uncommitted';
set session transaction isolation level read uncommitted;

# 2. 开启事务
start transaction;

# 3. 查询表 `account` 的初始值
select * from account;

# 4. 客户端A 的事务未关闭

初始值

2. 客户端B—开启事务 > 更新数据1 > 查询所有

#客户端B

# 1. 设置 当前事务隔离级别 为 `read uncommitted`读未提交
set session transaction isolation level read uncommitted;

# 2. 开启事务
start transaction;

# 3. 更新 id = 1 账户的余额减 50
update account set balance = balance - 50 where id = 1;

# 4. 查询表 `account` 的更新值
select * from account;

# 5. 客户端 B 的事务未提交

更新值

3. 客户端A—查询所有 > 产生脏读

# 客户端A

# 5. 查询表 `account` 的更新值
select * from account;
# 会产生 脏读 的问题
# 客户端A 查询到了 客户端B 更新了但未提交的脏数据

这时，虽然客户端B 的事务还未提交
但是，客户端A 也可以查询到客户端B 已经更新的数据了

4. 客户端B—事务回滚 > 查询所有

# 客户端B

# 6. 事务回滚
rollback;

# 7. 查询表 `account` 的初始值
select * from account;

一旦客户端B 的事务因为某种原因回滚，所有的操作都将会被撤销
那么客户端A 查询到的数据，其实就是脏数据

5. 客户端A—更新数据1 > 查询所有

# 客户端A

# 6. 更新 id = 1 账户的余额减 50
update account set balance = balance - 50 where id = 1;

# 7. 查询表 `account` 的更新值
select * from account;

在客户端A 执行更新语句，lilei 的 balance 没有变成 350，居然是 400
产生了数据不一致问题

在应用程序中，我们会用 400 - 50 = 350，并不知道其他会话回滚了
要解决这个问题可以采用 读已提交 的隔离级别

# 一、客户端 A
# 1. 设置当前事务隔离级别为`read uncommitted`（读未提交）
# set tx_isolation='read-uncommitted';
set session transaction isolation level read uncommitted;
# 2. 开启事务
start transaction;
# 3. 查询表 `account` 的初始值
select * from account;
# 4. 客户端 A 的事务未关闭

# 三、客户端 A
# 5. 查询表 `account` 的更新值
select * from account;
# 产生了脏读的问题（查询到客户端 B 更新未提交的脏数据）

# 五、客户端 A
# 6. 更新 id = 1 账户的余额减 50
update account set balance = balance - 50 where id = 1;
# 7. 查询表 `account` 的更新值
select * from account;

# 二、客户端 B
# 1. 设置当前事务隔离级别为`read uncommitted`（读未提交）
set session transaction isolation level read uncommitted;
# 2. 开启事务
start transaction;
# 3. 更新 id = 1 账户的余额减 50
update account set balance = balance - 50 where id = 1;
# 4. 查询表 `account` 的更新值
select * from account;
# 5. 客户端 B 的事务未提交

# 四、客户端 B
# 6. 事务回滚
rollback;
# 7. 查询表 `account` 的初始值
select * from account;

3.2 读已提交（`read committed`）不可重复读

可以解决了 脏读 的问题。
会产生 不可重复读 的问题。
客户端B 更新的提交前后，客户端A 查询结果不一致。

一、客户端 A。

# 一、客户端 A

# 1. 设置当前事务隔离级别为`read committed`（读已提交）
set session transaction isolation level read committed;

# 2. 开启事务
start transaction;

# 3. 查询表 `account` 的初始值
select * from account;

# 4. 客户端 A 事务未关闭

初始值。

二、客户端 B。

# 二、客户端 B

# 1. 设置当前事务隔离级别为`read committed`（读已提交）
set session transaction isolation level read committed;

# 2. 开启事务
start transaction;

# 3. 更新 id = 1 账户的余额减 50
update account set balance = balance - 50 where id = 1;

# 4. 查询表 `account` 的更新值
select * from account;

# 5. 客户端 B 事务未提交

更新值。

三、客户端 A。

# 三、客户端 A

# 5. 查询表 `account` 的更新值
select * from account;
# `读已提交`解决了`脏读`的问题

这时，客户端B 的事务还未提交，客户端A 不能查询到客户端B 已经更新的数据。
解决了脏读。

四、客户端 B 的事务提交。

# 四、客户端 B

# 6. 提交事务
commit;

五、客户端 A。

# 五、客户端 A 

# 6. 查询表 `account` 的更新值
select * from account;
# 会产生`不可重复读`的问题（客户端 B 更新过的提交前后，客户端 A 查询结果不一致）

执行与上一步相同的查询，结果与上一步不一致。
即产生了不可重复读的问题。

# 一、客户端 A
# 1. 设置当前事务隔离级别为`read committed`（读已提交）
set session transaction isolation level read committed;
# 2. 开启事务
start transaction;
# 3. 查询表 `account` 的初始值
select * from account;
# 4. 客户端 A 事务未关闭

# 三、客户端 A
# 5. 查询表 `account` 的更新值
select * from account;
# `读已提交`解决了`脏读`的问题

# 五、客户端 A
# 6. 查询表 `account` 的更新值
select * from account;
# 会产生`不可重复读`的问题（客户端 B 更新过的提交前后，客户端 A 查询结果不一致）

# 二、客户端 B
# 1. 设置当前事务隔离级别为`read committed`（读已提交）
set session transaction isolation level read committed;
# 2. 开启事务
start transaction;
# 3. 更新 id = 1 账户的余额减 50
update account set balance = balance - 50 where id = 1;
# 4. 查询表 `account` 的更新值
select * from account;
# 5. 客户端 B 事务未关闭

# 四、客户端 B
# 6. 提交事务
commit;

3. REPEATABLE-READ 可重复读【幻读】【使用了 MVCC`】

可以解决不可重复读的问题
会产生幻读的问题（能查到客户端B 新增的数据）

1. 客户端A—开启事务 > 查询所有

# 客户端A

# 1. 设置 当前事务隔离级别 为 `repeatable read`可重复读
set session transaction isolation level repeatable read;

# 2. 开启事务
start transaction;

# 3. 查询表 `account` 的初始值
select * from account;

# 4. 客户端A 事务未关闭

初始值

2. 客户端B—开启事务 > 更新数据1 > 查询所有 > 提交事务

# 客户端B

# 1. 设置 当前事务隔离级别 为 `repeatable read`可重复读
set session transaction isolation level repeatable read;

# 2. 开启事务
start transaction;

# 3. 更新 id = 1 账户的余额减 50
update account set balance = balance - 50 where id = 1;

# 4. 查询表 `account` 的更新值
select * from account;

# 5. 提交事务
commit;

在客户端A 的事务提交之前，打开另一个客户端B，更新表 account 并提交

3. 客户端A—查询所有

# 客户端A

# 5. 查询表 `account` 的更新值
select * from account;
# `可重复读`解决了`不可重复读`的问题（与`步骤一`查询结果一致）

在客户端A 查询表 account 的所有记录，与步骤一查询结果一致
解决了 不可重复读 的问题

4. 客户端A—更新数据1 > 查询所有

# 客户端A

# 6. 更新 id = 1 账户的余额减 50
update account set balance = balance - 50 where id = 1;

# 7. 查询表 `account` 的更新值
select * from account;
# 数据的 一致性 没有被破坏（是在 步骤二 更新基础上做的更新）

执行 lilei 的 balance 减 50，结果没有变成 450 - 50 = 400
而是 lilei 的 balance 值用的是用步骤二中更新结果 400 - 50 = 350

数据的 一致性 没有被破坏

可重复读的隔离级别，使用了 MVCC（multi-version concurrency control）机制

Select 操作不会更新版本号，是读取快照（历史版本）
Insert、Update 和 Delete 会更新版本号，是读取最新（当前版本）

5. 客户端B—开启事务 > 插入数据 > 查询所有 > 提交事务

# 客户端B

# 6. 开启事务
begin;

# 7. 插入一条数据
insert into account values (4, 'qs', 700);

# 8. 查询表 `account` 的最新数据
select * from account;

# 9. 提交事务
commit;

最新数据

6. 客户端A—查询所有

# 六、客户端A

# 8. 查询表 `account` 的最新数据
select * from account;
# 没有出现 幻读 的问题（没有查出新增数据）

在客户端A 查询表 account 的所有记录，没有查出新增数据
所以没有出现幻读

7. 客户端A—更新数据4 > 查询所有

# 七、客户端A

# 9. 更新 id = 4 账户的余额为 888
update account set balance = 888 where id = 4;

# 10. 查询表 `account` 的最新数据
select * from account;
# 会产生 幻读 的问题（能查到 客户端B 新增的数据）

在客户端A 执行能更新成功，再次查询能查到客户端B 新增的数据
验证了幻读

# 一、客户端 A
# 1. 设置当前事务隔离级别为`repeatable read`（可重复读）
set session transaction isolation level repeatable read;
# 2. 开启事务
start transaction;
# 3. 查询表 `account` 的初始值
select * from account;
# 4. 客户端 A 事务未关闭

# 三、客户端 A
# 5. 查询表 `account` 的更新值
select * from account;
# `可重复读`解决了`不可重复读`的问题（与`步骤一`查询结果一致）

# 四、客户端 A
# 6. 更新 id = 1 账户的余额减 50
update account set balance = balance + 50 where id = 1;
# 7. 查询表 `account` 的更新值
select * from account;
# 数据的`一致性`没有被破坏（是在`步骤二`更新基础上做的更新）

# 六、客户端 A
# 8. 查询表 `account` 的最新数据
select * from account;
# 没有出现`幻读`的问题（没有查出新增数据）

# 七、客户端 A
# 9. 更新 id = 4 账户的余额为 888
update account set balance = 888 where id = 4;
# 10. 查询表 `account` 的最新数据
select * from account;
# 会产生`幻读`的问题（能查到客户端 B 新增的数据）

# 二、客户端 B
# 1. 设置当前事务隔离级别为`repeatable read`（可重复读）
set session transaction isolation level repeatable read;
# 2. 开启事务
start transaction;
# 3. 更新 id = 1 账户的余额减 50
update account set balance = balance - 50 where id = 1;
# 4. 查询表 `account` 的更新值
select * from account;
# 5. 提交事务
commit;

# 五、客户端 B
# 6. 开启事务
begin;
# 7. 插入一条数据
insert into account values (4, 'qs', 700);
# 8. 查询表 `account` 的最新数据
select * from account;
# 9. 提交事务
commit;

3.4 串行化（`serializable`）

一、客户端 A。

# 一、客户端 A

# 1. 设置当前事务隔离级别为`serializable`（串行化）
set session transaction isolation level serializable;

# 2. 开启事务
start transaction;

# 3. 查询表 `account` 的初始值
select * from account;

# 4. 客户端 A 事务未关闭

初始值。

二、客户端 B。

# 二、客户端 B

# 1. 设置当前事务隔离界别为`serializable`（串行化）
set session transaction isolation level serializable;

# 2. 开启事务
start transaction;

# 3. 插入一条记录报错
insert into account values(5, 'qs', 0);

#  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
# 表被锁了，插入失败

打开一个客户端B，并设置当前事务隔离界别为 serializable（串行化）。

插入一条记录报错，因为表被锁了插入失败。
MySQL 事务隔离级别为 serializable 时会锁表。

因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到。

4. 行锁其他

MySQL 默认隔离级别是：可重复读（repeatable-read）。

使用间隙锁可以 解决幻读 问题。
间隙锁，在某些情况下可以 解决幻读 问题。

要避免幻读可以使用间隙锁，在 Session_1 执行下面 SQL：
update account set name = 'qs' where id > 10 and id <= 20;
则其他 Session 没法在这个范围所包含的间隙里 ~~插入或修改~~ 任何数据。

无索引情况，行锁 会升级为 表锁。

锁主要是加在索引上，并非直接锁掉这行记录。
如果对非索引字段更新，行锁可能会变表锁。
如果一条 SQL 用到了主键索引（MySQL 主键自带索引）MySQL 会锁住主键索引。
如果一条 SQL 操作了非主键索引，MySQL 会先锁住非主键索引，再锁定主键索引。
Session_1 执行下面 SQL 后，Session_2 对该表任一行操作都会阻塞住。
update account set balance = 800 where name = 'lilei';
InnoDB 的行锁，是针对索引加的锁，不是针对记录加的锁。
并且该索引不能失效，否则都会从 ~~行锁升级为表锁~~。

锁定某一行还可以用 lock in share mode（共享锁）和 for update（排它锁）。

在 Session_1 执行下面 SQL 后。
select * from test_innodb_lock where a = 2 for update;。
其他 Session 只能读这行数据，修改则会被阻塞，直到锁定行的 Session 提交。

5. 行锁总结

InnoDB 存储引擎，实现了 行级锁定。

虽然在锁定机制的实现方面，所带来的性能损耗可能比表级锁定会要更高一些。
但是在整体并发处理能力方面，要远远优于 MyISAM 的表级锁定的。
当系统并发量高的时候，InnoDB 的整体性能和 MyISAM 相比，就会有比较明显的优势了。
但是 InnoDB 的行级锁定也有其脆弱的一面。
当使用不当的时候，可能会让 InnoDB 的整体性能比 MyISAM 更差。

6. 行锁分析

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

-- 行锁分析
show status like'innodb_row_lock%';

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：从系统启动到现在，总共等待的次数（等待总次数）。

注意：当 等待次数很高，并且 每次等待时长很长 的时候。
就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

五、多版本并发控制（`MVCC`）

多版本并发控制（multi-version concurrency control）机制。

Select 操作不会更新版本号，是读取快照（历史版本）。
Insert、Update 和 Delete 会更新版本号，是读取最新（当前版本）。

# 客户端 A
select * from account;
# 事务Id = 12

# 客户端 B
update account set balance = balance - 50 where id = 1;
select * from account;

# 客户端 A
select * from account;
update account set balance = balance - 50 where id = 1;
select * from account;

id	name	balance	创建事务Id	删除事务Id
1	lilei	450	10	13
2	hanmei	16000	11	空
2	han666	16000	13	空

对于事务Id 为 12 的操作。

先查询 select * from account。
创建了查询快照，记录执行 SQL 这一刻最大的已提交事务 Id（快照点已提交最大事务 Id）。

对于事务Id 为 13 的操作。

先删除 id = 1 的记录，然后更新 id = 2 的记录，再提交。
对于删除操作，MySQL 底层会记录好被删除的数据行的删除事务Id。
对于更新操作，MySQL 底层会新增一行相同数据并记录好对应的创建事务Id。

在事务Id 为 12 的事务里执行查询操作。

MySQL 底层会带上过滤条件。
select * from account where '创建事务Id' <= 12 and '删除事务Id' > 12;
创建事务 Id <= max（当前事务 Id = 12，快照点已提交最大事务 Id）。
删除事务 Id > max（当前事务 Id = 12，快照点已提交最大事务 Id）。

注意：开启事务 start transaction 命令，并不是一个事务的起点。

开启事务之后，在执行到的第一个操作 InnoDB 表的语句时，事务才真正启动，才会向 MySQL 申请事务Id。
MySQL 内部是严格 按照事务的启动顺序，来分配事务Id 的。

六、死锁

MySQL 有两种锁：

排它锁（X锁）是互斥的
共享锁（S锁）是不互斥的

死锁的原理：

当事务T1 对数据A 加上 X 锁时
只允许事务T1 读取和修改数据A
不允许其他事务读取和修改数据A
当事务T2 对数据A 加上 S 锁时
其他事务只能再对数据A 加 S 锁，而不能加 X 锁
直到事务T2 释放数据A 上的 S 锁

一般造成死锁的原因：

因为两个事物添加锁的时候，没能及时的解锁释放资源
等到第二个事务要添加锁的时候，发现已经被锁
从而造成环路等待，构成死锁条件

大多数情况 MySQL 可以自动检测死锁，并回滚产生死锁的那个事务
但是有些情况 MySQL 没法自动检测死锁

set tx_isolation='repeatable-read';

# Session_1 执行
select * from account where id=1 for update;
# Session_2 执行
select * from account where id=2 for update;

# Session_1 执行
select * from account where id=2 for update;
# Session_2 执行
select * from account where id=1 for update;

七、死锁案例

MySQL 死锁

1. Session_1 执行

# Session_1 执行
-- 开启事务
start transaction;

-- 子表插入一条记录
insert into dam_file ( file_id, mat_id, page_no, original_name, optimized_name, original_size, create_time )
values ( REPLACE(UUID(),'-',''), 'cefdcc4d79a949eaaec9715e572aaf82', 1, 'test_1.PDF', 'test_1.PDF', 1, NOW() );
-- 1. 排它锁，锁定新插入的子表主键索引。

-- 根据子表记录数，修改主表
update dam_material SET total_page = (SELECT COUNT(1) FROM dam_file WHERE mat_id = 'cefdcc4d79a949eaaec9715e572aaf82')
where mat_id = 'cefdcc4d79a949eaaec9715e572aaf82';
-- 2. 共享锁，锁定关联的`n`个子表主键索引。

2. Session_2 执行

# Session_2 执行
-- 开启事务
start transaction;

-- 子表插入一条记录
insert into dam_file ( file_id, mat_id, page_no, original_name, optimized_name, original_size, create_time )
values ( REPLACE(UUID(),'-',''), '1645b1893e364575ac25eb45e5bcf497', 1, 'test_2.PDF', 'test_2.PDF', 2, NOW() );

-- 根据子表记录数，修改主表
update dam_material SET total_page = (SELECT COUNT(1) FROM dam_file WHERE mat_id = '1645b1893e364575ac25eb45e5bcf497')
where mat_id = '1645b1893e364575ac25eb45e5bcf497';

产生死锁：

[2022-04-13 11:33:43] [40001][1205] Lock wait timeout exceeded; try restarting transaction。
Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction。

3. 问题原因

update dam_material SET total_page = (SELECT COUNT(1) FROM dam_file WHERE mat_id = 'cefdcc4d79a949eaaec9715e572aaf82')
where mat_id = 'cefdcc4d79a949eaaec9715e572aaf82';

-- 查看表上加过的锁（所有库）
show open tables where in_use > 0;


-- 行锁分析 21
show status like'innodb_row_lock%';

-- 查询近期死锁日志信息
show engine innodb status;
-- 2022-04-13 03:31:27


-- 查询当前数据库运行的所有事务
select * from information_schema.INNODB_TRX; 
-- 2022-04-13 03:18:53
-- 查询当前数据库出现的锁
SELECT * FROM information_schema.INNODB_LOCKs;
-- 查询锁等待的对应关系
SELECT * FROM information_schema.INNODB_LOCK_waits;
-- 连表查询出所有需要杀掉的进程
select concat('KILL ',id,';') from information_schema.processlist p inner
 join information_schema.INNODB_TRX x on p.id=x.trx_mysql_thread_id where db='dam';
-- 查看数据库当前的进程
show  processlist;


ps aux | grep 5573

KILL 5572;
KILL 5579;

Session_1 中执行 Insert语句 加 X 锁被锁定。

我的事务1 中 Update wc_examine 会多很多个 Update，这里有 N 行记录被锁定，事务的更新大量数据时间比较长，更新会加 X 锁。
而此时事务2 是 UPDATE wc_examine，在 Update 之前先执行了 Select 操作，添加了 S 锁，然后想要 Update 的时候添加 X 锁。

事务1 的 X 锁正准备加上还是还没加上，实际是存在 X 锁，但是事务2 加了 S 锁。
事务1 会等待事务2 的 S 锁，事务2 的完整事务加了 S 锁立即就要加 X 锁，但是事务1 的 X 锁没有释放。

造成了环路等待。

4. 分析死锁

-- 查询 MySQL 事务处理表
select * from information_schema.INNODB_TRX;

MySQL 事务处理表

-- 查询近期死锁日志信息
show engine innodb status;

近期死锁日志信息

死锁日志信息


=====================================
2022-04-13 03:41:51 0x7f3a8407f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 640460 srv_active, 0 srv_shutdown, 8866166 srv_idle
srv_master_thread log flush and writes: 9506626
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1379055
OS WAIT ARRAY INFO: signal count 1378858
RW-shared spins 0, rounds 2727939, OS waits 1362501
RW-excl spins 0, rounds 18498, OS waits 392
RW-sx spins 270, rounds 3123, OS waits 34
Spin rounds per wait: 2727939.00 RW-shared, 18498.00 RW-excl, 11.57 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-04-13 03:01:26 0x7f3a5df35700
*** (1) TRANSACTION:
TRANSACTION 1295884, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 19 lock struct(s), heap size 1136, 574 row lock(s), undo log entries 1
MySQL thread id 5540, OS thread handle 139888832341760, query id 9387271 121.228.199.72 root Sending data
update dam_material
         SET total_page = (SELECT COUNT(1) FROM dam_file WHERE mat_id = 'cefdcc4d79a949eaaec9715e572aaf82') 
        where mat_id = 'cefdcc4d79a949eaaec9715e572aaf82'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 31 page no 16 n bits 120 index PRIMARY of table `dam`.`dam_file` trx id 1295884 lock mode S waiting
Record lock, heap no 44 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 30; hex 646538643031613866396365346465383834643763343635373133323163; asc de8d01a8f9ce4de884d7c46571321c; (total 32 bytes);
 1: len 6; hex 00000013c611; asc       ;;
 2: len 7; hex b60000012a0110; asc     *  ;;
 3: len 30; hex 313634356231383933653336343537356163323565623435653562636634; asc 1645b1893e364575ac25eb45e5bcf4; (total 32 bytes);
 4: len 4; hex 80000002; asc     ;;
 5: len 30; hex 2f64616d5f70726f66696c652f3331303130343139333531323032313231; asc /dam_profile/31010419351202121; (total 103 bytes);
 6: len 30; hex 535543434553532d3331303130343139333531323032313231372d302831; asc SUCCESS-310104193512021217-0(1; (total 48 bytes);
 7: len 4; hex 8004d7cf; asc     ;;
 8: SQL NULL;
 9: len 5; hex 99ac9ab05a; asc     Z;;

*** (2) TRANSACTION:
TRANSACTION 1295889, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
9 lock struct(s), heap size 1136, 173 row lock(s), undo log entries 1
MySQL thread id 5541, OS thread handle 139888661059328, query id 9387272 121.228.199.72 root Sending data
update dam_material
         SET total_page = (SELECT COUNT(1) FROM dam_file WHERE mat_id = '1645b1893e364575ac25eb45e5bcf497') 
        where mat_id = '1645b1893e364575ac25eb45e5bcf497'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 31 page no 16 n bits 120 index PRIMARY of table `dam`.`dam_file` trx id 1295889 lock_mode X locks rec but not gap
Record lock, heap no 44 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 30; hex 646538643031613866396365346465383834643763343635373133323163; asc de8d01a8f9ce4de884d7c46571321c; (total 32 bytes);
 1: len 6; hex 00000013c611; asc       ;;
 2: len 7; hex b60000012a0110; asc     *  ;;
 3: len 30; hex 313634356231383933653336343537356163323565623435653562636634; asc 1645b1893e364575ac25eb45e5bcf4; (total 32 bytes);
 4: len 4; hex 80000002; asc     ;;
 5: len 30; hex 2f64616d5f70726f66696c652f3331303130343139333531323032313231; asc /dam_profile/31010419351202121; (total 103 bytes);
 6: len 30; hex 535543434553532d3331303130343139333531323032313231372d302831; asc SUCCESS-310104193512021217-0(1; (total 48 bytes);
 7: len 4; hex 8004d7cf; asc     ;;
 8: SQL NULL;
 9: len 5; hex 99ac9ab05a; asc     Z;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 31 page no 8 n bits 112 index PRIMARY of table `dam`.`dam_file` trx id 1295889 lock mode S waiting
Record lock, heap no 35 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 30; hex 336238653631343265386661343262356237376132386632373834366130; asc 3b8e6142e8fa42b5b77a28f27846a0; (total 32 bytes);
 1: len 6; hex 00000013c60c; asc       ;;
 2: len 7; hex b3000001270110; asc     '  ;;
 3: len 30; hex 636566646363346437396139343965616165633937313565353732616166; asc cefdcc4d79a949eaaec9715e572aaf; (total 32 bytes);
 4: len 4; hex 80000002; asc     ;;
 5: len 30; hex 2f64616d5f70726f66696c652f3331303130343139333531323032313231; asc /dam_profile/31010419351202121; (total 103 bytes);
 6: len 30; hex 535543434553532d3331303130343139333531323032313231372d302831; asc SUCCESS-310104193512021217-0(1; (total 48 bytes);
 7: len 4; hex 8004d7cf; asc     ;;
 8: SQL NULL;
 9: len 5; hex 99ac9ab05a; asc     Z;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 1297398
Purge done for trx's n:o < 1297396 undo n:o < 0 state: running but idle
History list length 7
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421364308164240, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421364308177120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421364308176200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421364308163320, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421364308175280, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421364308174360, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421364308173440, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421364308172520, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421364308171600, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421364308168840, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421364308167920, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421364308167000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421364308166080, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421364308162400, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1297325, ACTIVE 539 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5560, OS thread handle 139888831530752, query id 9390328 121.228.199.72 root
---TRANSACTION 1297322, ACTIVE 554 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5559, OS thread handle 139888834504448, query id 9390311 121.228.199.72 root
---TRANSACTION 1297209, ACTIVE 1378 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5544, OS thread handle 139888832341760, query id 9389164 121.228.199.72 root
---TRANSACTION 1297204, ACTIVE 1396 sec
5 lock struct(s), heap size 1136, 67 row lock(s), undo log entries 2
MySQL thread id 5545, OS thread handle 139888966031104, query id 9389014 121.228.199.72 root
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
633 OS file reads, 7459605 OS file writes, 5524267 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.79 writes/s, 0.71 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 19 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
0.00 hash searches/s, 1.00 non-hash searches/s
---
LOG
---
Log sequence number 309828405
Log flushed up to   309828405
Pages flushed up to 309828125
Last checkpoint at  309828116
0 pending log flushes, 0 pending chkp writes
3451411 log i/o's done, 0.50 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 451404
Buffer pool size   8192
Free buffers       5710
Database pages     2454
Old database pages 885
Modified db pages  3
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 546, created 1908, written 3291522
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2454, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=139888888375040, state: sleeping
Number of rows inserted 179861, updated 657152, deleted 4566, read 76058212
0.00 inserts/s, 0.07 updates/s, 0.00 deletes/s, 0.14 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

七、乐观锁和悲观锁

数据库中对于一次的数据修改，大概将其分为三步：

获取数据
修改数据
提交修改

假设 A、B 两个用户对数据进行修改。

1. 乐观锁

乐观锁对数据保持一个乐观态度
A 获取数据、修改数据时，都不会对数据加锁
只有在提交修改时，才会判断中间是否有其他人对数据进行了修改

因为没有对数据加锁，B 可在 A 获取数据后，也获取到数据，甚至比 A 提前提交数据修改

实现就是给数据添加版本号字段
例如 A 在获取数据阶段，获取到的版本号为 1，则在提交修改阶段的执行语句就为：

update 表名 
SET version = version + 1 
where version = 1

这样当数据在中途被 B 修改后，version 字段将不为原来的 1，此条更新语句也就失效了。不会导致数据更新异常。

2. 悲观锁

悲观锁对数据保持一个悲观态度
则在 A 获取数据后，就对数据进行了加锁
直到修改数据、提交修改后才将锁释放

中途由于数据已经被加锁，B 压根读不数据，也就不存在中途被修改的情况。

select … for update

使用的 select 语句只是查询，不涉及修改，故不存在加锁的概念
而 select * for update 从字面意思就可以知道，该语句不单单是查询
而是为了后续的修改，所以是要对数据加锁的，并且是悲观锁
此时按照 select 中where 条件字段，又可分为 行锁和表锁

3. select … for update 行锁

A

-- empno 主键
begin;
	SELECT * FROM employee WHERE empno = '111' FOR UPDATE;

ROLLBACK;

B

begin;
	SELECT * FROM employee WHERE empno = '111' FOR UPDATE;

ROLLBACK;

4. select … for update 表锁

A

-- dname 非索引
begin;
	SELECT * FROM employee WHERE dname = '销售部' FOR UPDATE;

ROLLBACK;

B

begin;
	SELECT * FROM employee WHERE dname = '技术部' FOR UPDATE;

ROLLBACK;

5. 行锁和表锁

如果查询条件用了索引/主键，那么 select … for update 就会进行行锁。
如果是普通字段(没有索引/主键)，那么 select … for update 就会进行锁表。

通过 set @@autocommit=0; 设置为手动提交

0 代表手动提交
1 代表自动提交

SELECT @@autocommit;
-- 1 代表自动提交

set @@autocommit=0;

SELECT @@autocommit;
-- 0 代表手动提交

八、优化

尽可能让所有数据检索，都通过索引来完成，避免无索引行锁升级为表锁。
合理设计索引，尽量 缩小锁的范围。
尽可能减少检索条件范围，避免间隙锁。
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的 SQL 尽量放在事务最后执行。
尽可能低事务隔离级别。

骑士梦

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
八、MySQL 事务与锁

一、MySQL 事务二、MySQL 表锁三、MySQL 页面锁四、MySQL 行锁五、MySQL 间隙锁六、MySQL 死锁
复制链接

扫一扫

专栏目录

八、MySQL 事务与锁

一、InnoDB 支持事务

二、Transaction 事务

1. 并发事务 带来的问题

2. 事务隔离级别

3. 设置事务隔离级别（默认 REPEATABLE-READ 可重复读）

三、锁定义

四、锁分类

1. 表锁

2. 读写锁

3. 行锁分类

1. read uncommitted 读未提交【脏读】

1. 客户端A—开启事务 > 查询所有

2. 客户端B—开启事务 > 更新数据1 > 查询所有

3. 客户端A—查询所有 > 产生脏读

4. 客户端B—事务回滚 > 查询所有

5. 客户端A—更新数据1 > 查询所有

3.2 读已提交（read committed）不可重复读

3. REPEATABLE-READ 可重复读【幻读】【使用了 MVCC`】

1. 客户端A—开启事务 > 查询所有

2. 客户端B—开启事务 > 更新数据1 > 查询所有 > 提交事务

3. 客户端A—查询所有

4. 客户端A—更新数据1 > 查询所有

5. 客户端B—开启事务 > 插入数据 > 查询所有 > 提交事务

6. 客户端A—查询所有

7. 客户端A—更新数据4 > 查询所有

3.4 串行化（serializable）

4. 行锁其他

5. 行锁总结

6. 行锁分析

五、多版本并发控制（MVCC）

六、死锁

七、死锁案例

1. Session_1 执行

2. Session_2 执行

3. 问题原因

4. 分析死锁

七、乐观锁 和 悲观锁

1. 乐观锁

2. 悲观锁

3. select … for update 行锁

4. select … for update 表锁

5. 行锁 和 表锁

八、优化

1. 并发事务带来的问题

3. 设置事务隔离级别（默认 `REPEATABLE-READ` 可重复读）

3.2 读已提交（`read committed`）不可重复读

3.4 串行化（`serializable`）

五、多版本并发控制（`MVCC`）

七、乐观锁和悲观锁

5. 行锁和表锁