MySQL(三)：详解Innodb锁机制

偏执的太偏执、 2023-10-03 08:56 1阅读 0赞

#### 1、前言 ####

可以通过检查Innodb\_row\_lock状态变量来分析行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';
    +-------------------------------+-------+
    | Variable_name                 | Value |
    +-------------------------------+-------+
    | Innodb_row_lock_current_waits | 0     |
    | Innodb_row_lock_time          | 1572 |
    | Innodb_row_lock_time_avg      | 1572 |
    | Innodb_row_lock_time_max      | 1572 |
    | Innodb_row_lock_waits         | 1     |
    +-------------------------------+-------+

注：如果Innodb\_row\_lock\_waits和Innodb\_row\_lock\_time\_avg的值比较高，说明锁争用比较严重。

#### 2、InnoDB的行锁模式及加锁方法 ####

##### 1）共享锁（读锁） #####

将数据变为只读形式，不能进行更新，即读取锁定。  
假如事务T对数据对象A加上读锁，则事务T可以读A 也能修改A；其他事务只能再对A加读锁，而不能加写锁，直到事务T释放A上的读锁。  
这样保证了其他事务可以读取A，但在T释放A上的读锁之前，不能对A做任何修改。

##### 2）排他锁（写锁） #####

允许获取到排他锁的事务更新数据，阻止其他事务获取相同数据集的共享读锁和排他写锁，即写入锁定。  
假如事务T对数据对象A加上写锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

##### 3）加锁方式 #####

MYSQL Innodb引擎默认的修改数据语句：update、delete、insert都会自动给涉及到的数据加上排他锁。

select操作默认不会加任何锁类型，可以使用select … for update 加排他锁，使用select … lock in share mode 加共享锁。

**所以加过排他锁的数据行，其他事务中是不能修改其数据的，也不能通过for update和lock in share mode锁的方式查询数据；但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。**

##### 4）Innodb行锁实现方式 #####

Innodb行锁是通过给索引上的索引项加锁来实现的，而Oracle是通过给数据块中对相应数据行加锁来实现的。

这也就意味着，只要有通过索引条件来检索数据，InnoDB才使用行级锁，否者InnoDB将使用表锁！

#### 3、意向共享锁和意向排他锁 ####

意向锁是InnoDB数据操作之前自动加的，不需要用户干预。

##### 1）意向共享锁： #####

表示事务准备给数据行加入共享锁，即一个数据行在加共享锁之前必须要先取得该表的IS锁。

##### 2）意向排他锁： #####

表示事务准备给数据行加入排他锁，即一个数据行在加排他锁之前必须要先取得该表的IX锁。

#### 4、加锁情况案例 ####

**1. 创建有索引表：**

create table table_with_index(id int,name varchar(10)) engine=innodb;
    alter table table_with_index add index id(id);
    insert into table_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

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

**2. 创建无索引表：**

create table table_no_index(id int,name varchar(10)) engine=innodb;
    insert into table_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0_size_16_color_FFFFFF_t_70 1]  
**注意：** 表中的ID不是主键，Innodb会自动生成一个6位的主键（隐藏字段）。

##### 1）不走索引条件查询时，使用表锁 #####

session1只给一行加了排他锁，但是session2在请求其他行的排他锁的时候，会出现锁等待。原因是在没有索引的情况下，innodb只能使用表锁。

<table> 
 <thead> 
  <tr> 
   <th align="center">session1</th> 
   <th align="center">session2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">set autocommit=0<br>select * from table_no_index where id = 1;</td> 
   <td align="center">set autocommit=0<br>select * from table_no_index where id =2</td> 
  </tr> 
  <tr> 
   <td align="center">select * from table_no_index where id = 1 for update</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center">select * from table_no_index where id = 2 for update;</td> 
  </tr> 
 </tbody> 
</table>

session1：对id=1记录行进行加锁  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0_size_16_color_FFFFFF_t_70 2]  
session2：对id=2记录行加写锁 读，由于table\_no\_index表没有索引 且 查询条件中不包含索引列，所以session1已经对表加了锁，session2需要等待锁释放。由于MySQL的innodb中，事务有超时设置，所以session2等待了一段时间session1释放锁，一直等不到，就抛出了事务超时异常。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0_size_16_color_FFFFFF_t_70 3]

##### 2）走索引条件查询时，使用行锁 #####

<table> 
 <thead> 
  <tr> 
   <th align="center">session1</th> 
   <th align="center">session2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">set autocommit=0<br>select * from table_with_index where id = 1;</td> 
   <td align="center">set autocommit=0<br>select * from table_with_index where id =2</td> 
  </tr> 
  <tr> 
   <td align="center">select * from table_with_index where id = 1 for update;</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center">select * from table_with_index where id = 2 for update;</td> 
  </tr> 
 </tbody> 
</table>

session1:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0_size_16_color_FFFFFF_t_70 4]  
session2:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0_size_16_color_FFFFFF_t_70 5]

#### 5、总结 ####

*  InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
 *  在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

**减少锁冲突和死锁的措施：**

*  尽量使用索引访问数据，使加锁更精确，避免表锁出现，从而减少锁冲突的概率；
 *  给记录集显式加锁时，一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
 *  不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少发生死锁的概率；
 *  尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响；
 *  不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
 *  对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210622170558518.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210622170645900.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210622171322161.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20210622171717911.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/2021062217224189.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20210622172312665.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NhaW50bW0=,size_16,color_FFFFFF,t_70