Mysql事务和锁

最新推荐文章于 2024-05-04 13:51:18 发布

深夜面包

最新推荐文章于 2024-05-04 13:51:18 发布

阅读量816

点赞数 1

分类专栏：深入理解Mysql 文章标签： mysql java

本文链接：https://blog.csdn.net/weixin_43738764/article/details/124868553

版权

深入理解Mysql 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

Mysql事务和锁

1、ACID 特性

在关系型数据库管理系统中，一个逻辑工作单元要成为事务，必须满足这 4 个特性，即所谓的 ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

WAL的全称为Write-Ahead Logging，先写日志，再写磁盘。

原子性

原子性：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
可能会有下面两种情况：
- 事务提交了，如果此时Buffer Pool的脏页没有刷盘，Redo Log 保证修改的数据生效；
- 如果事务没提交，但是Buffer Pool的脏页刷盘了，Undo Log保证不该存在的数据撤销；
每一个写事务，都会修改 Buffer Pool，从而产生相应的 Redo / Undo 日志，在Buffer Pool 中的页被刷到磁盘之前，这些日志信息都会先写入到日志文件中。
- 如果事务已提交并且Buffer Pool 中的脏页没有刷成功，此时数据库挂了，那在数据库再次启动之后，可以通过 Redo 日志将其恢复出来，以保证脏页写的数据不会丢失；
- 如果事务未提交脏页刷新成功，此时数据库挂了，就需要通过Undo 日志讲已修改数据的撤销；

持久性

持久性：指的是一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，后续的操作或故障不应该对其有任何影响，不会丢失。

隔离性

隔离性：指的是一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他的并发事务是隔离的。
InnoDB 支持的隔离性有 4 种，隔离性从低到高分别为：读未提交、读提交、可重复读、可串行化。

一致性

一致性：指的是事务开始之前和事务结束之后，数据库的完整性限制未被破坏。一致性包括：约束一致性和数据一致性。
- 约束一致性：创建表结构时所指定的外键、Check、唯一索引等约束，MySQL 中不支持Check 。
- 数据一致性：是一个综合性的规定，因为它是由原子性、持久性、隔离性共同保证的结果，而不是单单依赖于某一种技术。
一致性也可以理解为数据的完整性。数据的完整性是通过原子性、隔离性、持久性来保证的，而这3个特性又是通过 Redo/Undo 来保证的。逻辑上的一致性，包括唯一索引、外键约束、check 约束，这属于业务逻辑范畴。

2、事务控制的演进

并发事务

事务并发处理可能会带来一些问题，比如：更新丢失、脏读、不可重复读、幻读等。

更新丢失：当两个或多个事务更新同一行记录，会产生更新丢失现象。可以分为回滚覆盖和提交覆盖：
- 回滚覆盖：一个事务回滚操作，把其他事务已提交的数据给覆盖了；
- 提交覆盖：一个事务提交操作，把其他事务已提交的数据给覆盖了；
脏读：一个事务读取到了另一个事务修改但未提交的数据。
不可重复读：一个事务中多次读取同一行记录数据内容不一致。
幻读：一个事务中多次按相同条件查询，数据总量不一致。

串行化

所有事务执行的数据库操作顺序进行，不需要加锁，即全局排队。序列化执行所有的事务单元，数据库某个时刻只处理一个事务操作，特点是强一致性，处理性能低。

排他锁

锁就可以支持并发处理事务，如果事务之间涉及到相同的数据项时，会使用排他锁，或叫互斥锁，先进入的事务独占数据项以后，其他事务被阻塞，等待前面的事务释放锁。
在整个事务1结束之前，锁是不会被释放的，事务2必须等到事务1结束之后开始。

读写锁

读和写操作：读读、写写、读写、写读。
读写锁就是进一步细化锁的颗粒度，区分读操作和写操作，让读和读之间不加锁，两个事务就可以同时被执行了。
读写锁，可以让读和读并行，而读和写、写和读、写和写这几种之间还是要加排他锁。

MVCC

在事务1开始写操作的时候会copy一个记录的副本，其他事务读操作会读取这个记录副本，因此不会影响其他事务对此记录的读取，实现写和读并行。
多版本控制MVCC，也就是Copy on Write的思想。MVCC除了支持读和读并行，还支持读和写、写和读的并行，但为了保证一致性，写和写是无法并行的。

