前置信息
隔离级别:REPEATABLE-READ(查看隔离级别语句:SELECT @@global.tx_isolation;)
查看数据库中锁情况的语句:SELECT * FROM information_schema.INNODB_LOCKS;
-- G事务
初始化语句
CREATE TABLE `lxx_test` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(32) CHARACTER SET utf8mb4 NOT NULL COMMENT '姓名',
`age` int(3) NOT NULL COMMENT '年龄',
PRIMARY KEY (`id`),
FULLTEXT KEY `index-1` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1
INSERT INTO lxx_test(NAME,age)VALUES('lxx1',1);
INSERT INTO lxx_test(NAME,age)VALUES('lxx2',2);
INSERT INTO lxx_test(NAME,age)VALUES('lxx3',3);
测试案例
-- A事务
START TRANSACTION;
UPDATE lxx_test SET age=11 WHERE NAME='lxx1';
-- 去执行B事务
COMMIT;
-- B事务
START TRANSACTION;
UPDATE lxx_test SET age=21 WHERE NAME='lxx1';
COMMIT;
结果:
B事务会阻塞,说明事务A添加了X锁,且在commit之后才释放锁。
=============================================
-- C事务
START TRANSACTION;
SELECT * FROM lxx_test WHERE NAME='lxx1';
-- 去执行D事务
COMMIT;
-- D事务
START TRANSACTION;
UPDATE lxx_test SET age=31 WHERE NAME='lxx1';
COMMIT;
结果:
D事务不会阻塞,说明C事务版本读没有加S锁
=============================================
-- E事务
START TRANSACTION;
SELECT * FROM lxx_test WHERE NAME='lxx1' FOR UPDATE;
-- 去执行F事务
COMMIT;
-- F事务
START TRANSACTION;
UPDATE lxx_test SET age=41 WHERE NAME='lxx1';
COMMIT;
结果:
F事务阻塞,说明E事务当前读添加了X锁(使用查看锁的语句查看了是X锁,不是S锁),且commit之后才会释放
=============================================
-- G事务
START TRANSACTION;
SELECT * FROM lxx_test WHERE NAME='lxx1' LOCK IN SHARE MODE;
-- 去执行H事务
-- 执行 SELECT * FROM information_schema.INNODB_LOCKS;查看锁的情况
COMMIT;
-- H事务
START TRANSACTION;
SELECT * FROM lxx_test WHERE NAME='lxx1' FOR UPDATE;
COMMIT;
结果:
H事务阻塞,通过查看锁的语句,表中一个S锁,一个X锁。
只有显示的加S锁才会添加S锁。即select ... LOCK IN SHARE MODE;