mysql innodb事务和锁详解

柔光的暖阳◎ 2022-03-19 05:22 273阅读 0赞

mysql 当使用innodb时候，是支持事务和行级锁的，本篇作为自己理解的一个梳理。

**目录**

简介

事务的ACID特性：

mysql开启事务的方法

事务并发可能会遇到的问题：

1.赃读

2.幻读

3.不可重复读

数据库的隔离级别

mySql中的锁

一次封锁or两段锁

行锁分类：共享锁（S锁），排他锁（X锁）

表锁分类：意向共享锁(IS)，意向排它锁(IX)，自增锁（AUTO\_INC LOCKS）

mysql行锁实现原理

利用锁来解决赃读，幻读，不可重复读的问题

悲观锁和乐观锁

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

# 简介 #

数据库事务：数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作; 事务是一组不可再分割的操作集合(工作逻辑单元);

## 事务的ACID特性： ##

原子性(Atomicity)：最小的工作单元，整个工作单元要么一起提交成功，要么全部失败回滚。

一致性(Consistency)：事务中操作的数据及状态改变是一致的，即写入资料的结果必须完全符合预设的规则，不会因为出现系统意外等原因导致状态的不一致。

隔离性(Isolation)：一个事务所操作的数据在提交之前，对其他事务的可见性设定(一般设定为不可见)（一个事务的执行不影响其他的事务）

持久性(Durability)：事务所做的修改就会永久保存，不会因为系统意外导致数据的丢失。

## mysql开启事务的方法 ##

手动开启：

begin / start transaction：（事务开始）

commit / rollback：事务提交或回滚

自动：

set session autocommit = on/off;：设定事务是否自动开启。

在Spring JDBC中开启方法：connection.setAutoCommit(boolean);（相当于mysql set session autocomit ）

AOP中：expression=execution(com.test.dao.\*.\*(..))

## 事务并发可能会遇到的问题： ##

### 1.赃读 ###

赃读：指一个线程中的事务读取到了另外一个线程中未提交的数据。

举例如下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70][]

事务B对数据库中数据ada进行age修改，此时事务A读取数据库中这个数据，获得的是修改的数据，然后事务B回滚，数据库中数据恢复到之前的数据，此时事务A读取的到的数据就是错误的数据，这个就是赃读。

### 2.幻读 ###

幻读（虚读）：指一个线程中的事务读取到了另外一个线程中提交的insert的数据。（**读取结果集条数的对比**）一个事务按相同的查询条件查询之前检索过的数据，确发现检索出来的结果集条数变多或者减少（由其他事务插入、删除的），类似产生幻觉。

举例如下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 1][]

事务B提交之后，事务A两次读取的数据不一致。

### 3.不可重复读 ###

不可重复读：指一个线程中的事务读取到了另外一个线程中提交的update的数据。（**读取数据本身的对比**）一个事务在读取某些数据后的一段时间后，再次读取这个数据，发现其读取出来的数据内容已经发生了改变，就是不可重复读。

举例：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 2][]

事务B更新数据库中数据之后，事务的两次读取结果不同。

## 数据库的隔离级别 ##

基于以上数据库并发会产生的三种情况，mysql将数据库的隔离级别分为四种：未提交度（RU），已提交读（RC），可重复读（RR），串行化（Serializable）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 3][]

四种隔离级别和三种并发情况对应的关系如下：

Oracle等多数数据库默认都是已提交读(RC)级别 ，mysql中的innodb默认是可重复读级别（RR）。

# mySql中的锁 #

数据库中的锁：是用于管理不同事务对共享资源的并发访问。

在innodb中分为表锁和行锁，innodb表锁的实质是通过锁定表中的所有行来实现表锁的功能。

表锁与行锁区别：

1.锁定粒度：表锁>行锁

2.加锁效率：表锁>行锁

3.冲突概率：表锁>行锁

4.并发性能：表锁<行锁

## 一次封锁or两段锁 ##

因为有大量的并发访问，为了预防死锁，一般应用中推荐使用一次封锁法，就是在方法的开始阶段，已经预先知道会用到哪些数据，然后全部锁住，在方法运行之后，再全部解锁。这种方式可以有效的避免循环死锁，但在数据库中却不适用，因为在事务开始阶段，数据库并不知道会用到哪些数据。数据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）

*  加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁（共享锁，其它事务可以继续加共享锁，但不能加排它锁），在进行写操作之前要申请并获得X锁（排它锁，其它事务不能再获得任何锁）。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。
 *  解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。