MVCC概念

MVCC（Multi Version Concurrency Control）被称为多版本控制，是指在数据库中为了实现高并发的数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本。大大提高了数据库的吞吐量及读写性能。
每次事务修改操作之前，都会在Undo日志中记录修改之前的数据状态和事务号，该备份记录可以用于其他事务的读取，也可以进行必要时的数据回滚。

MVCC实现原理 (版本链)

MVCC最大的好处是读不加锁，读写不冲突。MVCC只在 Read Commited（读已提交）和 Repeatable Read（可重复读）两种隔离级别下工作。
在 MVCC 并发控制中，读操作可以分为两类：快照读（Snapshot Read）与当前读（Current Read）
- 快照读：读取的是记录的快照版本（有可能是历史版本），不用加锁。（select）
- 当前读：读取的是记录的最新版本，并且当前读返回的记录，都会加锁，保证其他事务不会再并发修改这条记录。（select… for update 或lock in share mode，insert/delete/update）
如下图所示，假设 F1～F6 是表中字段的名字，1～6 是其对应的数据。后面三个隐含字段分别对应该行的隐含ID、事务号和回滚指针。
- 假如一条数据是刚 INSERT 的，DB_ROW_ID 为 1，其他两个字段为空。当事务 1 更改该行的数据值时，如下图所示。
  - 事务1 用排他锁锁定该行；记录 Redo log；
  - 把该行修改前的值复制到 Undo log，即图中下面的行；
  - 修改当前行的值，填写事务编号，使回滚指针指向 Undo log 中修改前的行。
- 事务2操作，过程与事务 1 相同，此时 Undo log 中会有两行记录，并且通过回滚指针连在一起，通过当前记录的回滚指针回溯到该行创建时的初始内容，如下图所示。

ReadView

readView（读视图）：事务在进行快照读的时候生成的记录快照，每一个事务都会生成自己的readView。
四个比较重要的概念：
- m_ids：表示在生成readview时，当前系统中活跃的读写事务id列表（即已开始且未提交的事务id列表）；
- min_id：m_ids中最小的事务id；
- max_id：系统分配给下一个事务的id；
- creator_trx_id：当前事务的id；
MVCC基于ReadView、undo log链（版本链）的查询过程：
1. 当执行select查询语句时，会生成一个ReadView记录如下数据：
  - m_ids:表示在生成readview时，当前系统中活跃的读写事务id列表，即已开时且未提交的事务id列表；
  - min_id：m_ids中最小的事务id；
  - max_id：系统分配给下一个事务的id；
  - creator_trx_id：当前事务的id；
2. 拿出当前buffer pool中该记录的db_trx_id。
3. 判断db_trx_id与creator_trx_id是否相等。
4. 如果相等则表示是同一事务，则这条记录可见。
5. 否则判断db_trx_id是否小于min_id，如果小于则表示当前数据在开启事务之前就存在的，这条记录可见。
6. 否则判断db_trx_id是否大于max_id，如果大于则表示这个数据版本是在创建ReadView之后产生的，这条记录可见不可见，回滚到上一个数据版本继续从步骤2开始执行。
7. 否则表示db_trx_id在max_id和min_id之间，判断db_trx_id是否在m_ids列表中，不存在则说明创建ReadView之前这个事务已经被提交了，这条记录可见。
8. 否则表示创建ReadView之前这个事务未提交，这条记录不可见回滚到上一个数据版本继续从步骤2开始执行。
Read Commited（读已提交）和 Repeatable Read（可重复读）在MVCC实现区别：
- RC是在每一次select时都会重新生成readView， RR则是在只会在第一次select生成readView。

3、事务的隔离级别

事务隔离级别类型

读未提交（Read Uncommitted）：最低的隔离级别，允许读取并发事务尚未提交的数据变更。解决了回滚覆盖类型的更新丢失，可能导致脏读、幻读或不可重复读；
读已提交（Read Committed）：允许读取并发事务已经提交的数据。解决了脏读，可能会导致不可重复读或幻读；
可重复读（Repeatable Read）：除了数据是被本身事务所修改，否则对同一字段的多次读取结果都是一致的。解决了不可重复读，可能会导致幻读；
串行化（Serializable）：最高的隔离级别，完全遵从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰。解决了脏读、不可重复读以及幻读，可能导致大量的超时现象的和锁竞争，效率低下；
数据库的事务隔离级别越高，并发问题就越小，但是并发处理能力越差（代价）。事务隔离级别，针对Innodb引擎，支持事务的功能。

