暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 分布式数据库的快照隔离级别案例对比分析

分布式数据库的快照隔离级别案例对比分析

数据库技术闲谈 2019-08-28
344



本文简单测试了一下ORACLE/MySQL/OceanBase的SERIALIZABLE
隔离级别、MySQL/TiDB的REPEATABLE READ
，供各位对数据库事务隔离级别感兴趣的朋友参考。

测试场景非常简单，就是并发更新同一个表的同一笔记录一个字段，然后先后提交，观察数据库行为和结果。

ORACLE


ORACLE支持SERIALIZABLE
隔离级别，但又不是严格遵守标准。ORACLE选择用快照技术实现SERIALIZABLE
隔离级别，降低了锁冲突。

  • 初始化测试表
create table t1(id number not null primary key, c1 number not null, gmt_modified date  default sysdate not null);
insert into t1(id,c1) values(1,101);
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select * from t1;

SQL>

        ID         C1 GMT_MODIFIED
---------- ---------- -------------------
         1        101 2019-08-28 10:32:08
  • 会话1
set transaction isolation level SERIALIZABLE;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
update t1 set c1=102 where id=1;
select * from t1;



  • 会话2 修改记录被阻塞:
set transaction isolation level SERIALIZABLE;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
update t1 set c1=103 where id=1;


会话2在 update的时候卡住，因为会话1对该记录持有锁。

  • 会话1 提交事务
commit;

  • 会话2 报错
SQL> update t1 set c1=102 where id=1;



update t1 set c1=102 where id=1
       *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction


SQL> SQL> SQL> SQL> select * from t1;

        ID         C1 GMT_MODIFIED
---------- ---------- -------------------
         1        101 2019-08-28 10:32:08

SQL>




会话2报错，因为update想修改的数据版本号已经发生变化。

OceanBase


  • 准备环境
create table t1(id number not null primary key, c1 number not null, gmt_modified date  default sysdate not null);
insert into t1(id,c1) values(1,101);

alter system set ob_trx_timeout=1200000000;
alter system set ob_trx_idle_timeout=1000000000;
alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select * from t1;


OceanBase的ORACLE租户的sql和事务都有相应的超时机制，默认超时时间比较短，这里设置长一些，不影响测试。



  • 会话1 修改记录不提交
begin;
set transaction isolation level SERIALIZABLE;
show session variables where variable_name in ('ob_trx_timeout','ob_trx_idle_timeout','ob_query_timeout','autocommit');
update t1 set c1=102 where id=1;





  • 会话2 修改同一笔记录被阻塞
begin;
set transaction isolation level SERIALIZABLE;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
show variables where variable_name in ('ob_trx_timeout','ob_trx_idle_timeout','ob_query_timeout');
update t1 set c1=103 where id=1;

ORA-30006: resource busy; acquire with WAIT timeout expired


会话2 在 update 时被阻塞，报锁超时(其实是SQL query 超时)。加大超时时间看看

alter session set ob_query_timeout=1000000000;
update t1 set c1=103 where id=1;




此时update继续被阻塞，直到会话1提交或者回滚释放锁。

  • 提交会话1 事务 
commit;
select * from t1;



  • 会话2 更新报错
obclient> update t1 set c1=103 where id=1;


ORA-08177: can't serialize access for this transaction




OceanBase检测到修改的数据快照版本发生变化，于是报错ORA-08177
，连错误号都跟ORACLE保持一致。

MySQL


REPEATABLE READ


  • 准备环境
create table t1(id bigint not null primary key, c1 bigint not null, gmt_modified datetime  default current_timestamp not null);
insert into t1(id,c1) values(1,101);
select * from t1;




  • 会话1
set session transaction isolation level  REPEATABLE READ;
set session autocommit=off;
update t1 set c1=102 where id=1;




  • 会话2
set session transaction isolation level  REPEATABLE READ;
set session autocommit=off;
update t1 set c1=103 where id=1;

MySQL里update记录时会申请锁，如果其他事务持有锁，当前事务会等待锁。MySQL锁等待有超时机制，加大超时时间，重试更新。

set session innodb_lock_wait_timeout=100000;
update t1 set c1=103 where id=1;

update会继续被阻塞，直到再次超时或者申请到锁。

  • 会话1

