醋醋百科网

Good Luck To You!

2025年07月18日

InnoDB锁机制之行级锁(一)（innodb行锁怎么用）

一、行级锁介绍

行锁的是mysql锁中粒度最小的一种锁，因为锁的粒度很小，所以发生资源争抢的概率也最小，并发性能最大，但是也会造成死锁，每次加锁和释放锁的开销也会变大。

1、使用MySQL行级锁的两个前提

  1. 使用 innoDB 引擎
  2. 开启事务 (隔离级别为 Repeatable Read )

2、InnoDB行锁的类型

  • 共享锁（S）：当事务对数据加上共享锁后, 其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
  • 排他锁（X）：如果事务T对数据A加上排他锁后，则其他事务不能再对数据A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

二、加锁的方式

  • InnoDB引擎默认更新语句，update,delete,insert 都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果要加可以使用下面的方式:
  • 加共享锁（S）：select * from table_name where ... lock in share mode;
  • 加排他锁（x）：select * from table_name where ... for update;

锁兼容

  • 共享锁只能兼容共享锁, 不兼容排它锁；
  • 排它锁互斥共享锁和其它排它锁；

三、行锁测试

1、数据准备

#创建表
CREATE TABLE innodb_lock(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20),
  age INT,
  INDEX idx_name(NAME)
);

# 插入数据
INSERT INTO innodb_lock VALUES(NULL,'a', 13);
INSERT INTO innodb_lock VALUES(NULL,'b', 23);
INSERT INTO innodb_lock VALUES(NULL,'c', 33);
INSERT INTO innodb_lock VALUES(NULL,'d', 43);
insert into innodb_lock values(null,'b',53); 
insert into innodb_lock values(null,'c',63); 
insert into innodb_lock values(null,'d',73);

2、打开两个窗口,并开启手动提交事务(提交或者回滚事务就会释放锁)

#开启MySQL数据库手动提交
SET autocommit=0;

3、窗口1中, 对id为1的数据进行更新操作,但是不commit. 执行之后,在当前窗口查看表数据,发现被修改了。

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from innodb_lock;
+----+------+------+
| id | NAME | age |
+----+------+------+
| 1 | aaa | 13 |
| 2 | b | 23 |
| 3 | c | 33 |
| 4 | d | 43 |
+----+------+------+

4、在窗口2 查看表信息, 无法看到更新的内容

mysql> select * from innodb_lock;
+----+------+------+
| id | NAME | age |
+----+------+------+
| 1 | a | 13 |
| 2 | b | 23 |
| 3 | c | 33 |
| 4 | d | 43 |
+----+------+------+
4 rows in set (0.00 sec)

总结: 在有写锁的情况下,一个事务不允许读取到另一个事务没有提交的内容。避免了脏读的发生。

5、窗口1开启事务, 对innodb_lock表 id=1的这一行进行读取。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from innodb_lock where id = 1;
+----+------+------+
| id | NAME | age |
+----+------+------+
| 1 | aaa | 13 |
+----+------+------+

6、窗口2开启事务, 对id=1的数据进行修改,然后提交事务

begin;
update innodb_lock set name = 'a' where id=1;
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock;
+----+------+------+
| id | NAME | age |
+----+------+------+
| 1 | a | 13 |
| 2 | b | 23 |
| 3 | c | 33 |
| 4 | d | 43 |
+----+------+------+
4 rows in set (0.00 sec)

7、窗口2提交事务后,窗口1再次查询,还是之前的查询结果

mysql> select * from innodb_lock where id = 1;
+----+------+------+
| id | NAME | age |
+----+------+------+
| 1 | aaa | 13 |
+----+------+------+

总结: 在有写锁的情况下，一个事务内多次读取同一数据的结果始终保持一致,避免了不可重复读的问题。

四、InnoDB行级锁升级为表级锁

InnoDB中的行级锁是「对索引加的锁，在不通过索引查询数据的时候，InnoDB就会使用表锁」。

但是通过索引查询的时候是否使用索引，还要看Mysql的执行计划，Mysql的优化器会判断是一条sql执行的最佳策略。

若是Mysql觉得执行索引查询还不如全表扫描速度快, 那么Mysql就会使用全表扫描来查询，这是即使sql语句中使用了索引, 最后还是执行为全表扫描，加的是表锁。

下面是行级锁升级为表级锁的原因:

  • 未使用到索引
  • 索引失效
  • 索引字段重复率过高

接下来对上面的几种情况进行一下演示:

