mysql默认事务下乐观锁解决方案

一、sql语句

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `subject` varchar(20) NOT NULL COMMENT '科目',
  `score` int(11) NOT NULL DEFAULT '0' COMMENT '分数',
  `version` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_key` (`name`,`subject`) USING BTREE COMMENT '唯一索引'
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES (1, '阿飞', '语文', 99, 0);
INSERT INTO `student` VALUES (2, '阿飞', '数学', 98, 0);
INSERT INTO `student` VALUES (3, '阿飞', '英语', 79, 0);
INSERT INTO `student` VALUES (4, '雪梨', '语文', 89, 1);
INSERT INTO `student` VALUES (5, '雪梨', '数学', 78, 0);
INSERT INTO `student` VALUES (6, '雪梨', '英语', 67, 0);
INSERT INTO `student` VALUES (7, '小月', '语文', 100, 2);
INSERT INTO `student` VALUES (8, '小月', '数学', 99, 0);
INSERT INTO `student` VALUES (9, '小月', '英语', 98, 0);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

二、问题及解决方案：

1、mysql的事务默认隔离级别为可重复读（repeatable-read）：

1、 一个事务读取到另一个事务提交前后的数据保持不变
2、 如果两个事务对同一条数据做修改，都未提交时候，晚修改的会阻塞住，直到早提交的事务commit或rollback了才能继续执行

2、模拟程序：高并发下可能出问题的事务语句

BEGIN;
# 一个事务中查询的数据有可能在执行update的时候已经被别的事务修改并提交了，
# 所以这时候如果执行更新语句(更新内容依赖查询结果的数据)会出现非预期错误；
# 如下语句；
SELECT s.score FROM student s  WHERE s.`name` = '阿飞' and s.`subject` = '数学';
# 下面语句中score是从查询语句获取，此时的score有可能是错误的数据，已经被之前的事务所修改了
# 比如 开始查询的score是100，然后另一个事务修改成了99并提交，此时再执行update拿的score依然是100，然后修改成了99
# 实际上预期的值应该是98
UPDATE student s SET s.score = s.score-1 WHERE s.`name` = '阿飞' and s.`subject` = '数学';
# 此时提交不是预期结果
commit;

# 解决办法：乐观锁思想(版本号控制，即数据库加上版本号version字段)
BEGIN;
# 每次查询的时候都查出版本号version； 
SELECT s.score,s.`version` FROM student s  WHERE s.`name` = '阿飞' and s.`subject` = '数学';
# 程序中score是从查询语句获取，更新的时候条件带上版本号
UPDATE student s SET s.score = s.score-1 , s.`version`= s.`version`+1  
WHERE s.`name` = '阿飞' and s.`subject` = '数学' and s.`version` = 0;
# 有可能更新失败，因为版本号已经升级，不会出现错误结果
commit;