事务隔离级别和锁的关系

事务隔离级别是SQL92定制的标准，相当于事务并发控制的整体解决方案，本质上是对锁和MVCC使用的封装，隐藏了底层细节。
锁是数据库实现并发控制的基础，事务隔离性是采用锁来实现，对相应操作加不同的锁，就可以防止其他事务同时对数据进行读写操作。
对用户来讲，首先选择使用隔离级别，当选用的隔离级别不能解决并发问题或需求时，才有必要在开发中手动的设置锁。
- MySQL默认隔离级别：可重复读；
- Oracle、SQLServer默认隔离级别：读已提交；
- 一般使用时，建议采用默认隔离级别，然后存在的一些并发问题，可以通过悲观锁、乐观锁等实现处理。

4、锁机制

锁分类

从操作的粒度可分为表级锁、行级锁和页级锁。
- 表级锁：每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB 等存储引擎中。
- 行级锁：每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB 存储引擎中。
- 页级锁：每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，开销和加锁时间界于表
  锁和行锁之间，并发度一般。应用在BDB 存储引擎中。
从操作的类型可分为读锁和写锁。
- 事物锁
  - 读锁和写锁是行级锁，可分为读锁（S锁）和写锁（X锁），数据库的增删改操作默认都会加排他锁，而查询不会加任何锁；
  - 读锁（S锁）：共享锁，对某一资源加共享锁，多个读操作可以同时进行而不会互相影响（即共享锁可多个共存），但无法修改。要想修改就必须等所有共享锁都释放完之后。
  - 写锁（X锁）：排他锁，对某一资源加排他锁，自身可以进行增删改查，当前写操作没有完成前，其他人无法进行任何操作。
  - S锁：事务A对记录添加了S锁，可以对记录进行读操作，不能做修改，其他事务可以对该记录追加S锁，但是不能追加X锁，需要追加X锁，需要等记录的S锁全部释放。
  - X锁：事务A对记录添加了X锁，可以对记录进行读和修改操作，其他事务不能对记录做读和修改操作。
- 意向锁：
  - 意向锁是表级锁，可分为意向读锁（IS锁）和意向写锁（IX锁）；
  - 解决表锁与存在的行锁冲突，避免为了判断表是否存在行锁而去扫描全表的系统消耗；
  - 事务在获取行级 S 锁之前，必须获取其对应表的 IS 或 IX 锁。
  - 事务在获取行级 X 锁之前，必须获取其对应表的 IX 锁。
从操作的性能可分为乐观锁和悲观锁。
- 乐观锁：总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，不会上锁，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。
- 悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。
- 区别：
  - 乐观锁适用于读多写少的情况，即冲突很少发生的时候，这样可以省去了锁的开销。
  - 悲观锁适用于写多的情况，经常产生冲突不断的进行retry，这样反倒是降低了性能。

行锁原理

在InnoDB引擎中，我们可以使用行锁和表锁，其中行锁又分为共享锁和排他锁。InnoDB行锁是通过对索引数据页上的记录加锁实现的，主要实现算法有 3 种：Record Lock、Gap Lock 和 Next-key Lock。
- RecordLock锁：锁定单个行记录的锁。（记录锁，RC、RR隔离级别都支持）
- GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别支持）
- Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持）
在RR隔离级别，InnoDB对于记录加锁行为都是先采用Next-Key Lock，但是当SQL操作含有唯一索引时，Innodb会对Next-Key Lock进行优化，降级为RecordLock，仅锁住索引本身而非范围。

示例

以如下操作为例分析下 InnoDB 对不同索引的加锁行为，隔离级别为：Repeatable Read（可重复读）

update t1 set name=‘XX’ where id=10

主键加锁
- 加锁行为：仅在id=10的主键索引记录上加X锁。
唯一键加锁
- 加锁行为：先在唯一索引id上加X锁，然后在id=10的主键索引记录上加X锁。
非唯一键加锁
- 加锁行为：对满足id=10条件的记录和主键分别加X锁，然后在（6，c） - （10，b）、（10，b） - （10，d）、（10，d） - （11，f）范围分别加Gap Lock锁。
无索引加锁
- 加锁行为：表里所有行和间隙都会加X锁。（当没有索引时，会导致全表锁定，因为InnoDB引擎锁机制是基于索引实现的记录锁定）。