1、未使用索引导致行级锁升级为表级锁

-- 窗口1中,设置手动提交,更新数据成功,但是不提交
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

-- 窗口2中,开启事务,然后对id为6的数据进行修改,但是发生阻塞
mysql> update innodb_lock set name = 'wangwu' where id = 6;
-- 阻塞......

2、索引失效导致行级锁升级为表级锁

-- 窗口1中,设置手动提交,更新数据成功,但是不提交
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> update innodb_lock set name = 'lisi' where name like '%c';

-- 窗口2中,开启事务,然后对id为6的数据进行修改,但是发生阻塞
mysql> update innodb_lock set name = 'wangwu' where id = 6;

3、索引字段重复率过高,导致索引失效

-- 窗口1,执行查询 并添加排它锁
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock where name = 'a' for update;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | a | 13 |
| 2 | a | 23 |
| 3 | a | 33 |
| 4 | a | 43 |
| 5 | a | 43 |
| 6 | a | 53 |
+----+------+------+
6 rows in set (0.00 sec)

-- 窗口2
mysql> update innodb_lock set name = 'wangwu' where id = 7;
-- 发生阻塞,原因是name字段虽然有索引,但是字段值重复率太高,MySQL放弃了该索引

-- 窗口1 开启事务,查询name = b的数据,并加入排它锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock where name = 'b' for update;
Empty set (0.00 sec)

-- 窗口1 开启事务,查询name = d的数据,并加入排它锁,查询到了结果,没有阻塞的原因是name字段的索引生效了,还是行级的锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock where name = 'd' for update;
+----+------+------+
| id | name | age |
+----+------+------+
| 8 | d | 73 |
+----+------+------+
1 row in set (0.00 sec)

-- 通过Explain进行分析,可以看到两条SQL的索引使用情况
mysql> explain select * from innodb_lock where name = 'b';
+----+-------------+-------------+------------+------+---------------+----------
+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key
| key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+----------
+---------+-------+------+----------+-------+
| 1 | SIMPLE | innodb_lock | NULL | ref | idx_name | idx_name
| 63 | const | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------+----------
+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from innodb_lock where name = 'a';
+----+-------------+-------------+------------+------+---------------+------+---
------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key |
key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---
------+------+------+----------+-------------+
| 1 | SIMPLE | innodb_lock | NULL | ALL | idx_name | NULL |
NULL | NULL | 8 | 75.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---
------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

五、查询SQL的锁测试

1、查询时的排他锁测试

select语句加排他锁方式 : select * from table_name where ... for update;

  • for update 的作用: for update 是在数据库中上锁用的，可以为数据库中的行上一个排他锁。存在高并发并且对于数据的准确性很有要求的场景，可以选择使用for update。
  • for update 的注意点: for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。

2、在窗口1中, 首先开启事务, 然后对 id为1 的数据进行排他查询

begin;
select * from innodb_lock where id = 1 for update;

3、在窗口2中，对同一数据分别使用 排他查 和 共享锁 两种方式查询

-- 排他锁查询
select * from innodb_lock where id = 1 for update;

-- 共享锁查询
select * from innodb_lock where id = 1 lock in share mode;

我们看到开了窗口2的排他锁查询和共享锁查询都会处于阻塞状态，因为id=1的数据已经被加上了排他锁，此处阻塞是等待排他锁释放。

4、如果只是使用普通查询,我们发现是可以的

select * from innodb_lock where id = 1;

5、查询时的共享锁测试

  • 添加共享锁: select * from table_name where ... lock in share mode;
  • 事务获取了共享锁，在其他查询中也只能加共享锁,但是不能加排它锁。

6、窗口1 开启事务, 使用共享锁查询 id = 2 的数据 ,但是不要提交事务

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

mysql> select * from innodb_lock where id = 2 lock in share mode;
+----+------+------+
| id | NAME | age |
+----+------+------+
| 2 | b | 23 |
+----+------+------+

7、窗口2 开启事务, 使用普通查询和共享锁查询 id = 2 的数据 ,是可以的

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

mysql> select * from innodb_lock where id = 2 lock in share mode;
+----+------+------+
| id | NAME | age |
+----+------+------+
| 2 | b | 23 |
+----+------+------+

8、加排他锁就查不到，因为排他锁与共享锁不能存在同一数据上。

select * from innodb_lock where id = 2 for update;
-- 阻塞

标签：锁机制 

作者:alicucu , 分类:技术文章 , 浏览:5 , 评论:0

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言