mysql innodb 行锁_MySQL InnoDB行锁

在 MySQL 中，InnoDB 行锁通过给索引上的索引项加锁来实现，如果没有索引，InnoDB 将通过隐藏的聚簇索引来对记录加锁。

InnoDB 支持 3 种行锁定方式：

行锁(Record Lock)：直接对索引项加锁。

间隙锁(Gap Lock)：锁加在索引项之间的间隙，也可以是第一条记录前的“间隙”或最后一条记录后的“间隙”。

Next-Key Lock：行锁与间隙锁组合起来用就叫做 Next-Key Lock。 前两种的组合，对记录及其前面的间隙加锁。

默认情况下，InnoDB 工作在可重复读(默认隔离级别)下，并且以 Next-Key Lock 的方式对数据行进行加锁，这样可以有效防止幻读的发生。

Next-Key Lock 是行锁与间隙锁的组合，这样，当 InnoDB 扫描索引项的时候，会首先对选中的索引项加上行锁(Record Lock)，再对索引项两边的间隙(向左扫描扫到第一个比给定参数小的值， 向右扫描扫到第一个比给定参数大的值， 然后以此为界，构建一个区间)加上间隙锁(Gap Lock)。如果一个间隙被事务 T1 加了锁，其它事务不能在这个间隙插入记录。

要禁止间隙锁的话，可以把隔离级别降为读已提交(READ COMMITTED)，或者开启参数 innodb_locks_unsafe_for_binlog。

注意：以上语句描述的情况，与 MySQL 所设置的事务隔离级别有较大的关系。

开启一个事务时，InnoDB 存储引擎会在更新的记录上加行级锁，此时其它事务不可以更新被锁定的记录。下面我们以示例1演示此过程。

例 1

下面的语句需要在两个命令行窗口中执行。为了方便理解，我们分别称之为 A 窗口和 B 窗口。

分别在 A 窗口和 B 窗口中查看事务隔离级别，A 窗口和 B 窗口的事务隔离级别需要保持一致。

A 窗口查看隔离级别的 SQL 语句和运行结果如下所示：

mysql> SHOW VARIABLES LIKE 'tx_isolation' \G

*************************** 1. row ***************************

Variable_name: tx_isolation

Value: REPEATABLE-READ

1 row in set, 1 warning (0.03 sec)

B 窗口查看隔离级别 SQL 语句和运行结果如下所示：

mysql> SHOW VARIABLES LIKE 'tx_isolation' \G

*************************** 1. row ***************************

Variable_name: tx_isolation

Value: REPEATABLE-READ

1 row in set, 1 warning (0.03 sec)

结果显示，A窗口和 B窗口的事务隔离级别都为 REPEATABLE-READ。

在 A窗口中开启一个事务，并修改 tb_student 表，SQL 语句和运行结果如下：

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE test.tb_student SET age ='30' WHERE id = 1;

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

在 B窗口中也开启一个事务，并修改 tb_student 表，SQL 语句和运行结果如下：

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE test.tb_student SET age ='30' WHERE id = 1;

会发现 UPDATE 语句一直在执行。这时我们在 A 窗口中提交事务。

mysql> COMMIT;

Query OK, 0 rows affected (0.01 sec)

这时我们发现 B 窗口中的 UPDATE 语句执行成功。

mysql> UPDATE test.tb_student SET age ='30' WHERE id = 1;

Query OK, 0 rows affected (1 min 2.78 sec)

Rows matched: 1 Changed: 0 Warnings: 0

查询 tb_student 表中的数据，SQL 语句和运行结果如下：

mysql> SELECT * FROM test.tb_student;

+----+------+------+------+------+

| id | name | age | sex | num |

+----+------+------+------+------+

| 1 | 张三 | 30 | 男 | 4 |

| 2 | 李四 | 12 | 男 | 4 |

| 3 | 王五 | 13 | 女 | 4 |

| 4 | 张四 | 13 | 女 | 4 |

