数据化时代,随着计算机用户不断增加,数据的流动性也在不断增加,涉及到临界数据的时候,会出现数据不一致的问题
例子:我花了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;