详解Mysql事务隔离级别与锁机制

张三博客

已于 2023-03-30 19:21:36 修改

阅读量1.6k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： mysql优化实战文章标签： mysql 数据库 java

于 2023-03-28 20:01:44 首次发布

本文链接：https://blog.csdn.net/qq_43565087/article/details/129823025

mysql优化实战专栏收录该内容

5 篇文章

订阅专栏

文章详细阐述了Mysql中的事务ACID属性，包括原子性、一致性、隔离性和持久性，并探讨了并发事务处理中可能出现的问题，如脏写、脏读、不可重复读和幻读。接着介绍了四种事务隔离级别以及它们的特点和应用场景。此外，还详细讲解了Mysql的锁机制，包括表锁、行锁、间隙锁和临键锁，以及如何分析和管理锁，以及如何处理和优化常见的锁问题，如无索引行锁升级为表锁和死锁情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 事务及其ACID属性

1.1 ACID属性

1.1.1 原子性（Atomicity）

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

1.1.2 一致性（Consistent）

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

1.1.3 隔离性（Isolation）

数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独
立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然

1.1.4 持久性（Durable）

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

1.2 并发事务处理带来的问题

1.2.1 脏写或更新丢失

当两个或者多个事务选择操作同一条数据，然后基于最初的值进行更新该数据，由于每个事务都不知道其他事务的存在，就会发生数据更新丢失的问题，最后更新的事务覆盖了其他事务所做的更新

举例：两个事务同时对库存进行操作假定该库存初始值为20 A事务将库存更新为15 而B事务则将库存更新为10 就出现了脏写的问题

1.2.2 脏读

事务A读取到了事务B已经修改但尚未提交的数据，并且在这个数据上进行了操作，此时如果B事务回滚，A事务读取到的数据就是无效，不符合一致性要求

1.2.3 不可重复读

事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合事务的隔离性

1.2.4 幻读

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

1.3 事务隔离级别

隔离级别	脏读	不可重复读	幻读
读未提交	Y	Y	Y
读已提交	N	Y	Y
可重复读	N	N	Y
可串行化	N	N	N

1.3.1 读未提交

set tx_isolation='read-committed';

客户端A执行的修改操作并没有提交
客户端B查询到客户端A没有提交的数据

客户端A将数据rollback 就会出现脏读的情况

1.3.2 读已提交

 set tx_isolation='read-committed';

开启事务修改一条数据在事务A中可以看到数据已经发生变化
事务B中数据并没有发生改变（解决了脏读的问题）

但读已提交任没有解决可重复读的问题不满足隔离性

1.3.3 可重复读

客户端A 修改id为1的数据并进行提交
客户端B中任是之前已经读取到的数据
此时库中的值已经发生了改变。（解决了脏读和可重复的问题）

1.3.4 可串行化

串行化的操作都会进行加锁。效率低

set tx_isolation='serializable';

在可串行化中开启事务进行查询
事务B进行更新操作就会进行等待（解决了脏读、可重复读和幻读但效率太低）

2. 锁详解

2.1 表锁

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

手动增加表锁
```
LOCK table 表名 read(write)
```
查看表上加过的锁
```
SHOW open tables;
```
删除表锁
```
unlock tables;
```

2.1.1 表读(写)锁

读锁：当前session和其他session都可以读该表，当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待
写锁：当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞

LOCK table employees read

对表进行加锁
执行写入操作
查看是否加锁成功

2.2 行锁

每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

手动begin开启事务
客户端2更新一条id为2的数据可以更新成功
更新id为1的数据则需要进行等待
当客户端commit 事务之后客户端2 才可以对ID为1的数据进行更改

2.3 间隙锁

间隙锁，锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read

事务A修改id 1-10的数据（事务A 为id 1-10建立了间隙锁）
事务B插入一条id=9 的数据（事务B无法进行插入）

在这里插入图片描述
间隙为：(5,15)，（15,正无穷）

2.4 临键锁

是行锁与间隙锁的组合
在这里插入图片描述
此例子中(5,15]的整个区间可以叫做临键锁

2.5 常用操作

2.5.1 行锁分析

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

2.5.2 查看INFORMATION_SCHEMA系统库锁相关数据表

2.5.2.1 查看事务

select * from INFORMATION_SCHEMA.INNODB_TRX;

在这里插入图片描述

2.5.2.2 查看锁

select * from INFORMATION_SCHEMA.INNODB_LOCKS;

在这里插入图片描述

2.5.2.3 查看锁等待

select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

在这里插入图片描述

2.5.2.4 查看锁等待详细信息

show engine innodb status\G;

2.5.2.5 释放锁

select * from INFORMATION_SCHEMA.INNODB_TRX;

kill trx_mysql_thread_id

在这里插入图片描述

2.6 常见问题

2.6.1 无索引行锁升级为表锁

锁主要是加在索引上，对非索引字段进行更新，行锁可能变为表锁
- 事务A执行：UPDATE employees SET age=20 WHERE name=‘zhangsanNB’;
- 事务B执行employees表中的任何操作都会被阻塞
InnoDB存储引擎的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则就会从行锁升级为表锁。

2.6.2 死锁

set tx_isolation=‘repeatable-read’;
事务A执行：select * from employees where id=1 for update;
事务B执行：select * from employees where id=2 for update;
事务A执行：select * from employees where id=2 for update;
事务B执行：select * from employees where id=1 for update;