【MySQL中的锁】

骑猪看日落 2023-03-06 08:17 11阅读 0赞

### MySQL中的锁 ###

*   *   *   *   *  \[1\] 为什么要加锁
                 *  \[2\] 不同引擎所支持的锁？
                 *  \[3\] 行锁：悲观锁
                 *  \[4\] 意向锁
                 *  \[5\] 间隙锁
                 *  \[5\] MVCC：乐观锁
                 *  \[6\] 什么时候会加锁？

> 原文链接：https://blog.csdn.net/soonfly/article/details/70238902
> 
> 首先从思想上无论在java还是mysql都有这乐观锁和悲观锁的概念，数据库可以分为**悲观锁（共享锁和排他锁）**，本文就从这里入手

##### \[1\] 为什么要加锁 #####

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的 计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一 个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。本章我们着重讨论MySQL锁机制 的特点，常见的锁问题，以及解决MySQL锁问题的一些方法或建议。

##### \[2\] 不同引擎所支持的锁？ #####

> 它们都支持表级锁，BDB采用页面锁，InnoDB采用的是行级锁。

**MyISAM和MEMORY存储引擎**：采用的是表级锁（table-level locking）；

**BDB存储引擎**：采用的是页面锁（page-level locking），但也支持表级锁；

**InnoDB存储引擎**：既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。  
**表级锁**：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。  
**行级锁**：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。  
**页面锁**：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般  
从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度 来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

> MySQL的表级锁有两种模式\*\*：表共享读锁（Table Read Lock）**和**表独占写锁（Table Write Lock）\*\*。

##### \[3\] 行锁：悲观锁 #####

InnoDB实现了以下两种类型的**悲观锁**。

*  **X锁**（Exclusive Lock）—排他锁或独占锁：若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁.它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止.在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁.
    
    注意：排他锁会阻止其它事务再对其锁定的数据加读或写的锁，但是不加锁的就没办法控制了.

> **排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁，而并非不能读**
> 
> 在select命令中使用独占锁的SQL语句为：select … for update;

*  **S锁**（Shared Lock）—共享锁：若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁.这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改.

> 如果在select查询语句中要手动加入共享锁，那么对应的SQL语句为：select … lock in share mode

**InnoDB行锁实现方式**  
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

##### \[4\] 意向锁 #####

**为了允许行锁和表锁共存**，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

**意向共享锁（IS）**：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。  
**意向排他锁（IX）**：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。  
![è¿™é‡Œå†™å›¾ç‰‡æè¿°][SouthEast]

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。  
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。  
**事务可以通过以下语句显式给记录集加共享锁或排他锁**：

共享锁（S）：SELECT \* FROM table\_name WHERE … LOCK IN SHARE MODE。  
排他锁（X）：SELECT \* FROM table\_name WHERE … FOR UPDATE。  
用SELECT … IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

##### \[5\] 间隙锁 #####

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。  
举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

Select * from  emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

**InnoDB使用间隙锁的目的**，一方面是为了**防止幻读**，以满足相关隔离级别的要求，对于上面的例子，要是不使 用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了**满足其恢复和复制的需 要**。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

> 在mysql的innoDB存储引擎中，如果**更新操作**是针对一个区间的，那么它会**锁住这个区间内所有的记录**，比如update xxx where id between a and b那么它会锁住a到b之间所有记录，注意是所有记录，甚至这个记录并不存在也会被锁住，这个时候，如果另外一个连接需要插入一条记录到a到b之间，那么它就必须等到上一个事务结束。

##### \[5\] MVCC：乐观锁 #####

> https://www.jianshu.com/p/56fa361e0d94

mysql的innodb采用的是行锁，而且采用了多版本并发控制来提高读操作的性能。 什么是多版本并发控制呢 ？

**MVCC：multiversion concurrency control**。**其实就是在每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号**。 而每一个事务在启动的时候，**都有一个唯一的递增的版本号**。

1、**在插入操作时** ： **记录的创建版本号就是事务版本号**。比如我插入一条记录, 事务id 假设是1 ，那么记录如下：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>create version</th> 
   <th>delete version</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>test</td> 
   <td>1</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

2、**在更新操作的时候**，采用的是先标记旧的那行记录为已删除，并且删除版本号是事务版本号，然后插入一行新的记录的方式。比如，针对上面那行记录，事务Id为2 要把name字段更新

update table set name= 'new_value' where id=1;

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>create version</th> 
   <th>delete version</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>test</td> 
   <td>1</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>new_value</td> 
   <td>2</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

3、**删除操作的时候**，就把事务版本号作为删除版本号。比如

delete from table where id=1;

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>create version</th> 
   <th>delete version</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>new_value</td> 
   <td>2</td> 
   <td>3</td> 
  </tr> 
 </tbody> 
</table>

4、**查询操作**：  
从上面的描述可以看到，在查询时要符合以下两个条件的记录才能被事务查询出来：

1.  **删除版本号 大于 当前事务版本号，就是说删除操作是在当前事务启动之后做的**。
2.  **创建版本号 小于或者等于 当前事务版本号 ，就是说记录创建是在事务中（等于的情况）或者事务启动之前**。

> 创建版本号<=当前事务版本号<删除版本号

1.  这样就保证了各个事务互不影响。从这里也可以体会到一种提高系统性能的思路，就是： 通过版本号来减少锁的争用。
2.  另外，只有read-committed和 repeatable-read 两种事务隔离级别才能使用MVCC  
    read-uncommited由于是读到未提交的，所以不存在版本的问题
3.  而serializable 则会对所有读取的行加锁。

##### \[6\] 什么时候会加锁？ #####

在数据库增删改查四种操作中\*\*，insert、delete和update都是会加排它锁\*\*(Exclusive Locks)的，select不会加锁，只有显式声明才会加锁:

*  select: 即最常用的查询，是不加任何锁的
 *  select … lock in share mode: 会加共享锁(Shared Locks)
 *  select … for update: 会加排它锁

[SouthEast]: /images/20230208/a6843d4a044347408fd789494921289f.png