0


mysql如何加行锁

一、概述

InnoDB 引擎是支持行级锁的,而 MyISAM 引擎并不支持行级锁,所以后面的内容都是基于 InnoDB 引擎的。当我们使用delete、update进行数据库删除、更新的时候,数据库会自动加上行锁。但是,行锁有时也会失效。
数据库版本:8.0.32。

二、InnoDB锁类型

InnoDB一共有四种锁:共享锁(读锁/S锁)、排他锁(写锁/X锁)、意向共享锁(IS锁)和意向排他锁(IX锁)。其中共享锁与排他锁属于行级锁,另外两个意向锁属于表级锁。

共享锁(读锁/S锁):若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放S锁。
排他锁(写锁/X锁):若事务T对数据对象A加上X锁,则只允许T读取和修改A,其他事务不能再对A加作何类型的锁,直到T释放A上的X锁。
意向共享锁(IS锁):事务T在对表中数据对象加S锁前,首先需要对该表加IS(或更强的IX)锁。
意向排他锁(IX锁):事务T在对表中的数据对象加X锁前,首先需要对该表加IX锁。
比如:

SELECT ... FROM T1  LOCK IN SHARE MODE

语句首先会对表T1加IS锁,成功加上IS锁后才会对数据加S锁。

同样,

SELECT ... FROM T1  FOR UPDATE

语句首先会对表T1加IX锁,成功加上IX锁后才会对数据加X锁。

三、mysql加行锁的方法:

  • 1.select … for update 对读取的记录加独占(排他)锁;
  • 2.select … lock in share mode 对读取的记录加共享锁;
  • 3.使用delete 自动加独占锁;
  • 4.使用update 自动加独占锁;

锁的规则:
共享锁(S锁)满足读读共享,读写互斥。独占锁(X锁)满足写写互斥、读写互斥。

四、行级锁种类:

  • 1.读已提交隔离级别下,行级锁的种类只有 记录锁; 简单来说,就是把数据表中的某条记录锁住;
  • 2.在可重复读隔离级别下,行级锁的种类除了有记录锁,还有间隙锁(目的是为了避免幻读),所以行级锁的种类主要有三类:

第一种:
Record Lock,记录锁,也就是仅仅把一条记录锁上;
记录锁包括:共享锁和独占锁。

第二种:
Gap Lock,间隙锁,锁定一个范围,但是不包含记录本身;
间隙锁之间是相互兼容的,即两个事务可以同时持有包含共同间隙范围的间隙锁,并不存在互斥关系。

第三种:
Next-Key Lock 临键锁:Record Lock + Gap Lock 的组合,锁定一个范围,并且锁定记录本身。

  • 何时使用行锁,何时产生间隙锁? 1.只使用唯一索引查询,并且只锁定一条记录时,innoDB会使用行锁。 2.只使用唯一索引查询,但是检索条件是范围检索,或者是唯一检索但检索结果不存在(试图锁住不存在的数据)时,会产生 Next-Key Lock(临键锁)。 3.使用普通索引检索时,不管是何种查询,只要加锁,都会产生间隙锁(Gap Lock)。 4.同时使用唯一索引和普通索引时,由于数据行是优先根据普通索引排序,再根据唯一索引排序,所以也会产生间隙锁。

开启mysql(8.0.32)命令行窗口,开启事务执行加锁:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> update t_run_work_order set work_title='' where id='7742';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看加锁情况:

mysql> select * from performance_schema.data_locks;
+--------+-----------------------------------------+-----------------------+-----------+----------+---------------+------------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID                          | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME      | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------------------------+-----------------------+-----------+----------+---------------+------------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 140295790075904:1271:140295710762800    |              80343359 |      2548 |      225 | sfms-efms     | t_run_work_order | NULL           | NULL              | NULL       |       140295710762800 | TABLE     | IX            | GRANTED     | NULL      |
| INNODB | 140295790075904:213:6:2:140295710759696 |              80343359 |      2548 |      225 | sfms-efms     | t_run_work_order | NULL           | NULL              | PRIMARY    |       140295710759696 | RECORD    | X,REC_NOT_GAP | GRANTED     | 7742      |
+--------+-----------------------------------------+-----------------------+-----------+----------+---------------+------------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)

字段说明:


ENGINE 表使用的存储引擎,这里是InnoDB
ENGINE_TRANSACTION_ID 事务ID
OBJECT_SCHEMA 加锁的表空间,这里的表空间是test
OBJECT_NAME 加锁的表名,这里是user
INDEX_NAME 加锁的索引名称,表级锁为null,行级锁为加锁的索引名称。这里PRIMARY表示是主键索引上添加锁。
LOCK_TYPE 锁类型:TABLE对应表级锁,RECORD对应行级锁。
LOCK_MODE 加锁模式,对应具体锁的类型,比如:IX 意向排他锁,X,GAP 排他间隙锁。
LOCK_STATUS 锁的状态,GRANTED 已获取,WAITING 等待中
LOCK_DATA 加锁的数据,这里的7742表示,在主键索引值为7742的记录上加锁。由于加的是记录锁,这里锁定的主键值为7742的记录。
 

LOCK_MODE 加锁模式:

在这里插入图片描述

五、行锁失效

如果where条件中没有使用索引或者索引失效,行锁会升级为表锁,此时,相当于行锁失效,对数据表进行操作时,会锁住整张表。下一篇,我们再讨论索引失效的场景。
标签: mysql 数据库 sql

本文转载自: https://blog.csdn.net/goodmentc/article/details/130400717
版权归原作者 oyezitan 所有, 如有侵权,请联系我们删除。

“mysql如何加行锁”的评论:

还没有评论