Mysql（六）Mysql锁与事务隔离级别 ACID属性并发事务处理带来的问题事务隔离级别锁详解锁分类表锁行锁事务隔离级别案例读未提交读已提交不可重复读串行化间隙锁

冷不防 2022-08-28 09:55 66阅读 0赞

### 文章目录 ###

*  前言
 *  ACID属性
 *  并发事务处理带来的问题
 *  事务隔离级别
 *  锁详解
 *   *  锁分类
     *  表锁
     *  行锁
 *  事务隔离级别案例
 *   *  读未提交：
     *  读已提交
     *  不可重复读
     *  串行化
 *  锁案例
 *   *  间隙锁(Gap Lock)
     *  临键锁(Next-key Locks)
     *  无索引行锁会升级为表锁
     *  锁优化建议

--------------------

# 前言 #

数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能就会导致脏写、脏读、不可重复读、幻读这些问题。为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题。本章主要是同读者一起学习，认识感受这些概。念

--------------------

# ACID属性 #

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

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

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

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

# 事务隔离级别 #

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

<table> 
 <thead> 
  <tr> 
   <th>隔离级别</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>读未提交</td> 
   <td>可能</td> 
   <td>可能</td> 
   <td>可能</td> 
  </tr> 
  <tr> 
   <td>读已提交</td> 
   <td>不可能</td> 
   <td>可能</td> 
   <td>可能</td> 
  </tr> 
  <tr> 
   <td>可重复读</td> 
   <td>不可能</td> 
   <td>不可能</td> 
   <td>可能（MySQLInnoDB不可能）</td> 
  </tr> 
  <tr> 
   <td>可串行化</td> 
   <td>不可能</td> 
   <td>不可能</td> 
   <td>不可能</td> 
  </tr> 
 </tbody> 
</table>

数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。不同的应用对读一致性和事务隔离程度的要求也是不同的,有的应用对“不可重复读"和“幻读”并不敏感,可能更关心数据并发访问的能力。

查看当前数据库的事务隔离级别:

show variables like 'tx_isolation';

mysql8.0，tx\_isolation变量改为transaction\_isolation

show variables like 'transaction_isolation'

设置事务隔离级别：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别。

# 锁详解 #

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

在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，**锁冲突**也是影响数据库并发访问性能的一个重要因素。

## 锁分类 ##

从性能上分：

*  乐观锁(用版本对比来实现)
 *  悲观锁

从对数据库操作的类型分：(都属于悲观锁)

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

从对数据操作的粒度分：

*  表锁
 *  行锁

## 表锁 ##

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

手动增加表锁

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

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

show open tables;

删除表锁

unlock tables;

## 行锁 ##

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

一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞  
.  
InnoDB与MYISAM的不同：

*  InnoDB支持事务（TRANSACTION）
 *  InnoDB支持行级锁
 *  MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。
 *  InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。

# 事务隔离级别案例 #

案例表

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 `test`.`account` (`name`, `balance`) VALUES ('lilei', '450');
    INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('hanmei', '16000');
    INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lucy', '2400');

实验开始前，先关闭自动提交

set global autocommit = 0;

![在这里插入图片描述][7f27092fa43d4d8db9af9f4e32278e61.png]

## 读未提交： ##

打开一个客户端A，并设置当前事务模式为read uncommitted（读未提交）

set global transaction isolation level read uncommitted;

查询表account的初始值：

start transaction;-- 开启事务
    select * from account

打开另一个客户端B，更新表account：

start transaction；
    update account set balance = balance - 50 where id = 1;

此时不能用navicat，navicat会自动提交  
![在这里插入图片描述][10353a45861748a6a0e328a35167f33c.png]  
这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据：  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAVF9BbnRyeQ_size_20_color_FFFFFF_t_70_g_se_x_16]  
一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端A查询到的数据其实就是脏数据。

## 读已提交 ##

客户端A修改隔离级别

set global transaction isolation level read committed;

客户端A开启事务查询

start transaction;
    select * from account;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAVF9BbnRyeQ_size_18_color_FFFFFF_t_70_g_se_x_16]

重启新的客户端B，开启事务，修改表

update account set balance = balance - 50 where id = 1;

![在这里插入图片描述][36615296e4b545db8ce7a884b5272ca1.png]

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

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAVF9BbnRyeQ_size_12_color_FFFFFF_t_70_g_se_x_16]

客户端B的事务提交

commit;

![在这里插入图片描述][d11d188c08da415fbb20b6f022aa2541.png]

客户端A执行与上一步相同的查询，结果 与上一步不一致，即产生了不可重复读的问题  
![在这里插入图片描述][bd10eb99886944f1a7fd708a38b0a97b.png]

## 不可重复读 ##

客户端A，设置当前事务模式为repeatable read

set global transaction isolation level repeatable read;

查询表account的所有记录

![在这里插入图片描述][3bc3c3777b7e455190af2b7e0f766e70.png]

打开另一个客户端B，更新表account并提交

start transaction;
    update account set balance = balance - 50 where id = 1;
    commit;

在客户端A查询表account的所有记录，  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAVF9BbnRyeQ_size_20_color_FFFFFF_t_70_g_se_x_16 1]  
在客户端A，接着执行

update account set balance = balance - 50 where id = 1

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAVF9BbnRyeQ_size_18_color_FFFFFF_t_70_g_se_x_16 1]

balance没有变成450-50=400，lilei的balance值用的是步骤2中的400来算的，所以是350，数据的一致性倒是没有被破坏。

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

重新打开客户端B，插入一条新数据后提交，

