create table zjw_test(
a int,
b int,
primary key(a),
key(b)
)
insert into zjw_test(a,b)values(1,1),(3,4),(5,8)
sessionA
begin
select * from zjw_test where a>2 for update;(先执行,不提交)
commit;
sessionB
select * from zjw_test where a=1 for update(sessionA 执行中,sessionB 阻塞)
commit;
-------------------------------------------------------
sessionA
begin
select * from zjw_test where a>3 for update;(先执行,不提交)
commit;
sessionB
select * from zjw_test where a=1 for update(sessionA 执行中,sessionB 不阻塞)
commit;
sessionA
----------------------------
sessionA
BEGIN
SELECT * FROM zjw_test WHERE a=3 FOR UPDATE;
COMMIT;
sessionB
BEGIN
SELECT * FROM zjw_test WHERE a=3 FOR UPDATE;(sessionA 执行中,sessionB 不阻塞)
COMMIT;
--------------------------
sessionA
BEGIN
SELECT * FROM zjw_test WHERE a=3 FOR UPDATE;
COMMIT;
sessionB
BEGIN
insert into zjw_test(a,b)values(2,5)(顺利插入)
COMMIT;
---------------------------------------------
sessionA
BEGIN
SELECT * FROM zjw_test WHERE b=5 FOR UPDATE;
COMMIT;
sessionB
BEGIN
INSERT INTO zjw_test(a,b)VALUES(7,7)(不能插入成功)
COMMIT;
-----------------------------------
sessionA
BEGIN
SELECT * FROM zjw_test WHERE b=5 FOR UPDATE;
COMMIT;
sessionB
BEGIN
INSERT INTO zjw_test(a,b)VALUES(7,3)(插入成功)
COMMIT;
这里面主要涉及了record lock,gap lock,next-key-lock
Mysql锁之事
最新推荐文章于 2023-09-09 16:16:51 发布