es悲观锁悲观锁的使用

转载

编程小匠人 2024-07-26 00:26:23

悲观锁，乐观锁，mysql，InnoDB

最近一次写了一个接口在并发场景出现了数据覆盖的问题，记得从一开始学数据库的时候就没有深入了解MySQL的锁和事务这块，每次一想到这块就总有一些疑惑，特此记录一下使用场景以便后期回顾。

业务场景

学生答题每道题有多个空，每个空的正确与否以逗号分隔的方式保存在一个字段中，批改人员多次调用批阅接口分别批改每道题不同的空，在并发场景如果没有加锁就会导致多个空的批改记录被覆盖。
表结构大体如下:

CREATE TABLE `test_answer` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `test_id` bigint(20) NOT NULL,
  `question_id` bigint(20) NOT NULL,
  `student_id` bigint(20) NOT NULL,
  `correct_detail` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_test_question_student_id` (`test_id`,`question_id`,`student_id`) USING BTREE,
  KEY `idx_student_id` (`student_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

需要执行的sql大体如下

begin;
#a
select correct_detail from test_answer where test_id = 1 and question_id = 1 and student_id = 1;
#b
update test_answer set correct_detail = '0,1' where id = 1;
commit;

我们先假定答案的原始批改记录是0,0，2次调用分别要将第一第二个空都改成1，当并发批改同一条记录时都先执行到a而没有执行b并提交时，因为MySQL的默认隔离级别是repeatable read(可重复读)，所以两个事务读取的都是0,0(两个事务均未提交)，就会导致本来想要得到的结果是1,1，但却变成了0,1或1,0，如下图

事务A	事务B
begin	begin
select correct_detail from test_answer where test_id = 1 and question_id = 1 and student_id = 1;
	select correct_detail from test_answer where test_id = 1 and question_id = 1 and student_id = 1;
update test_answer set correct_detail = '0,1' where id = 1;
	update test_answer set correct_detail = '1,0' where id = 1;
commit	commit

悲观锁与乐观锁

解决上面的场景有几种方式，如果不单说从数据库角度考虑，可以基于消息保证唯一索引的记录线性执行；当然从数据库的角度除了将隔离级别设置为serializable级别以外，就是针对记录添加悲观锁与乐观锁了。

悲观锁

悲观锁方式的SQL如下

begin;
#a
select correct_detail from test_answer where test_id = 1 and question_id = 1 and student_id = 1 for update;
#b
update test_answer set correct_detail = '0,1' where id = 1;
commit;

使用悲观锁时因为查询的是唯一索引，所以是针对这条数据的行加排他锁(X锁)，此时其他事务无法读和写，只能等待这个事务结束或回滚，排他锁释放。(此处多说一句因为InnoDB的二阶段提交,所以每次加锁的时候是立即加锁而释放要等事务结束或回滚后才释放,所以这些加锁操作应该尽可能放在整个事务中尽可能靠后的位置,降低锁的时间)

乐观锁

乐观锁方式的SQL如下

begin;
#a
select correct_detail from test_answer where test_id = 1 and question_id = 1 and student_id = 1;
#b
update test_answer set correct_detail = '0,1' where id = 1 and correct_detail = '0,0';
commit;

乐观锁的方式因为查询时不会加锁所以可以提高并发,但是也产生了一个问题就是可能当两个事务并发执行时其中一个无法修改数据,因为需要修改的数据已经变化如上例当其他事务在b执行之前提交了事务那么b语句并不会更改这条记录。