使我郁郁寡欢的 MySQL 事务和锁

一时失言乱红尘 2022-11-18 01:37 89阅读 0赞

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70][]

# 一 事务的ACID 特性 #

事务是一组 SQL 语句，它们是一个执行单位。

## 1 原子性（Atomicity）与 持久性（Durability） ##

原子性：单个事务的 SQL 要么全都执行成功，要么全部执行失败。 事务的原子性通过 Undo Log 日志实现。

持久性：一个事务一旦提交，它对数据库中数据的改变是永久性的。事务的持久性通过 Redo Log 日志实现。

### **1.1 Undo Log**  ###

> Undo ：意为撤销或取消，以撤销操作为目的，返回某个指定的状态。
> 
> Undo Log ：数据库事务开始之前，会将要修改的记录存放到  Undo  日志里，当事务回滚时或者数据库崩溃时，可以利用 Undo  日志，撤销未提交事务对数据库产生的影响。
> 
> Undo Log  产生和销毁： Undo Log  在事务开始前产生；事务在提交后，并不会立刻删除 U ndo Log，I nnodb  会将该事务对应的 U ndo Log  放入到删除列表中，后面会通过后台线程P urge Thread  进行回收处理。Undo Log  属于逻辑日志，记录一个变化过程。例如执行一个  delete ，Undo Log 会记录一个 insert ；执行一个  update ，Undo Log 会记录一个相反的 update 。

### **1.2 Redo Log** ###

> Redo：顾名思义就是重做。以恢复操作为目的，在数据库发生意外时重现操作。 
> 
> Redo Log：事务中修改的任何数据，都会将最新的值备份存储到 Redo Log。Redo Log 可以防止在发生故障的时间点，尚有脏数据未写入磁盘中，重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。
> 
> Redo Log  的生成和释放：随着事务操作的执行，就会生成  Redo Log ，在事务提交时会将产生 Redo Log 写入 Log Buffer ，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log  的使命也就完成了， Redo Log  占用的空间就可以重用（被覆盖写入）。

### 1.3 图解 ###

**（1）流程图**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 1][]

**（2）Redo Undo 写入过程**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 2][]

**（3）数据持久化到磁盘**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 3][]

通过变量 innodb\_flush\_log\_at\_trx\_commit 的值来决定。该变量有3种值：0、1、2，默认为1。这个变量只是控制 commit 动作是否刷新 Log Buffer 到磁盘

（1）0，事务提交后，每秒将 Redo buffffer 写入 OS cache， 再调用 fsync() 把 OS cache 写入磁盘；

（2）1，事务每次提交都会将 Redo buffffer 写入 OS cache， 再调用 fsync() 把 OS cache 写入磁盘；

（3）2，事务每次提交仅将 Redo buffffer 写入 OS cache，然后由后台 Master 线程每隔 1s 调用 fsync() 把 OS cache 写入磁盘。

一般建议选择取值2，因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失1秒的事务提交数据。

## 2 一致性（Consistency） ##

[mysql事务“一致性”理解][mysql]

## 3 隔离性（Isolation） ##

隔离型指事务之间不互相影响，这样事务在并发执行时会得到与串行执行一样的效果。

### 3.1 没有隔离级别可能会产生的问题 ###

**（1）脏读（数据在修改）**

某个事务所做的修改尚未提交时，其他事务就能读到这些修改。

**（2）幻读（数据在增加）**

一个事务突然读到之前未见过的行。假设某个事务刚执行完一条 SELECT 语句，接着另一个事务插入了一条新的记录，此时第一个事务再执行一条 SELECT 语句时，就有可能看到这条新增的记录。

**（3）不可重复读**

同一个事务使用同一条 SELECT 在每次读取时得到的结果都不一样。

**（4）数据覆盖**

回滚覆盖：一个事务回滚操作，把其他事务已提交的数据给覆盖了。

提交覆盖：一个事务提交操作，把其他事务已提交的数据给覆盖了。

###    3.2 隔离级别 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 4][]

**（1）READ UNCOMMITTED**

允许某个事务看到其他事务尚未提交的行修改。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 5][]

