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

数据化时代，随着计算机用户不断增加，数据的流动性也在不断增加，涉及到临界数据的时候，会出现数据不一致的问题

例子：我花了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;