MySQL行锁机制

柔光的暖阳◎ 2023-06-11 12:22 135阅读 0赞

## 行锁（InnoDB) ##

mysql 默认自动commit； oracle默认不会自动commit；  
暂时关闭自动commit：**set autocommit = 0;**，还有start transaction; 或者begin;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70]  
总结：

1.  如果会话X对某条数据A进行DML操作（自动关闭commit情况下），则其他会话必须等待会话X结束事务（commit或rollback）后，才能对数据A进行操作(可以查询)。
2.  表锁是通过unlock tables解锁，也可以通过事务解锁（行锁升级为表锁时）; 行锁是通过事务解锁。

## DDL和DML的区别： ##

1.  DDL：数据操纵语言，对数据库中的数据进行一些简单操作，如增删改查等；
2.  DML：对数据库中的某些对象（database，table）进行管理，如create，alter，drop等。

## 行锁注意的事项： ##

1.  如果操作没有走索引，则行锁会转为表锁；  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 1]  
    升级为表锁后，会话2更新被阻塞  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 2]  
    原因：**如果查询条件进行模糊匹配，则索引失效，行锁升级为表锁。**
2.  行锁的一种特殊情况：间隙锁，值在范围内，但却不存在  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 3]  
    **该表中没有id = 7的数据，当我在会话1更新id为2到9范围的数据后，在会话2插入id = 7的操作，发现被锁住了。**  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 4]  
    **分析原因**：  
    MySQL会自动给间隙加锁->间隙锁，即给本例子id = 7的数据加间隙锁（行锁）；对于行锁，如果有where条件，实际加锁的范围是where后面的整个范围。

**行锁分析：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 5]

1.  Innodb\_row\_lock\_current\_waits ：当前正在等待锁的数量
2.  Innodb\_row\_lock\_time： 等待总时长，从系统启动到现在一共等待的时间
3.  Innodb\_row\_lock\_time\_avg： 平均等待的时长，从系统启动到现在平均等待的时长
4.  Innodb\_row\_lock\_time\_max：最大等待时长，从系统启动到现在最大等待时长
5.  Innodb\_row\_lock\_waits：等待次数，从系统启动到现在一共等待的次数

## 行锁总结： ##

1.  缺点：比表锁性能消耗大；
2.  优点：并发能力高，效率高
3.  因此建议：高并发建议用InnoDB，否则使用MyISAM。

## 通过for update 对查询语句进行加锁。 ##

**select \* from t\_linelock where id = 3 for update;**  
此刻会话2被锁住：  
![在这里插入图片描述][2019103021540965.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70]: /images/20230531/40493497bb7741ac90b37b127ad22989.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 1]: /images/20230531/c5f9b59eafb74ff4b7970b195547a822.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 2]: /images/20230531/4a957caf2aeb4fc8889e8a6691f45b72.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 3]: /images/20230531/23ac3af1623f4a859ed68750e0a335a4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 4]: /images/20230531/446e7db86ea24b0bad25450daf0995f5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 5]: /images/20230531/1cdac19f13494abcba9d40df05d7c7c3.png
[2019103021540965.png]: /images/20230531/fcd08ca50dee415d80a0a2b3e84a7142.png