InnoDB引擎-行锁和意向锁

最新推荐文章于 2025-04-30 23:17:44 发布

markliwei1

最新推荐文章于 2025-04-30 23:17:44 发布

阅读量1k

点赞数 1

分类专栏： Mysql

本文链接：https://blog.youkuaiyun.com/makeliwei1/article/details/82343856

版权

Mysql 专栏收录该内容

13 篇文章

订阅专栏

本文详细介绍了InnoDB存储引擎中的两种锁：行锁与意向锁。重点解释了共享锁和排他锁的工作原理及兼容性，并通过实例展示了不同类型的锁如何相互作用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

InnoDB提供了两种锁，一种是行锁另一种就是意向锁，其中行锁是针对当我们查询或者更新时使用到了索引的时候(这个使用索引不是我们在DDL中使用了索引，而是InnoDB根据默认的索引操作，或者是根据我们设置的索引规则，InnoDB在执行DDL语句的时候确实使用了索引)，那么这个时候就会使用行锁。当我们的DDL语句没有使用索引，而是进行的表的扫描的时候，这个时候InnoDB就会使用表锁(意向锁是一种页级、表级锁)。

一、行锁

InnoDB提供了行锁，当我们使用索引对数据库进行操作的时候，InnoDB就会使用行锁，这个时候锁定的是一行或者是一个范围，如果我们使用的唯一索引，那么锁定的一行，如果我们使用的一般索引，那么锁定的就是一个范围。InnoDB提供了两种形式的行锁

锁	功能
共享锁(S 锁)	对于读采用共享锁的方式
排它锁(X 锁)	对于更新或者插入的方式会使用排他锁

共享锁和排他锁的兼容情况

/	共享锁(S 锁)	排它锁(X 锁)
共享锁(S 锁)	兼容	不兼容
排他锁(X 锁)	不兼容	不兼容

如果事务A获取了r行的共享锁，那么如果我们事务B也要获取r行的共享锁，那么不会发生阻塞。如果我们的事务A获取了r行的排它锁，那么其他事务就不能再获取改行的任何锁了。

1.1、共享锁与共享锁兼容

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from test where id = 1 lock in share mode;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | zou  |   31 |
+----+------+------+
1 row in set (0.00 sec)


mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from test where id=1 lock in share mode;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | zou  |   31 |
+----+------+------+
1 row in set (0.00 sec)

1.2、共享锁和排他锁不兼容

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from test where id = 1 lock in share mode;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | zou  |   31 |
+----+------+------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set name='pi' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

1.3、排他锁和共享锁不兼容

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set name='ji' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from test where id = 2;
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | fu   |   23 |
+----+------+------+
1 row in set (0.00 sec)

mysql> select *from test where id = 2 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

1.4、排他锁和排他锁不兼容

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set name='wang' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set name='jiwang' where id =3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

二、表锁

在一个事务中，如果我们使用的DDL语句中没有使用索引，那么这个时候InnoDB就会使用页级或者是表级意向锁。

行锁共享锁和表共享兼容

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from test where id = 1 lock in share mode;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | qipa |   31 |
+----+------+------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from test where age = 31 lock in share mode;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | qipa |   31 |
+----+------+------+
1 row in set (0.00 sec)

行共享锁和表排它锁不兼容

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from test where id=1 lock in share mode;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | shahs |   31 |
+----+-------+------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set name='nida' where age = 31;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test set name='hanhan' where age = 27;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

行锁排他锁和表共享锁不兼容

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set name = 'haha' where id =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from test where age = 31 lock in share mode;

行锁排他锁和表排他锁不兼容

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set name='nidaye' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set name='hanhan' where age = 27;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

表共享锁和表共享锁兼容

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from test where age = 31 lock in share mode;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | zou  |   31 |
+----+------+------+
1 row in set (0.00 sec)

mysql> select *from test where age = 27 lock in share mode;
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | Li   |   27 |
+----+------+------+
1 row in set (0.00 sec)

表共享锁和表排它锁不兼容

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from test where age = 31 lock in share mode;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | haha |   31 |
+----+------+------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set name='quni' where age = 31;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

表排他锁和表排他锁不兼容

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set name='shahs' where age=31;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set name='baid' where age=27;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

表排他锁和行排他锁不兼容

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set name='qipa' where age = 31;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set name='yidi' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction