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条件中没有使用索引或者索引失效，行锁会升级为表锁，此时，相当于行锁失效，对数据表进行操作时，会锁住整张表。下一篇，我们再讨论索引失效的场景。