**（2）READ COMMITTED**

允许某个事务看到其他事务已经提交的行修改。

**（3）REPEATABLE READ（MySQL默认隔离级别 ）**

同一个事务使用同一条 SELECT 在每次读取时得到的结果都一样，就算中间有其他事务插入或修改行。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 6][]

**（4）SERIALIZABLE**

如果某个事务在读取某些行，那么在它完成之前，其他事务都无法对这些行进行修改。

### **3.3 MySQL隔离级别控制** ###

**3.3.1 查看事务隔离级别**

show variables like 'transaction_isolation'; 
    
    select @@transaction_isolation;

**3.3.2 设置事务隔离级别**

set transaction_isolation='READ-UNCOMMITTED'; 
    set transaction_isolation='READ-COMMITTED'; 
    set transaction_isolation='REPEATABLE-READ'; 
    set transaction_isolation='SERIALIZABLE';

# 二 锁分类 #

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

*  根据操作粒度来分：全局锁，表锁和行锁
 *  根据操作类型来分：读锁（共享锁）和写锁（排他锁）
 *  根据操作性能来分：乐观锁和悲观锁

## 1 悲观锁（Pessimistic Locking） ##

悲观锁是指在数据处理过程，将数据处于锁定状态，一般使用数据库的锁机制实现。从广义上来讲，行锁、表锁、读锁、写锁、共享锁、排他锁等，这些都属于悲观锁范畴。

### 1.1 表锁 ###

表锁每次操作都锁住整张表，并发度低。

手动添加表锁：

> lock table  表名称  read|write, 表名称 2 read|write;

查看表上过的锁：

> show open tables;

删除表锁：

> unlock tables;

表级读锁不会阻塞读操作，但会阻塞写操作。表级写锁会把读写操作都阻塞。

### 1.2 行锁 ###

InnoDB行锁是通过对索引数据页上的记录加锁实现的，主要实现算法有 3 种：Record Lock、Gap Lock 和 Next-key Lock。

> （1）RecordLock  锁：锁定单个行记录的锁。（记录锁， RC 、 RR  隔离级别都支持） 
> 
> （2）GapLock  锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁， RR 隔离级别支持） 
> 
> （3）Next-key Lock  锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁 \+ 范围锁，RR  隔离级别支持）

*  共享锁（行级锁 - 读锁）

> select ... lock in share mode

共享锁又称为读锁，简称  S  锁。共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

InnoDB  会使用  Next-Key Lock  锁进行处理，如果扫描发现唯一索引，可以降级为 RecordLock  锁。

*  排他锁（行级锁 - 写锁）

> select ... from ... for update

排他锁又称为写锁，简称 X 锁。排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能对该行记录做其他操作，也不能获取该行的锁。

Innodb 引擎默认会在 update，delete 语句加上 for update。行级锁的实现其实是依靠其对应的索引，如果查询没有使用到索引，将会锁住整个表记录。 InnoDB 会使用 Next-Key Lock 锁进行处理，如果扫描发现唯一索引，可以降级为 RecordLock 锁。

## 2 乐观锁 ##

乐观锁是相对于悲观锁而言的，它不是数据库提供的功能，需要开发者自己去实现。在数据库操作时，想法很乐观，认为这次的操作不会导致冲突，因此在数据库操作时并不做任何的特殊处理，即不加锁，而是在进行事务提交时再去判断是否有冲突了。

悲观锁和乐观锁都可以解决事务写写并发， 对并发率要 求高的选择乐观锁，对并发率要求低的选择悲观锁。

乐观锁实现方式

*  使用版本字段（version）

`version` int(11) NOT NULL DEFAULT '1' COMMENT '版本号'

先给数据表增加一个版本 (version)  字段，每操作一次，将那条记录的版本号加  1 。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 7][]

例子：下单

（1）查询商品库存信息

> select (quantity,version) from goods where id=1;

（2）修改商品库存

> update goods set quantity=quantity-1,version=version+1 where id=1 and version=\#\{version\};

*  使用时间戳（Timestamp）