悲观锁

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

表级锁

表级锁每次操作都锁住整张表，并发度最低。常用命令如下：
- 手动增加表锁
  - ```
  lock table 表名称 read|write,表名称2 read|write;
```
- 查看表上加过的锁
  - ```
  show open tables;
```
- 删除表锁
  - ```
  unlock tables;
```
- 表级读锁：当前表追加读锁，当前连接和其他的连接都可以读操作；但是当前连接增删改操作会报错，其他连接增删改会被阻塞。
- 表级写锁：当前表追加写锁，当前连接可以对表做增删改查操作，其他连接对该表所有操作都被阻塞（包括查询）。
- 总结：表级读锁会阻塞写操作，但是不会阻塞读操作。而写锁则会把读和写操作都阻塞。

共享锁（行级锁，读锁）

共享锁又称为读锁，简称S锁。共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。使用共享锁的方法是在select … lock in share mode，只适用查询语句。
总结：事务使用了共享锁（读锁），只能读取，不能修改，修改操作被阻塞。

排他锁（行级锁，写锁）

排他锁又称为写锁，简称X锁。排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能对该行记录做其他操作，也不能获取该行的锁。
使用排他锁的方法是在SQL末尾加上for update，innodb引擎默认会在update，delete语句加上for update。行级锁的实现其实是依靠其对应的索引，所以如果操作没用到索引的查询，那么会锁住全表记录。
总结：事务使用了排他锁（写锁），当前事务可以读取和修改，其他事务不能修改，也不能获取记录锁（select… for update）。如果查询没有使用到索引，将会锁住整个表记录。

乐观锁

乐观锁不是数据库提供的功能，需要开发者自己去实现。在数据库操作时，总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，不会上锁，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。
乐观锁实现的关键点：冲突的检测。
悲观锁和乐观锁都可以解决事务写写并发，在应用中可以根据并发处理能力选择区分，比如对并发率要求高的选择乐观锁；对于并发率要求低的可以选择悲观锁。

乐观锁实现原理

使用版本字段（version）
- 先给数据表增加一个版本(version) 字段，每操作一次，将那条记录的版本号加 1。version是用来查看被读的记录有无变化，作用是防止记录在业务处理期间被其他事务修改。

死锁与解决方案

表锁死锁

产生原因：
- 用户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的顺序处理，必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

行级锁死锁

产生原因1：
- 如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁。
解决方案1：
- SQL语句中不要使用太复杂的关联多表的查询；使用explain“执行计划"对SQL语句进行分析，对于有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化。
产生原因2：
- 两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁。
解决方案2：
- 在同一个事务中，尽可能做到一次锁定所需要的所有资源，按照id对资源排序，然后按顺序进行处理。

共享锁转换为排他锁

产生原因：
- 事务A 查询一条纪录，然后更新该条纪录；此时事务B 也更新该条纪录，这时事务B 的排他锁由于事务A 有共享锁，必须等A 释放共享锁后才可以获取，只能排队等待。事务A 再执行更新操作时，此处发生死锁，因为事务A 需要排他锁来做更新操作。但是，无法授予该锁请求，因为事务B 已经有一个排他锁请求，并且正在等待事务A 释放其共享锁。
解决方案：
- 使用乐观锁进行控制。乐观锁机制避免了长事务中的数据库加锁开销，大大提升了大并发量下的系统性能。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中；

死锁排查

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：系统启动后到现在总共等待的次数；
  - 如果等待次数高，而且每次等待时间长，需要分析系统中为什么会有如此多的等待，然后着手定制优化；

深夜面包

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
Mysql事务和锁

Mysql事务和锁1、ACID 特性在关系型数据库管理系统中，一个逻辑工作单元要成为事务，必须满足这 4 个特性，即所谓的 ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。WAL的全称为Write-Ahead Logging，先写日志，再写磁盘。原子性原子性：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。可能会有下面两种情况：事务提交了，如果此时Buffer Pool的脏页
复制链接

扫一扫