会话1 提交，释放锁。


  • 会话2

会话2 等到锁后也会更新提交。


从会话2的结果看，把会话1提交的结果覆盖了(事务2在事务1之后执行并提交)。在可重复读里隔离级别的事务里，读都是快照读，但是写或者select for update
那种读，其行为还是当前读(即需要读记录最新的数据并加锁)。如果记录上已经有锁了，这个写请求就会被阻塞。

把这个例子稍微改为c1累加，即可看出这个写是在最新已提交数据基础上修改。

  • 会话1:
set session transaction isolation level  REPEATABLE READ;
set session autocommit=off;
select * from t1;
update t1 set c1=c1+1 where id=1;
select * from t1;




  • 会话2 
set session transaction isolation level  REPEATABLE READ;
set session autocommit=off;
select * from t1;
update t1 set c1=c1+2 where id=1;
select * from t1;




加大超时时间，继续等待锁

set session innodb_lock_wait_timeout=100000;
update t1 set c1=c1+2 where id=1;


  • 会话1 事务提交
commit;
select * from t1;




  • 会话2
select * from t1;
commit;




从上面结果可以看到，会话2的update 在拿到锁之后是在最新数据(会话1提交的结果)基础上继续累加的。这个行为特征是否有问题要看业务场景。如果业务期望会话2读的结果是117。那么使用MySQL的可重复隔离级别就是有问题的。正确的做法还是使用两阶段加锁机制，在读出结果的时候就加锁。

SERIALIZABLE


  • 会话1
set session transaction isolation level SERIALIZABLE;
set session autocommit=off;
select * from t1;
update t1 set c1=c1+1 where id=1;
select * from t1;




  • 会话2
set session transaction isolation level SERIALIZABLE;
set session autocommit=off;
select * from t1;
update t1 set c1=c1+2 where id=1;
select * from t1;




加大 query超时时间，重试

set session innodb_lock_wait_timeout=100000;
select * from t1;


在select的时候就被阻塞了，可见 MySQL的serializable
隔离级别是加锁实现的。

  • 会话1 提交事务
commit;
select * from t1;



  • 会话2 继续
update t1 set c1=c1+2 where id=1;
select * from t1;
commit;




会话2 的 update 又被会话1的select阻塞了。退出会话1.会话2的update 继续，也是在最新的值上更新的。

由此可见MySQL的Serializable隔离级别里读和写都是加锁(读锁和写锁)，并发事务修改相同的数据时，事务跟事务的执行效果 等同顺序执行的。所以这种隔离级别最严格，但并发能力最差。

TiDB


TiDB是兼容MySQL的，事务隔离级别默认是REPEATABLE READ
。TiDB都是使用乐观锁。最近3.0才推出悲观锁功能。

乐观锁


  • 准备环境
create table t1(id bigint not null primary key, c1 bigint not null, gmt_modified datetime  default current_timestamp not null);
insert into t1(id,c1) values(1,101);
select * from t1;




  • 会话1
set session transaction isolation level  REPEATABLE READ;
set session autocommit=off;
update t1 set c1=102 where id=1;
select * from t1;




会话1将c1 更新为102 ，没有提交。

  • 会话2
set session transaction isolation level  REPEATABLE READ;
set session autocommit=off;
update t1 set c1=103 where id=1;
select * from t1;




前面说了，会话1将c1 更新为102 ，没有提交。而会话2却能够将c1更新为103时没有被阻塞并且成功了。可见TiDB的Update并不会立即加锁。

此时，假设会话1先提交，会话2后提交，实际结果会是怎样？

  • 会话1 提交
commit;
select * from t1;




提交后结果依然是102，这个符合会话1的目的。

  • 会话2 提交
commit;
select * from t1;




会话2 提交后的再读取结果依然是 103。直接覆盖了会话1修改的101结果。

再看会话1结果


第一次见到两个并发事务更新同一笔记录都提交成功了会非常奇怪，后来再看了一下是TiDB默认开启了事务重试机制。

再继续看一下 累加更新那个案例

  • 会话1:
set session transaction isolation level  REPEATABLE READ;
set session autocommit=off;
select * from t1;
update t1 set c1=c1+1 where id=1;
select * from t1;




会话1 将c1加1到104
  • 会话2 
