mysql - 2.锁的概念(表级锁)

原创已于 2025-07-23 23:53:30 修改 · 234 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2019-03-28 18:18:43 首次发布

基础同时被 2 个专栏收录

29 篇文章

订阅专栏

mysql

23 篇文章

订阅专栏

本文通过实验详细介绍了在MySQL 5.6版本中使用InnoDB存储引擎时，表级锁的具体行为，包括不同会话间进行读写操作时的相互影响及等待情况。

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

先举个例子：我花了5块钱，账户还有20块钱，（扣费：先select多少钱，根据select结果来update）发了2次请求，因为网络问题，select2次都一样导致扣了2次费，那肯定不行的呀：人民利益不可侵犯。

这就要用到锁了，保证只能有一个修改操作能成功。--> 数据库的锁有表级锁和行级锁

本篇文章主要介绍表级锁

首先来实现表级锁：-------以下测试是在mysql-5.6 版本，InnoDB存储引擎下测试，myisam下测试了几个，情况一致

分析表级锁的读写情况：

锁类别	本session				另外session
	lock 表		非lock 表		lock 表		非lock 表
	查	增删改	查	增删改	查	增删改	查	增删改
read	OK	ERROR	ERROR	ERROR	OK	WAIT	OK	OK
write	OK	OK	ERROR	ERROR	WAIT	WAIT	OK	OK

read--本session-- lock表 -- 查

read--本session-- lock表 -- 增删改

read--本session--非lock表 -- 查

read--本session--非lock表 -- 增删改

[SQL]LOCK TABLE mylock READ

受影响的行: 0
时间: 0.000s

[SQL]INSERT INTO myunlock VALUES(4,"g");
[Err] 1100 - Table 'myunlock' was not locked with LOCK TABLES

[SQL]UPDATE myunlock set `name` = 'd' WHERE id = 1;
[Err] 1100 - Table 'myunlock' was not locked with LOCK TABLES

[SQL]DELETE FROM myunlock WHERE id = 1;
[Err] 1100 - Table 'myunlock' was not locked with LOCK TABLES

______________________________________________________________________________________________

read--另外session-- lock表 -- 查

read--另外session-- lock表 -- 增删改

[SQL]INSERT INTO mylock VALUES(3,"c");  ----进入wait状态

[SQL]UNLOCK TABLES    此时如果表读锁释放

受影响的行: 0
时间: 0.000s

[SQL]INSERT INTO mylock VALUES(3,"c");     ---wait被唤醒，执行插入，返回结果
受影响的行: 1
时间: 61.262s


其他一样效果
[SQL]UPDATE mylock set `name` = 'd';
[SQL]DELETE FROM mylock WHERE id = 1;

read--另外session-- 非lock表 -- 查

[SQL]SELECT * from myunlock;
受影响的行: 0
时间: 0.000s

read--另外session--非lock表 -- 增删改

[SQL]INSERT INTO myunlock VALUES(4,"g");
受影响的行: 1
时间: 0.005s

[SQL]UPDATE myunlock set `name` = 'd' WHERE id = 1;
受影响的行: 1
时间: 0.004s

[SQL]DELETE FROM myunlock WHERE id = 1;
受影响的行: 1
时间: 0.017s

————————————————————————————————————————————————————————

LOCK TABLE mylock WRITE

write--本session-- lock表 -- 查

[SQL]SELECT * FROM mylock;
受影响的行: 0
时间: 0.000s

write--本session-- lock表 -- 增删改

[SQL]INSERT INTO mylock VALUES(4,"c");
受影响的行: 1
时间: 0.016s

[SQL]UPDATE mylock set `name` = 'd';
受影响的行: 4
时间: 0.016s

[SQL]DELETE FROM mylock WHERE id = 1;
受影响的行: 1
时间: 0.015s

write--本session-- 非lock表 -- 查

[SQL]SELECT * from myunlock;
[Err] 1100 - Table 'myunlock' was not locked with LOCK TABLES

write--本session-- 非lock表 -- 增删改

[SQL]INSERT INTO myunlock VALUES(4,"g");
[Err] 1100 - Table 'myunlock' was not locked with LOCK TABLES

[SQL]UPDATE myunlock set `name` = 'd' WHERE id = 1;
[Err] 1100 - Table 'myunlock' was not locked with LOCK TABLES

[SQL]DELETE FROM myunlock WHERE id = 1;
[Err] 1100 - Table 'myunlock' was not locked with LOCK TABLES

————————————————————————————————————————————————————————

write--另外session-- lock表 -- 查

write--另外session-- lock表 -- 增删改（全都阻塞）

[SQL]SELECT * FROM mylock;
[SQL]UPDATE mylock set `name` = 'd';
[SQL]DELETE FROM mylock WHERE id = 1;

write--另外session-- 非lock表 -- 查

[SQL]SELECT * from myunlock;
受影响的行: 0
时间: 0.001s

write--另外session-- 非lock表 -- 增删改

[SQL]INSERT INTO myunlock VALUES(5,"g");
受影响的行: 1
时间: 0.014s

[SQL]UPDATE myunlock set `name` = 'd' WHERE id = 1;
受影响的行: 0
时间: 0.000s

[SQL]DELETE FROM myunlock WHERE id = 1;
受影响的行: 0
时间: 0.001s

最后将我运行的sql语句贴上，只是做一个参考

session--1

LOCK TABLE mylock READ
LOCK TABLE mylock WRITE

SELECT * FROM mylock;
INSERT INTO mylock VALUES(4,"c");
UPDATE mylock set `name` = 'd';
DELETE FROM mylock WHERE id = 1;

SELECT * from myunlock;
INSERT INTO myunlock VALUES(4,"g");
UPDATE myunlock set `name` = 'd' WHERE id = 1;
DELETE FROM myunlock WHERE id = 1;

UNLOCK TABLES

-------------------------------------------------------------------------------------
session--2

SELECT * FROM mylock;
INSERT INTO mylock VALUES(6,"c");
UPDATE mylock set `name` = 'd';
DELETE FROM mylock WHERE id = 1;

SELECT * from myunlock;
INSERT INTO myunlock VALUES(5,"g");
UPDATE myunlock set `name` = 'd' WHERE id = 1;
DELETE FROM myunlock WHERE id = 1;