月度归档:2019年09月

MySQL事务隔离级别

MySQL的InnoDB存储引擎实现了事务,行级锁,优化主键查询,支持外键等等。事务遵循ACID特征:

  • A:原子性(atomicity),一个事务要么完全成功,要么完全失败
  • C:一致性(consistency),一个事务对数据的修改必须符合数据库预定义的约束,比如字段类型、外键约束等等
  • I:隔离性(isolation),多个事务同时进行时,事务之间数据的读写的隔离
  • D:持久性(Durability),事务提交成功后,对数据的修改即永久保存的,不会因为系统故障丢失

MySQL默认开启autocommit,除非碰到start transaction/commit/rollback等主动事务管理。事务保证了对于写的先后顺序问题,即A事务先开始变更,B事务的变更必须等A事务完成。此外事务也对读提供不容程度的隔离,与隔离级别有关,包括不同的等级:读未提交(Read uncommitted)、读已提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。串行化为最高隔离级别,在这个隔离级别下面,读写都是顺序的,不会出现这些问题,但效率低下,MySQL默认隔离级别为可重复读。在其他隔离级别下面可能会出现不同的问题

隔离级别 脏读 不可重复读 幻读
读未提交(Read Uncommitted) yes yes yes
读已提交(Read Committed) no yes yes
可重复读(Repeatable Read) no no yes
串行化(Searializable) no no no

假设有一个表test结构为

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `total` int(11) DEFAULT 0,
  `status` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`id`, `total`, `status`) VALUES (1, 0, 1);

脏读是指A事务读到B事务未提交的事务,B事务可能取消更改,那么A事务读取的数据不可靠

操作顺序 事务A 事务B
1 start transaction; start transaction;
2 update total set total=100 where id=1;
3  select total from test where id=1;
4 rollback;
5  commit;

结果

隔离级别 事务A 事务B
读未提交(Read Uncommitted) 由于可以读取到未提交的数据,total值为100 数据未更新
读已提交(Read Committed) 由于A事务读取数据时,事务B尚未提交,total值为原始数据 数据未更新
可重复读(repeatable read) 由于A事务开始前total值未发生改变,total值为原始数据 数据未更新

不可重复读是指A事务内,读取的记录数据不恒定,两次读取同一记录的值不一样,

操作顺序 事务A 事务B
1 start transaction; start transaction;
2 select total from test where id=1;
3 update test set total=100 where id=1;
4 commit;
5 select total from test where id=1;
6  commit;

结果

隔离级别 事务A 事务B
读未提交(Read Uncommitted) 由于读取数据时不管是否提交,两次读取的total值不一样 数据已更新
读已提交(Read Committed) 由于A事务读取数据时,事务B已提交,两次读取的total值不一样 数据已更新
可重复读(repeatable read) 由于A事务开始前total值未发生改变,total值为原始数据,两次读取的total值一样 数据已更新

幻读是指A事务内读取的记录跟实际更改的记录不一致,比如查询表内所有符合条件的记录,并使结果加100

操作顺序 事务A 事务B
1 start transaction; start transaction;
2 select * from test;
3 insert into `test` (`id`, `total`, `status`) values (2,0, 1);
4 commit;
5 update test set total=total+100;
6 select * from test;
7  commit;

结果

隔离级别 事务A 事务B
读未提交(Read Uncommitted) 由于事务A开始时id为2的记录尚未存在,于是更新所有数据;但由于事务B插入数据,于是事务A也更新事务B插入的数据;A事务第二次读取数据时,读出了事务B的数据   数据插入成功,但随后被事务A更新
读已提交(Read Committed) 由于A事务第一次读取数据时,id为2的记录尚未存在,于是更新所有数据;但由于事务B插入数据,于是事务A也更新事务B插入的数据;A事务第二次读取数据时,读出了事务B的数据 数据插入成功,但随后被事务A更新
可重复读(repeatable read) 由于A事务开始前total值未发生改变,id为2的记录尚未存在,于是更新所有数据;但由于事务B插入数据,于是事务A也更新事务B插入的数据;A事务第二次读取数据时,并没有读到事务B的数据 数据插入成功,但随后被事务A更新

或者当数据库里面没有记录时则增加

操作顺序 事务A 事务B
1 start transaction; start transaction;
2 select * from test;
3 insert into `test` (`id`, `total`, `status`) values (3, 0, 1);
4 commit;
5 insert into `test` (`id`, `total`, `status`) values (3, 0, 1);
6  select * from test;
7  commit;

结果