| 5 | 王四 | 15 | 男 | 4 |

| 6 | 赵六 | 12 | 女 | 4 |

+----+------+------+------+------+

6 rows in set (0.00 sec)

如以上实例所示，当有不同的事务同时更新同一条记录时，另外一个事务需要等待另一个事务把锁释放，此时查看 MySQL 中 InnoDB 存储引擎的状态如下：

mysql> SHOW ENGINE innodb status \G

......

------------

TRANSACTIONS

------------

Trx id counter 19556

Purge done for trx's n:o < 19554 undo n:o < 0 state: running but idle

History list length 12

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 283572223909376, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 19555, ACTIVE 54 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 14, OS thread handle 4568, query id 886 localhost ::1 root updating

UPDATE test.tb_student SET age ='30' WHERE id = 1

------- TRX HAS BEEN WAITING 54 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 197 page no 3 n bits 80 index PRIMARY of table `test`.`tb_student` trx id 19555 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0

0: len 4; hex 80000001; asc ;;

1: len 6; hex 000000004c62; asc Lb;;

从上面运行结果可以看出，SQL 语句 UPDATE test.tb_student SET age ='30' WHERE id = 1 在等待，RECORD LOCKS space id 197 page no 3 n bits 80 index PRIMARY of table `test`.`tb_student` trx id 19555 lock_mode X locks rec but not gap 表示锁住的资源，locks rec but not gap 代表锁住的是一个索引，不是一个范围。

“MySQL thread id 14, OS thread handle 4568, query id 886 localhost ::1 root updating”表示第 2 个事务连接的 ID 为 14，当前状态为正在更新，同时正在更新的记录需要等待其它事务将锁释放。当超过事务等待锁允许的最大时间，此时会提示“ERROR 1205(HY000):Lock wait timeout exceeded; try restarting transaction" 及当前事务执行失败，则自动执行回滚操作。

MySQL 数据库采用 InnoDB 模式，默认参数 innodb_lock_wait_timeout 设置锁等待的时间是 50s，一旦数据库锁超过这个时间就会报错。可通过以下命令查看当前数据库锁等待的时间。

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| innodb_lock_wait_timeout | 120 |

+--------------------------+-------+

1 row in set, 1 warning (0.02 sec)

下面演示了 InnoDB 间隙锁的实现机制。

例 2

下面在保证 A 窗口和 B 窗口的前提下，将 tb_student 表中的 id 字段设为外键，并开启一个事务，修改 tb_student 表中 id 为 1 的 age。SQL 语句和运行结果如下：

mysql> ALTER TABLE test.tb_student ADD unique key idx_id(id);

Query OK, 0 rows affected (0.17 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE test.tb_student SET age ='31' WHERE id = 1;

Query OK, 0 rows affected (0.01 sec)

Rows matched: 1 Changed: 0 Warnings: 0

在 B 窗口中开启一个事务，修改 tb_student 表中 id 为 2 的 age，SQL 语句和运行结果如下：

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE test.tb_student SET age ='28'WHERE id=2;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

这时分别提交 A窗口和 B窗口的事务。

mysql> COMMIT;

Query OK, 0 rows affected (0.01 sec)

查询 tb_student 表的数据，SQL 语句和运行结果如下：

mysql> SELECT * FROM test.tb_student;

+----+------+------+------+------+

| id | name | age | sex | num |

+----+------+------+------+------+

| 1 | 张三 | 31 | 男 | 4 |

| 2 | 李四 | 28 | 男 | 4 |

| 3 | 王五 | 13 | 女 | 4 |

| 4 | 张四 | 13 | 女 | 4 |

| 5 | 王四 | 15 | 男 | 4 |

| 6 | 赵六 | 12 | 女 | 4 |

+----+------+------+------+------+

6 rows in set (0.00 sec)

在上述示例中，由于 InnoDB 行级锁为间隙锁，只锁定需要的记录，因此 B窗口中的事务可以更新其它记录，两个事务之间互不影响。