与使用 version 版本字段相似，同样需要给在数据表增加一个字段，字段类型使用 timestamp 时间戳。也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则提交更新，否则就是版本冲突，取消操作。

## 3 死锁与解决方案 ##

### 3.1 表锁死锁 ###

**产生原因**

用户  A  访问表  A （锁住了表  A ），然后又访问表  B ；另一个用户  B  访问表  B （锁住了表 B ），然后企图访问表 A ；这时用户  A  由于用户  B  已经锁住表  B ，它必须等待用户  B  释放表  B  才能继续，同样用户  B  要等用户 A  释放表  A  才能继续，这就死锁就产生了。

用户 A ->  A 表（表锁）->  B 表（表锁）

用户 B ->  B 表（表锁）->  A 表（表锁）

**解决方案**

这种死锁比较常见，是由于程序的 BUG 产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个

资源，如操作 A 和 B 两张表时，总是按先 A 后 B 的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

### 3.2 行锁死锁 ###

**产生原因 1**

如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发

生阻塞或死锁。这坑我踩过，详见：[那些年踩过的 MySQL 索引的坑][MySQL]

**解决方案 1**

SQL 语句中不要使用太复杂的关联多表的查询；使用 explain 对 SQL 语句进行分析，对于有全表扫描和全表锁定的 SQL 语句，建立相应的索引进行优化。

**产生原因 2**

两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 8][]

**解决方案 2**

在同一个事务中，尽可能做到一次锁定所需要的所有资源

按照  id  对资源排序，然后按顺序进行处理

### 3.3 共享锁转为排他锁 ###

**产生原因**

事务  A  查询一条纪录，然后更新该条纪录；此时事务  B  也更新该条纪录，这时事务  B  的排他锁由于事务A  有共享锁，必须等  A  释放共享锁后才可以获取，只能排队等待。事务  A  再执行更新操作时，此处发生死锁，因为事务 A  需要排他锁来做更新操作。但是，无法授予该锁请求，因为事务  B  已经有一个排他锁请求，并且正在等待事务 A  释放其共享锁。

> 事务 A:
> 
> select \* from dept where deptno=1 lock in share mode; //  共享锁 ,1 
> 
> update dept set dname='java' where deptno=1; //  排他锁 ,3 
> 
> 事务 B:
> 
> update dept set dname='java' where deptno=1; // 2, 由于 1 有共享锁，没法获取排他锁

**解决方案**

*  对于按钮等控件，点击立刻失效，不让用户重复点击，避免引发同时对同一条记录多次操作； 
 *  使用乐观锁进行控制。乐观锁机制避免了长事务中的数据库加锁开销，大大提升了大并发量下的系统性能。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中；

### 3.4 死锁排查 ###

MySQL 提供了几个与锁有关的参数和命令，可以辅助我们优化锁操作，减少死锁发生。

*  查看死锁日志

> **show engine innodb status\\G**  命令查看近期死锁日志信息。

使用方法： 1 、查看近期死锁日志信息； 2 、使用 explain 查看下 SQL 执行计划

*  查看锁状态变量

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

如果等待次数高，而且每次等待时间长，需要分析系统中为什么会有如此多的等待，然后着手定制优化。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70]: /images/20221022/c7a7ab93d20d4198b1bb0ba471e3d480.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 1]: /images/20221022/312f4f8ad8ac465ea56a5cf6ebef9327.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 2]: /images/20221022/4af2d6d3e3524880af146b19d955d38d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 3]: /images/20221022/b7024999748742318232c7c5a2d2fcd6.png
[mysql]: https://juejin.cn/post/6916128431969140749
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 4]: /images/20221022/68103ccf3e4a436aadd2fcd1f6163259.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 5]: /images/20221022/8f76b6ec3dad4b9f9d8a0d8390559edf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 6]: /images/20221022/fffab4a175064b158507082da15a555e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 7]: /images/20221022/d6fa66af5b204e9da8286b10a5c8fc36.png
[MySQL]: https://xiangou.blog.csdn.net/article/details/114374839
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5ZHlkMTEw_size_16_color_FFFFFF_t_70 8]: /images/20221022/7596e492ed4a4698b2577c19abbc562b.png