第一步:创建测试表 test,如下:
CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`now` datetime DEFAULT NULL COMMENT '时间',
`year_month` binary(7) DEFAULT '\0\0\0\0\0\0\0',
`stock` mediumint(11) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
模拟前提条件:
MySQL版本:5.7.26
事务级别:REPEATABLE-READ
第二步:模拟用户A制单、审核
START TRANSACTION;
select `stock`,`now` from test where id = 1 lock in share mode;
UPDATE test SET `stock` = `stock`-50,`now`=NOW() where id = 1;
COMMIT;
第三步:模拟用户B制单、审核
START TRANSACTION;
SELECT `stock`,`now` from test where id = 1 lo