<table> 
 <thead> 
  <tr> 
   <th>事务</th> 
   <th>加锁/解锁处理</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>begin；</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>insert into test .....</td> 
   <td>加insert对应的锁</td> 
  </tr> 
  <tr> 
   <td>update test set...</td> 
   <td>加update对应的锁</td> 
  </tr> 
  <tr> 
   <td>delete from test ....</td> 
   <td>加delete对应的锁</td> 
  </tr> 
  <tr> 
   <td>commit;</td> 
   <td>事务提交时，同时释放insert、update、delete对应的锁</td> 
  </tr> 
 </tbody> 
</table>

这种方式虽然无法避免死锁，但是两段锁协议可以保证事务的并发调度是串行化（串行化很重要，尤其是在数据恢复和备份的时候）的。

##  行锁分类：共享锁（S锁），排他锁（X锁） ##

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

加锁释锁方式:

select \* from users WHERE id=1 LOCK IN SHARE MODE;

commit/rollback

排他锁又称为写锁，简称X锁，排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁(共享锁、排他锁)，只有该获取了排他锁的事务是可以对数据行进行读取和修改，(其他事务要读取数据可来自于快照)

加锁释锁方式:

delete / update / insert 默认加上X锁

SELECT \* FROM table\_name WHERE ... FOR UPDATE

commit/rollback

只有通过索引条件进行数据检索，InnoDB才使用行级锁，否则，InnoDB 将使用表锁(锁住索引的所有记录)

表锁:lock tables xx read/write;

## 表锁分类：意向共享锁(IS)，意向排它锁(IX)，自增锁（AUTO\_INC LOCKS） ##

意向共享锁：表示事务准备给数据行加入共享锁，即一个数据行加共享锁前必须先取得该表的IS锁， 意向共享锁之间是可以相互兼容的

意向排它锁(IX)：表示事务准备给数据行加入排他锁，即一个数据行加排他锁前必须先取得该表的IX锁， 意向排它锁之间是可以相互兼容的

意向锁(IS、IX)是InnoDB数据操作之前自动加的，不需要用户干预

意义:当事务想去进行锁表时，可以先判断意向锁是否存在，存在时则可快速返回该表不能启用表锁

自增锁：针对自增列自增长的一个特殊的表级别锁

show variables like 'innodb\_autoinc\_lock\_mode';

默认取值1，代表连续，事务未提交ID永久丢失

## mysql行锁实现原理 ##

mysql行锁实现原理是通过记录锁，间隙锁和临键锁来实现的

临键锁：（Next-key locks）锁住记录+区间(左开右闭)，当sql执行按照索引进行数据的检索时,查询条件为范围查找(between and、<、>等)并有数 据命中则此时SQL语句加上的锁为Next-key locks，锁住索引的记录+区间(左开右闭)

间隙锁（Gap locks）:锁住数据不存在的区间(左开右开) ，当sql执行按照索引进行数据的检索时，查询条件的数据不存在，这时SQL语句加上的锁即为Gap locks，锁住索引不存在的区间(左开右开)

临键锁（Next-key locks）:锁住记录+区间(左开右闭)，当sql执行按照索引进行数据的检索时,查询条件为范围查找(between and、<、>等)并有数 据命中则此时SQL语句加上的锁为Next-key locks，锁住索引的记录+区间(左开右闭)

临键锁举例，入下图的例子，数据库中id数据有1，4，7，10；这段数据库的区间分为（-∞,1\],（1,4\],（4,7\],（7,10\],（10,+∞)（默认是按照左开右闭原则）。

1.当查找范围在id>5,id<9这个区间，那么锁住的的区间就是4,7\],（7,10\]。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 4][]

2.当查找的数据不存在的时候，变为间隙锁

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 5][]

3.当使用的是主键索引，查找数据刚好落在具体数据上的时候，变为记录锁

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 6][]

## 利用锁来解决赃读，幻读，不可重复读的问题 ##

赃读：加上排他锁：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 7][]

幻读：加上临键锁

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 8][]

不可重复读：加上S锁

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 9][]

### 悲观锁和乐观锁 ###

悲观锁：正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

乐观锁：相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70]: /images/20220319/5d22d92e72cd4d6ca86ed6779d9e79b3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 1]: /images/20220319/1e794ced33a34a68a025db0231dda5de.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 2]: /images/20220319/ca2e7c946d934692b49dfc8e4a912cfc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 3]: /images/20220319/ded57e0bbf2e4ae59b40703581f087fc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 4]: /images/20220319/b4b81a28a88e4ae58967980f8694f3f8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 5]: /images/20220319/2f2fe8e6e4b640d092b394b68ef2144c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 6]: /images/20220319/9eb9e2a9d7e1438c89681e9df3ee51d6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 7]: /images/20220319/e6eaf77b97364af087c57f248876d08a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 8]: /images/20220319/a532d6ca5f4645089c709b97bd41a3de.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcnJ5cHR0ZXI_size_16_color_FFFFFF_t_70 9]: /images/20220319/fa8b5e9d2a154753b9d2e55a1475fc66.png