set session transaction isolation level  REPEATABLE READ;
set session autocommit=off;
select * from t1;
update t1 set c1=c1+2 where id=1;
select * from t1;




会话2 将c1加2 更新为105.这里也没有阻塞，因为使用的是乐观锁策略(就是更新时不加锁)。

再看看两个会话提交时会发生什么事情？

  • 会话1 提交
commit;select * from t1;



会话1结果还是104，这个是对的。

  • 会话2 事务提交
commit;select * from t1;



会话2 提交后结果从105再次变为106. 这多出来的1个，看似是合并了会话1的修改。
再次看会话1的结果




会话1 的结果也变为106了。也就是最终两个事务的更新逻辑也都成功了

从这两个案例看，TiDB的行为跟MySQL的可重复读特征不一样。不过结果倒是一致的。

悲观锁


TiDB最近推出悲观锁功能。看了一下描述，感觉说的就是传统数据库事务加锁时的行为。所以就不测试了。


事务自动重试


上面两个事务都提交时最后结果感觉像是两个事务都执行成功了，这个是TiDB默认开启了一个事务自动重试的机制。按快照隔离级别的特征，其中一个事务应该失败。只不过Tidb默认在事务失败的时候自动重跑了整个事务(MySQL没有这个机制)。针对这点官方解释是“因为 TiDB 自动重试机制会把事务第一次执行的所有语句重新执行一遍，当一个事务里的后续语句是否执行取决于前面语句执行结果的时候，自动重试会违反快照隔离，导致更新丢失。这种情况下，需要在应用层重试整个事务。”

通过配置 tidb_disable_txn_auto_retry = on 变量可以关掉显示事务的重试。官方说3.0默认会关闭这个事务自动重试，实际测试alpha版本发现没有。

SET GLOBAL tidb_disable_txn_auto_retry = on;

再重新跑前面的例子。

  • 会话1 



  • 会话2 



  • 会话1 提交



  • 会话2 提交 报错



这个报错的结果就跟ORACLE、OceanBase关于快照隔离级别的结果一致了。只是在行为特征上将报错推迟到最后，同时又降低了锁冲突。




总结


数据库事务并发更新相同记录时，通常做法就是对记录加锁。MySQL的Serializable
隔离级别是严格遵守SQL92标准的，通过读写加锁保证并发事务执行效果跟顺序执行一致。ORACLE的Serializable
隔离级别实际是快照隔离级别，对于事务里的读，都是取快照读；对于事务里的写，会判断其依赖的数据快照是否发生变化，如果变化了就报错。OceanBase的Serializable隔离级别跟ORACLE保持兼容。MySQL的Repeatable read
隔离级别只是保证事务里的读是快照读，快照版本取事务开始时第一句sql执行的时间点对应的版本，对于事务里的写依然是当前读(会申请加锁)。当其他事务持有锁记录时，当前事务会选择等待，一旦拿到锁后就继续更新(用最新数据更新)而不是报错。所以，TiDB说MySQL的Repeatable read
算不上严格的快照隔离级别。

TiDB的Repeatable read
隔离级别的事务里面的读和写都是取事务开始时的数据版本，然后把潜在的冲突判断放到事务commit那个环节去判断。所以也称为乐观锁机制。而ORACLE/MySQL/OceanBase的做法特征都是悲观锁(尽管用快照读，在关键的环节还是有申请加锁)，在源头避免冲突。

悲观锁和乐观锁孰优孰劣，取决于事务执行的成本、冲突的概率以及回滚的成本等，跟业务场景有关。业务逻辑稍微复杂一点的场景就不能接受数据库自动做事务重试，需要业务自己做。乐观锁的性能会比悲观锁好一些，不过安全性我还不确定，还需要更多核心业务场景的检验。

此外无论哪种快照隔离级别，都存在一个写偏序问题。解决写偏序的关键就是在事务里能发现更新依赖的其他数据的版本发生变化，解决这个问题的隔离级别叫Serializable Snapshot Isolation
(简称SSI
)。据说PostgreSQL 和 CockroachDB 已经支持 SSI了。


注：本文是个人观点，可能有误，欢迎指正。或者看下面这本介绍数据库理论的书，分析论证非常严密。


推荐阅读


数据库
文章转载自数据库技术闲谈，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论