详细介绍MySQL InnoDB的 三 种行锁定方式

在 MySQL中，InnoDB 不是直接锁住磁盘上的数据行，而是锁住该行数据所对应的索引项。即使你没有为表定义任何主键（PRIMARY KEY），InnoDB 也会自动为你生成一个隐藏的、名为 DB_ROW_ID 的聚簇索引来组织数据。所以，每张 InnoDB 表至少有一个索引，如果你在没有二级索引（即你自己创建的索引）的列上进行查询，InnoDB 就不得不退而求其次，使用这个隐藏的聚簇索引（或主键）来锁定记录。

InnoDB 支持 三种行锁定方式，分别是记录锁（Record Lock）、间隙锁（Gap Lock）、临键锁（Next-Key Lock）。

下面有两个例子分别具体介绍记录锁 (Record Lock)、间隙锁 (Gap Lock)：

1. 记录锁 (Record Lock)

准备两个窗口A、B。（同时启动同时A、B窗口需要保持同级事务隔离级别）

进入A窗口看看A窗口的隔离级别：

SHOW VARIABLES LIKE 'transaction_isolation';

在B窗口也是同理，结果是一样的，不一样你就手动调成一样。

在A窗口准备一张表格，我准备的dotcpp_user如下：

在A窗口中开启事务并修改dotcpp_user表：

BEGIN;
UPDATE dotcpp_user SET hobby = "睡懒觉" WHERE id = 4;

同样的操作在B窗口进行：

BEGIN;
UPDATE dotcpp_user SET hobby = "睡懒觉" WHERE id = 4;

be like:

此时没有Query OK这一行提示，UPDATE一直在执行，这是为什么呢？

你不语，只是打开A窗口提交事务：

COMMIT;

回到B窗口发现此时执行成功：

看一下修改了没有：（此时第四个用户爱好变成“睡懒觉”）

原来，多个事务同时更新一条记录，需要等待前一个事务把锁释放才能进行修改，这里B窗口一直没有成功修改记录直到A窗口提交记录后才能修改，这就有力说明这一点！

此时我们查看MySQL 中 InnoDB 存储引擎的状态：

SHOW ENGINE innodb status;

得到结果(重点看这部分）：

TRANSACTIONS
------------
Trx id counter 2405
Purge done for trx's n:o < 2401 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 284587497428864, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284587497428088, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284587497427312, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 2404, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 36, OS thread handle 2256, query id 186 localhost ::1 Dotcpp updating
UPDATE dotcpp_user SET hobby = "?????" WHERE id = 4
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4 page no 4 n bits 72 index PRIMARY of table `dotcpp`.`dotcpp_user` trx id 2404 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 00000000095c; asc      \;;
 2: len 7; hex 02000001210151; asc     ! Q;;
 3: len 12; hex 646f74637070557365723034; asc dotcppUser04;;
 4: len 9; hex e79da1e68792e8a789; asc          ;;
------------------
---TRANSACTION 2403, ACTIVE 35 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 20, OS thread handle 2232, query id 184 localhost ::1 Dotcpp
--------

对于B窗口的这个修改语句：“UPDATE dotcpp_user SET hobby = "?????" WHERE id = 4“，修改需要申请一个排他锁”lock_mode X locks rec but not gap waiting“，这是因为InnoDB 的规定：为了修改一行数据，InnoDB 必须为该行加上排他锁（X Lock）。排他锁的特性是：如果其他事务已经持有了这行数据的任何锁（共享锁S或排他锁X），则当前事务必须等待。

当超过事务等待锁允许的最大时间，此时会报错（1205:超过锁等待超时时间；尝试重新启动事务）：

及当前事务执行失败，则自动执行回滚操作。

MySQL 数据库采用 InnoDB 模式，默认参数 innodb_lock_wait_timeout 设置锁等待的时间是 50s，一旦数据库锁超过这个时间就会报错。

我们可以通过这个变量innodb_lock_wait_timeout查看当前数据库锁等待的时间：

SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

2. 间隙锁 (Gap Lock)

准备A、B两个窗口，在A窗口为dotcpp_user表中的id 字段添加唯一性限制，同时开启事务并修改id=1的用户爱好为"睡懒觉";

这里添加主键索引注意id是否曾设置主键哈，有的就自行跳过，不知道的就输入 "show index from yourTable;" 看看你的id是否设置主键。

ALTER TABLE dotcpp.dotcpp_user ADD unique key  index_id(id);
BEGIN;
UPDATE dotcpp.dotcpp_user SET hobby = '睡懒觉'  WHERE id = 1;

在B窗口开启一个事务并修改:id=2的用户爱好为"睡懒觉";

BEGIN;
UPDATE dotcpp.dotcpp_user SET hobby = '睡懒觉'  WHERE id = 2;

此时分别提交A、B窗口的事务，查看表dotcpp_user内容：

SELECT * FROM dotcpp.dotcpp_user;

可以看到两个id=1、2的用户都变成了"睡懒觉"，由此我们知道，InnoDB 的行级锁采用间隙锁机制，仅锁定目标记录，因此不同事务可以并发更新互不冲突的数据行。