MySQL 数据库锁理论（六）

Dim_Jerry

墨衍会员 · AI 创作全网分发码龄8年

关注

原创最新推荐文章于 2026-07-24 08:14:37 发布 300 阅读 AI 写同款· GEO 优化 ›

MYSQL-创建数据库 CREATE DATABASE （十一） MYSQL创建数据库语法 CREATE DATABASE 阅读详情

9. 数据库锁理论

根据操作类型，锁可分为
- 读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而并不相互影响。
- 写锁(互斥锁)：当前写操作在没有完成之前，它会阻断其他写锁和读锁。
根据数据操作的粒度划分，锁可分为
- 表锁(偏读)：
- 行锁(偏写)：
- 页锁：

1. 创建表锁演示实例

建表 SQL：引擎选择 myisam

create table mylock (
    id int not null primary key auto_increment,
    name varchar(20) default ''
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

select * from mylock;

mylock 表中的测试数据
```
mysql> select * from mylock;
```
查看当前数据库中表的上锁情况：show open tables;，In_user 的值0 表示未上锁。
添加读/写锁

使用命令：lock table 表名1 read(write) [,表名2 read(write)]

为mylock表添加读锁，即lock table mylock read;

添加之后，我们发现，依然可以读取mylock的数据。

虽然可以读取数据，但是修改数据是不允许的。修改数据时，光标不断的在闪烁。

接着，我们将读锁改为写锁，即 lock table mylock write；

添加之后，我们发现，读取数据时会被阻塞，光标不断在闪烁。

最后，解锁，我们可以使用命令： unlock tables;

2. 表锁案例总结1：

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）

对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对用一表的写请求，当前进程写操作时会报错。只有当读锁释放后，才会执行其他进程的写操作。
对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。

简而言之，就是读写锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6VMkQOnR-1606020997384)(…/…/AppData/Roaming/Typora/typora-user-images/image-20201121153502148.png)]

3. 如何分析表锁？

分析表锁，我们可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定：

我们可以通过命令: show status like ‘table_lock%’; 查看状态变量。

上图中的两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：

Table_locks_immediate：产生表级锁定的次数，标识可以立即获取锁的查询次数，每立即获取锁值加1；
Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次，锁值加1），此值高则说明存在着较严重的表级锁争用情况。

此外，MyISAM的读写锁调度是写优先，这也就是 MyISAM 不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量更新会使查询很难得到锁，从而造成永远阻塞。

4. 创建行锁演示实例

建表 SQL：**引擎选择 InnoDB **

CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;

INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');

CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);

查询 test_innodb_lock 表中的测试数据
因为我们的数据库使用的是版本 Server version: 8.0.18 MySQL Community Server - GPL。 MySQL版本在5以上默认使用的是innodb引擎，该引擎是支持事务的，每一个分号的输入即自动提交事务。因此我们要关闭自动提交，自己手动提交数据，即输入命令：

set autocommit=0; 。
会话1 进行修改数据，并暂时不提交事务。update test_innodb_lock set b='40001' where a=4;
会话1 查询数据，发现数据库数据发生了变化， a=4的值为4001
会话2 查询数据，发现数据并没有修改。
此时会话1还未提交，会话2想要修改a=4,即第4行数据，希望修改为4002 update test_innodb_lock set b='4002' where a=4; 。

光标在闪烁，说明该修改命令被阻塞了，会话2无法修改。
此时会话1 进行 commit，提交数据，并查询结果。

会话1的第4行的b已经修改为40001，会话2的修改语句执行完成。但会话2查询到的结果为会话2修改的值4002.
会话2也继续进行提交数据。
会话1为再次提交数据，读取到的仍然为旧的值。因为MySQL数据库的默认隔离级别为可重复读。
会话1提交数据，即可读到最新的数据了。
会话1修改第4行的数据，修改为4001；会话2修改第5行的数据，修改为5001；
我们可以发现，两个会话是可以同时提交成功的，说明会话1修改的行对会话2修改是没有影响的。这就是行锁的效率体现。

5. 无索引行锁升级为表锁

这个问题发生往往都很隐蔽，难以发现该错误的发生：

首先，我们查看我们的表的字段类型
在学索引的时候，我们都字段自动类型转换会导致索引失效。除次之外，还会导致行锁变为表锁。
举个栗子，我们在会话1中b的值为4001的记录修改a字段为401，

即 update test_innodb_lock set a=401 where b=‘4001’;

对于varchar类型的数据，我们必须加上引号，否则会产生神秘的问题。
如果有引号时，结果是很正常的
当我们把引号去掉时，再看看结果如何吧！！！

我们可以发现，会话1秒出结果，但是会话2的光标不断的在闪烁。。。。说明行锁已经升级为表锁了。
当会话1提交时，会话2将不再阻塞。

6. 间隙锁

MySQL InnoDB支持三种行锁定方式：

行锁（Record Lock）：锁直接加在索引记录上面。
间隙锁（Gap Lock）：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。
Next-Key Lock：行锁与间隙锁组合起来用就叫做Next-Key Lock。

查询 test_innodb_lock 的数据，查看存在哪些记录
我们很容易可以发现，缺少a为2的记录项。当我们的会话1需要修改 1<a<5的记录，b值修改为fafu时。会话2插入1条a=2,b=’2b’;
```
# 会话1
update test_innodb_lock set b='fafu' where a<5 and a>1;

# 会话2
insert into test_innodb_lock values(2,'2b');
```
通过图，我们很容易看出问题了。 a=2的记录项原本就不能存在，但最终还是被锁住了，而如果插入的数据项a 的范围不在1~5之间的话，此时就不会被锁住。
所以这重分的说明了，如果是范围查询，会存在间隙锁。
InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了user_id大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要

小结：

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件；当然，对一条不存在的记录加锁，也会有间隙锁的问题。

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁，而不会使用Next-Key Lock的方式，也就是说不会对索引之间的间隙加锁，仔细想想的话，这个并不难理解，大家也可以自己测试一下。

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

7. 如何锁定具体的某一行数据

大致可分为下面4个步骤

# 步骤1，开启
begin;
# 步骤2，锁定行
select * from test_innodb_lock where a=8 for update;
# 步骤3，进行数据修改
update ......
# 步骤4，提交
commit;

在会话1中锁定一行数据时，其他会话无法对该行记录继续修改操作。如果修改其他记录，则不会被阻塞

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uk9QH7Y7-1606020997403)(…/…/AppData/Roaming/Typora/typora-user-images/image-20201121185401229.png)]

8. 如何分析行锁？

通过检查 innoDB_row_lock 状态变量来分析系统上的行锁的争夺情况：

使用命令： show status like 'innodb_row_lock%';

对各个状态量的说明如下：

状态	描述
Innodb_row_lock_current_waits	当前正在等待锁定的数量
Innodb_row_lock_time	从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg	每次等待所花的平均时间
Innodb_row_lock_time_max	从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits	系统启动后到现在总共等待的次数

在这5个状态量中，我们最主要关注 Innodb_row_lock_time_avg（平均时间），Innodb_row_lock_waits（等待总次数），Innodb_row_lock_time（等待总时间）三个状态即可

收录于