五分钟了解Mysql的行级锁——《深究Mysql锁》（转）

怼烎@ 2022-01-30 11:31 229阅读 0赞

延伸阅读：  
[三分钟了解Mysql的表级锁][Mysql]  
[一分钟深入Mysql的意向锁][Mysql 1]

我们首先需要知道的一个大前提是：mysql的锁是由具体的存储引擎实现的。所以像Mysql的默认引擎MyISAM和第三方插件引擎 InnoDB的锁实现机制是有区别的。  
Mysql有三种级别的锁定：表级锁定、页级锁定、行级锁定

# 一、定义 #

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

*  每次锁定的是一行数据的锁机制就是行级别锁定(row-level)。行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的

# 二、优缺点 #

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

**1. 优点**

*  由于锁粒度小，争用率低，并发高。

**2. 缺点**

*  实现复杂，开销大。
 *  加锁慢、容易出现死锁

# 三、支持存储引擎 #

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

*  使用行级锁定的主要有InnoDB存储引擎，以及MySQL的分布式存储引擎NDBCluster

# 四、行级锁类型 #

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

InnoDB的行级锁定同样分为两种类型：**共享锁**和**排他锁**，而在锁定机制的实现过程中为了让行级锁定和表级锁定**共存**，InnoDB也同样使用了**意向锁（表级锁定）**的概念，也就有了意向共享锁和意向排他锁这两种。

意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个**意向共享锁**。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个**意向排他锁**。

意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。所以，可以说InnoDB的`锁定模式实际上可以分为四种`：`共享锁（S）`，`排他锁（X）`，`意向共享锁（IS`）和`意向排他锁（IX）`

锁模式的兼容性：  
![这里写图片描述][70]

# 五、行级锁定实现方式 #

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

InnoDB行锁是通过给索引上的`索引项加锁`来实现的。所以，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。其他注意事项：

*  在不通过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁。
 *  由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以即使是访问不同行的记录，如果使用了相同的索引键，也是会出现锁冲突的。
 *  当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
 *  即便在条件中使用了索引字段，但具体是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

**隐式加锁**：

*  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方式获得排他锁。

**InnoDB如何加表锁**：

在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。

SET AUTOCOMMIT=0;
    LOCK TABLES t1 WRITE, t2 READ, ...;
    [do something with tables t1 and t2 here];
    COMMIT;
    UNLOCK TABLES;
     
      
     
      
       1
       2
       3
       4
       5

既然都用表锁了，怎么不选择MyISAM引擎呢！

# 六、间隙锁（Next-Key锁） #

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

#### 1. 间隙锁定义： ####

Innodb的锁定规则是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。 Innodb的这种锁定实现方式被称为“ NEXT-KEY locking” （`间隙锁`），因为Query执行过程中通过范围查找的话，它会锁定整个范围内所有的索引键值，即使这个键值并不存在。

例：假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

mysql> select * from emp where empid > 100 for update;
     
      
     
      
       1

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

#### 2. 间隙锁的缺点： ####

*  间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害
 *  当Query无法利用索引的时候， Innodb会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；
 *  当Quuery使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所指向的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；
 *  当Query在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定

#### 3 . 间隙锁的作用： ####

*  防止幻读，以满足相关隔离级别的要求。
 *  为了数据恢复和复制的需要。

#### 4. 注意 ####

*  在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。
 *  InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。

# 七、查看行级锁争用情况 #

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

执行SQL:`mysql> show status like 'InnoDB_row_lock%';`

mysql> show status like 'InnoDB_row_lock%'; +-------------------------------+-------+
    | Variable_name | Value | +-------------------------------+-------+
    | InnoDB_row_lock_current_waits | 0     |
    | InnoDB_row_lock_time | 0 | | InnoDB_row_lock_time_avg | 0 | | InnoDB_row_lock_time_max | 0 | | InnoDB_row_lock_waits         | 0     |
    +-------------------------------+-------+
     
      
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10

如果发现锁争用比较严重，还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。如：

设置监视器：`mysql> create table InnoDB_monitor(a INT) engine=InnoDB`;

查看：`mysql> show engine InnoDB status`;

停止查看：`mysql> drop table InnoDB_monitor`;

具体参考：[InnoDB Monitor][]

# 八、死锁 #

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

**什么是死锁**：你等我释放锁，我等你释放锁就会形成死锁。

**如何发现死锁**： 在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在

**解决办法**：

*  回滚较小的那个事务
 *  在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。

**判断事务大小**：事务各自插入、更新或者删除的数据量

**注意**：

*  当产生死锁的场景中涉及到不止InnoDB存储引擎的时候，InnoDB是没办法检测到该死锁的，这时候就只能通过锁定超时限制参数InnoDB\_lock\_wait\_timeout来解决。

# 九、优化行级锁定 #

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

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

（1）要想合理利用InnoDB的行级锁定，做到扬长避短，我们必须做好以下工作：  
a)尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定；  
b)合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；  
c)尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；  
d)尽量控制事务的大小，减少锁定的资源量和锁定时间长度；  
e)在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本。

（2）由于InnoDB的行级锁定和事务性，所以肯定会产生死锁，下面是一些比较常用的减少死锁产生概率的小建议：  
a)类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；  
b)在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；  
c)对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

原文地址： https://blog.csdn.net/zcl\_love\_wx/article/details/81983267

[Mysql]: https://blog.csdn.net/zcl_love_wx/article/details/81977447
[Mysql 1]: https://blog.csdn.net/zcl_love_wx/article/details/82015281
[70]: /images/20220130/d9509573845c4bbeab4dcefb04b9e20b.png
[InnoDB Monitor]: https://blog.csdn.net/zyz511919766/article/details/50147283