隔离级别 事务A 事务B
读未提交(Read Uncommitted) 由于事务A读取记录时,id为3的记录不存在,于是插入数据;但由于事务B插入数据,于是事务A插入失败;A事务第二次读取时,读取到id为3的数据 数据插入成功
读已提交(Read Committed) 由于A事务读取数据时,id为3的记录不存在,于是插入数据;但由于事务B插入数据,于是事务A插入失败;A事务第二次读取时,读取到id为3的数据 数据插入成功
可重复读(repeatable read) 由于A事务开始前id为3的记录尚未存在,于是插入数据;但由于事务B插入数据,于是事务A插入失败;A事务第二次读取时,未读取到id为3的数据 数据插入成功

可重复读与读已提交是互斥的,区别在于是否可以读取到已提交的变更,譬如Oracle的隔离级别为读已提交,可以通过 SET TRANSACTION ISOLATION LEVEL READ COMMITTED更改。

除了串行化,其他隔离级别对读是不加锁的,这可能会造成一种情况:事务A读取了数据然后做其他事情了(进入循环或耗时计算),事务B也读取了同一行数据并先提交变更,此后事务A也提交变更但没有重新获取最新值,此时事务B的变更丢失(被覆盖),即更新丢失

操作顺序 事务A 事务B
1 start transaction; start transaction;
2 select id,total,status from test where id=1;  select status from test where id=1;
3 update test set status=2 where id=1;
4 commit;
5 update test set total=200, status=1 where id=1;
6  select * from test where id=1;
7  commit;

结果

隔离级别 事务A 事务B
读未提交(Read Uncommitted) 由于事务A第一次读取记录时status为1,A事务更新时,事务B已提交,A事务提交变更重新改写status为1。重新读取记录最新数据可以解决 更新丢失
读已提交(Read Committed) 由于A事务第一次读取记录时status为1,A事务更新时,事务B已提交,A事务提交变更重新改写status为1。重新读取记录最新数据可以解决 更新丢失
可重复读(repeatable read) 由于A事务开始前id为1的status为1,A事务提交变更覆盖B事务的变更,重新改写status为1。重新读取记录最新数据不能解决。 更新丢失

在隔离级别为串行化的情况下可解决更新丢失,因为它对读也加锁,另一事务的读操作必须等待当前事务完成。可以在查询里面使用SELECT…FOR UPDATE为当前事务记录加锁,这样其他事务读取该记录也必须等待,即拿到最新值。其他事务也可以使用NOWAIT快速失败或者SKIP LOCKED跳过对该记录的查询。

SELECT…FOR UPDATE申请的锁为排他锁,锁有不同类型,比如行锁,区间锁,next-key锁等。此外SELECT…FOR SHARE可以加读锁(共享锁),即本次事务获得锁了,其他事务可以读,但是不能更新;如果其他事务先获得锁并开始更新,则本次事务等待。

对于更新丢失问题,可以将表拆小,这样更新的操作更加细致,比如status和total分开,减少不同进程更新数据的冲突;更新时带上更多条件,比如status=1条件,失败则返回或重试;更前再次刷新数据,比较cache中的版本;其他手段加锁等等。

SELECT…FOR UPDATE对记录加锁,要注意不同事务之间避免互相等待,造成死锁,比如不同先后为不同记录申请锁

操作顺序 事务A 事务B
1 start transaction; start transaction;
2 select * from test where id=1 for update;
3 select * from test where id=2 for update;
4 select * from test where id=2 for update;
5 select * from test where id=1 for update;
6  deadlock; deadlock;

这样会造成事务A、B互相等待对方事务完成(释放)超时。或者A事务对记录加读锁后等待写锁,而B事务获得写锁了,等待读锁,也会造成死锁

操作顺序 事务A 事务B
1 start transaction; start transaction;
2 select * from test where id=1 for update;
3 insert into `test` (`id`, `total`, `status`) values (4, 0, 1);
4 insert into `test` (`id`, `total`, `status`) values (5, 0, 1);
5 select * from test where id=1 for update;
6  deadlock; deadlock;

这个问题应该通过合理的程序设计,先避免不同程序对同一记录竞争加锁;分解大事务为小事务,加速事务提交;减少不必要的锁的申请;当需要排他锁时不应先申请共享锁;降级事务隔离等级为读已提交等等。

参考资料:
深入学习MySQL事务:ACID特性的实现原理
解析MySQL事务隔离级别
MySQL 四种事务隔离级的说明
MySQL 锁机制
【BAT面试题系列】面试官:你了解乐观锁和悲观锁吗?