表锁
建表
CREATE TABLE
mylock(
idINT (11) NOT NULL AUTO_INCREMENT,
NAMEVARCHAR (20) DEFAULT NULL, PRIMARY KEY (
id`)
);
INSERT INTO mylock (id,NAME) VALUES (1, ‘a’);
INSERT INTO mylock (id,NAME) VALUES (2, ‘b’);
INSERT INTO mylock (id,NAME) VALUES (3, ‘c’);
INSERT INTO mylock (id,NAME) VALUES (4, ‘d’);`
-
read锁
session1
lock TABLE mylock READ 给表加读锁 (1)
select * from mylock 查询表数据,正常 (2)
update mylock set name = ‘e’ where id = 1 无法进行操作 (4)
UNLOCK TABLES 释放锁 (6)
update mylock set name = ‘e’ where id = 1 无法进行操作 (7)正常更新session2
select * from mylock 查询表数据,正常 (3)
update mylock set name = ‘e’ where id = 1 无法进行操作 (5)
update mylock set name = ‘e’ where id = 1 正常更新(8) -
write锁
session1
lock TABLE mylock WRITE 给表加写锁 (1)
select * from mylock where id = 1 查询正常操作 (2)
update mylock set name = ‘f’ where id = 1 更新正常操作 (4)
UNLOCK TABLES 释放锁 (6)
session2
select * from mylock where id = 2 无法进行操作 (需要换成不同的ID,mysql自带了缓存) (3)
update mylock set name = ‘f’ where id = 2 无法进行操作 (5)
select * from mylock where id = 2 查询正常操作 (7)
update mylock set name = ‘f’ where id = 1 更新正常操作 (8)
- 总结
表共享读锁,顾名思义,读取数据session都能select,锁不释放的情况下,表没法进行更新和新增。
表独占写锁,占据锁的session可以对表进行 update,insert,select操作。锁不释放的情况下,其他session没法进行表进行操作。