主要是为了自己更好的理解事务和锁,做此记录
隔离级别:Repeatable Read
==========================================================
建表语句:
CREATE TABLE `lxx_test` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(32) NOT NULL COMMENT '姓名',
`age` int(3) NOT NULL COMMENT '年龄',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
==========================================================
-- A事务
START TRANSACTION;
INSERT INTO lxx_test(NAME,age)VALUES('lxx',2);
-- 去执行B事务
SELECT * FROM lxx_test WHERE NAME='lxx';
COMMIT;
-- B事务
START TRANSACTION;
INSERT INTO lxx_test(NAME,age)VALUES('lxx',3);
COMMIT;
结果:
B事务不会阻塞,可以执行
B事务提交之后A事务中的查询,查不到B事务提交的数据
==========================================================
-- C事务
START TRANSACTION;
UPDATE lxx_test SET age=16 WHERE NAME='lxx';
-- 去执行D事务
SELECT * FROM lxx_test WHERE NAME='lxx';
COMMIT;
-- D事务
START TRANSACTION;
UPDATE lxx_test SET age=17 WHERE NAME='lxx';
COMMIT;
结果:D事务会被阻塞(因为当前读)
==========================================================
-- E事务
START TRANSACTION;
SELECT * FROM lxx_test WHERE NAME='lxx';
-- 去执行F事务
SELECT * FROM lxx_test WHERE NAME='lxx';
COMMIT;
-- F事务
START TRANSACTION;
INSERT INTO lxx_test(NAME,age)VALUES('lxx',200);
COMMIT;
结果:E事务中的查询,看不到F事务提交的结果
==========================================================
-- G事务
START TRANSACTION;
SELECT * FROM lxx_test WHERE NAME='lxx';
-- 去执行H事务
SELECT * FROM lxx_test WHERE NAME='lxx';
COMMIT;
-- H事务
START TRANSACTION;
DELETE FROM lxx_test WHERE age=200;
COMMIT;
结果:H事务不会阻塞,G事务第二次查询看不到G事务提交的结果