MySQL（四）事务和锁

矫情吗；* 2022-04-03 08:56 220阅读 0赞

# 事务 #

事务是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位，它可以是一条SQL语句（默认）、一组SQL语句、或整个程序，要多条语句一个事务，需要通过 ‘begin’(等价start transaction)、‘commit’、‘rollback ’来控制事务，并且它的开始可以由用户显示的控制，如果用户没有显示地定义事务，则由数据库管理员默认规定自动划分事务。

## 事务的ACID特性 ##

事务具有4个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、和持续性（Durability）。这四个特性简称为ACID特性（ACID properties）。

> 1.  原子性：多个SQL作为一个整体运行，不可分割。
> 2.  一致性：一个事物内结果应当一致
> 3.  隔离性：一个事务的执行不能被其他事务干扰
> 4.  持续性：事务一旦提交，事务内的修改将应当永久生效

## 事务控制语言 ##

> begin; // 表示事务的起点 等价方式： \`start transaction\`  
> sql1  
> sql2  
> ...  
> commit(提交，表示结果都将生效) 和 rollback(回滚，用来撤销事务内的更改) 表示事务的终点

## 事务隔离性 ##

事务具有不同的隔离级别，隔离级别越低，并发性越好，但数据的一致性差，反之则并发性差，但数据一致性高。

> 由低到高：
> 
> 读未提交 < 读提交 < 可重复读(mysql默认) < 序列化读

### 脏读（读未提交） ###

脏读是由于事务一修改某一数据并将其写回磁盘，事务二读取同一数据后，事务一由于某种原因被撤销，这时被事务一修改过的数据恢复原值，事务二读到的数据就为“脏”数据，即不正确的数据，将隔离级别提升到“读提交”，可以避免脏读。

**如下图：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2dyZXlfbW91c2U_size_16_color_FFFFFF_t_70][]

如上图，事务一将小红的工资修改为700， 事务二读到小红的工资为700，而事务一由于某种原因撤销了修改，小红的工资恢复为1000，这时事务二读到的数据为700，与数据库内容不一致，这就是脏读，将隔离级别提升到“读提交”，可以避免脏读。

### 不可重复读 ###

不可重复读是指事务一读取数据后，事务二执行更新操作，使事务一无法再现前一次读取结果。

**如下图：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2dyZXlfbW91c2U_size_16_color_FFFFFF_t_70 1][]

事务二查询小红工资时（1000），事务一修改了小红工资（700），之后事务二在查询小红工资时，发现查到的工资和之前查到的不一样，这就是不可重复读。

### 幻读 ###

一边做查询，一边做插入。

如下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2dyZXlfbW91c2U_size_16_color_FFFFFF_t_70 2][]

这里的编号为主键，事务一查询编号的同时，事务二插入编号，这时事务一查询出来的编号为10和20，没查出编号30，事务一也想插入按顺序插入编号，也就是插入30，但是由于事务二已经插入了编号30，因此事务一插入就报错了（主键冲突，因为主键是唯一的），这就是幻读。

**因此要避免脏读、不可重复读、幻读：将隔离级别提高到\`序列化读\`，所谓的\`序列化读\`就是把多版本并发退化到锁的并发控制（也就是select语句上会被偷偷加上共享锁）。**

# 锁 #

> InnoDB 行级锁, 只要两个客户端更新的是不同的行，互不干扰，默认添加的是意向锁
> 
>  *  X锁：增删改（insert update delete) 都会在行上加排他锁（X锁），上了这个锁别人都不能对其进行操作，只能等它释放锁才行，并且都能再加上别的类型的锁了
> 
> select \* from 表名 where ... for update
> 
>  *  S锁：查询可以加共享锁 (S锁)：表示可以同时查询，但其他人不能增删改（insert update delete)
> 
> select \* from 表名 where ... lock in share mode; 添加共享锁-别人可以再加共享锁，但不能再加排他锁
> 
>  *  IS锁：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁（意向共享锁）
>  *  IX锁：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁（意向排它锁）
> 
> MyISAM 表锁，是锁住整个表，MySQL 8.0中已经去除MyISAM引擎的表，也表明MySQL已不建议使用该引擎。

### InnoDB 行级锁的兼容列表 ###

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td>S（共享锁）</td> 
   <td>X（排它锁）</td> 
   <td>IS（意向共享锁）</td> 
   <td>IX（意向排它锁）</td> 
  </tr> 
  <tr> 
   <td>S</td> 
   <td>兼容</td> 
   <td>不兼容</td> 
   <td>兼容</td> 
   <td>不兼容</td> 
  </tr> 
  <tr> 
   <td>X</td> 
   <td>不兼容</td> 
   <td>不兼容</td> 
   <td>不兼容</td> 
   <td>不兼容</td> 
  </tr> 
  <tr> 
   <td>IS</td> 
   <td>兼容</td> 
   <td>不兼容</td> 
   <td>兼容</td> 
   <td>兼容</td> 
  </tr> 
  <tr> 
   <td>IX</td> 
   <td>不兼容</td> 
   <td>不兼容</td> 
   <td>兼容</td> 
   <td>兼容</td> 
  </tr> 
 </tbody> 
</table>

上述列表表示，如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放，并且意向锁是InnoDB自动加的，不需用户干预。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2dyZXlfbW91c2U_size_16_color_FFFFFF_t_70]: /images/20220403/a5c383db122e4e0bb507fc4a50d58c7a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2dyZXlfbW91c2U_size_16_color_FFFFFF_t_70 1]: /images/20220403/6c7fc5b842e14d15b750223a13c27633.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2dyZXlfbW91c2U_size_16_color_FFFFFF_t_70 2]: /images/20220403/bc3d72bfd1004a1894375bef2f999fe2.png