begin;
    insert into account values(4,'antry',666);
    commit;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAVF9BbnRyeQ_size_15_color_FFFFFF_t_70_g_se_x_16]

随后如果在客户端A能查看到这条消息就是幻读。因为前面表格中降到了可重复读下（MySQLInnoDB不可能）出现幻读，所以这边没有幻读的效果。  
![在这里插入图片描述][db27b06c526142218d971067f8e372e9.png]

但，此时在客户端A能够修改客户端新增的id为4的数据,且再次查询能查到客户端B新增的数据。因此又可以证明幻读的存在了。

update account set balance = 888 where id = 4;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAVF9BbnRyeQ_size_17_color_FFFFFF_t_70_g_se_x_16]

## 串行化 ##

客户端A，设置当前事务模式为serializable

set global transaction isolation level serializable;

查询表account的初始值：  
![在这里插入图片描述][cc14be35a2b6432c86320931f72e109e.png]  
打开一个客户端B，更新相同的id为1的记录会被阻塞等待，如果很久等不到就会报超时。

![在这里插入图片描述][8caec665d43048bbafc3a161664fc33c.png]

更新id为2的记录可以成功，说明在串行模式下innodb的查询也会被加上行锁。  
![在这里插入图片描述][4aeb389b25464d6c95cf69839cd5954e.png]

如果客户端A执行的是一个范围查询，那么该范围内的所有行包括每行记录所在的间隙区间范围(就算该行数据还未被插入也会加锁，这种是间隙锁)都会被加锁。此时如果客户端B在该范围内插入数据都会被阻塞，所以就避免了幻读。

# 锁案例 #

## 间隙锁(Gap Lock) ##

间隙锁，锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read，间隙锁在某些情况下可以解决幻读问题。  
![4a5d4ac768c74e7f9b75b1b1bb4a5711.png][]  
上图数据间隙有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间

在Session\_1下面执行 update account set name = ‘antry’ where id > 8 and id <18;，则其他Session没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在(3,20\]区间都无法修改数据，**最后那个20也是包含在内的。**

间隙锁是在可重复读隔离级别下才会生效。

## 临键锁(Next-key Locks) ##

Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20\]的整个区间可以叫做临键锁。

## 无索引行锁会升级为表锁 ##

锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁

*  session1 执行：update account set balance = 800 where name = ‘lilei’;
 *  session2 对该表任一行操作都会阻塞住  
    InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

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

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。**当系统并发量高的时候，Innodb的整体性能和MYISAM相比就会有比较明显的优势了。** 但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MYISAM高，甚至可能会更差。

通过检查InnoDB\_row\_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';

![在这里插入图片描述][d4abe478f70145a9b8453c3504a0c230.png]

*  Innodb\_row\_lock\_current\_waits: 当前正在等待锁定的数量
 *  Innodb\_row\_lock\_time: 从系统启动到现在锁定总时间长度
 *  Innodb\_row\_lock\_time\_avg: 每次等待所花平均时间
 *  Innodb\_row\_lock\_time\_max：从系统启动到现在等待最长的一次所花时间
 *  Innodb\_row\_lock\_waits:系统启动后到现在总共等待的次

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

查看INFORMATION\_SCHEMA系统库锁相关数据表

‐‐ 查看事务
    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;

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

## 锁优化建议 ##

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

[7f27092fa43d4d8db9af9f4e32278e61.png]: /images/20220828/56168b1758f843869f1c76db4607058f.png
[10353a45861748a6a0e328a35167f33c.png]: /images/20220828/18e6060a2ac74ad0885223cc041127ef.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAVF9BbnRyeQ_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/2053accf4a1d47d78c0ed5809f871045.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAVF9BbnRyeQ_size_18_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/af60949ddf214ffc887ecc7f9ba46cb8.png
[36615296e4b545db8ce7a884b5272ca1.png]: /images/20220828/fcf04f345bdd4794afc326ed1ab13517.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAVF9BbnRyeQ_size_12_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/04145b7a132c409ebbd93bd921a0b37c.png
[d11d188c08da415fbb20b6f022aa2541.png]: /images/20220828/fc101ff0c3914e7d928770cafd6f8ecf.png
[bd10eb99886944f1a7fd708a38b0a97b.png]: /images/20220828/b0096563d3434a16b6710afc67ee21da.png
[3bc3c3777b7e455190af2b7e0f766e70.png]: /images/20220828/36471912333c4c01b5497b546df469e6.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAVF9BbnRyeQ_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/2ef65ba55fff46ab9c16b99c0d4d83ff.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAVF9BbnRyeQ_size_18_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/2307eb1b090341799a4e12c319ae9977.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAVF9BbnRyeQ_size_15_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/c73540e68d394c80ab9699472bbad32a.png
[db27b06c526142218d971067f8e372e9.png]: /images/20220828/d437515f23544bce8b14d5d70310c814.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAVF9BbnRyeQ_size_17_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/7a3050b4082b4c079fbe4d93d9141858.png
[cc14be35a2b6432c86320931f72e109e.png]: /images/20220828/6dce15c10f5a4869872a8d4ae1e27785.png
[8caec665d43048bbafc3a161664fc33c.png]: /images/20220828/62d06d72f371436b9419ef4f83b1c9bb.png
[4aeb389b25464d6c95cf69839cd5954e.png]: /images/20220828/61cfbb4fec9f4ca2ae2650461fe6c1d2.png
[4a5d4ac768c74e7f9b75b1b1bb4a5711.png]: /images/20220828/2863ae2b8b1947098fa46e1748391382.png
[d4abe478f70145a9b8453c3504a0c230.png]: /images/20220828/2c40ca2cc9744856ab40af5b79b94e58.png