基本概念
脏读：包含未提交数据的读。例如，事务1 更改了某行。事务2 在事务1 提交更改之前读取已更改的行。如果事务1 回滚更改，则事务2 便读取了逻辑上从未存在过的行。
不可重复读取：当某个事务不止一次读取同一行，并且一个单独的事务在两次（或多次）读取之间修改该行时。因为在同一个事务内的多次读取之间修改了该行，所以每次读取都生成不同值，从而引发不一致问题。
幻像：通过一个任务，在以前由另一个尚未提交其事务的任务读取的行的范围中插入新行或删除现有行。带有未提交事务的任务由于该范围中行数的更改而无法重复其原始读取。如果某个连接设置其事务隔离级别为可串行，则 SQL Server 使用键范围锁定以防止幻像。
SQL-92 定义了下列四种隔离级别
| 隔离级别 | 脏读 | 不可重复读取 | 幻像 |
| 未提交读 | 是 | 是 | 是 |
| 提交读 | 否 | 是 | 是 |
| 可重复读 | 否 | 否 | 是 |
| 可串行读 | 否 | 否 | 否 |
我们使用三个主流数据库测试，分别是Oracle 10g, SQL Server 2005和MySQL 5.1 (InnoDB only)
测试幻读
首先创建一个表test，并插入三条测试数据
CREATE TABLE test
(
c1 INTEGER,
c2 INTEGER
);
insert into test values(1,1);
insert into test values(2,2);
insert into test values(3,3);
commit;
SQL Server消除幻读测试
| Steps | T1 | T2 | 备注 |
| 1 |
|
| T1事务启动，设置隔离等级到Serializable， 然后查询一下test，会看到3条记录 |
| 2 |
select * from test; | T2启动，插入一行并提交，这时候SQL Server会阻塞这个插入, 因为T1加了范围锁 | |
| 3 | select * from test; | T1第二次查询应该看到和第一次一样的三行，然后提交 | |
| 只有T1提交之后，T2才可以插入 | ||
从sp_lock输出来看，在第一步之后，T1对test表加了S锁，所以T2可以选test的数据但是无法更改
Oracle消除幻读测试
| Steps | T1 | T2 | 备注 |
| 1 |
|
| T1事务启动，设置隔离等级到Serializable， 然后查询一下test，会看到3条记录 |
| 2 |
select * from test; | T2启动，插入一行并提交，这时候Oracle不会阻塞这个插入，然后T2成功提交 | |
| 3 | select * from test; | T1第二次查询仍然看到和第一次一样的三行，然后提交 | |
| 4 | select * from test; | 新事物开始，这时候T1才会看到刚才T2插入的记录 | |
从V$lock来看，整个过程Oracle没有加锁，Oracle使用了SCN从回滚段重建出来当初时刻的数据提供一个snapshot 来保证T1不受T2的影响。
MySQL消除幻读测试
| Steps | T1 | T2 | 备注 |
| 1 |
|
| T1事务启动，设置隔离等级到Serializable， 然后查询一下test，会看到3条记录 |
| 2 |
select * from test; | T2启动，插入一行并提交，这时候MySQL会阻塞这个插入, 因为T1加了范围锁 | |
| 3 | select * from test; | T1第二次查询应该看到和第一次一样的三行，然后提交 | |
| 只有T1提交之后，T2才可以插入 | ||
和SQL Server一样的处理方式，但是MySQL也有snapshot, 效果和Oracle一样，语法是START TRANSACTION WITH CONSISTENT SNAPSHOT;从这个角度来说MySQL实现还是不错的，比SQL Server好一些。
测试不可重复读
SQL Server消除不可重复测试
| Steps | T1 | T2 | 备注 |
| 1 |
|
| T1事务启动，设置隔离等级到Serializable， 然后查询一下test，会看到3条记录 |
| 2 |
select * from test; | T2启动，更改一行，这时候SQL Server会阻塞这个更改, 因为T1在记录行上加了X和U锁 (排他更新锁) | |
| 3 | select * from test; | T1第二次查询应该看到和第一次一样记录，然后提交 | |
| 只有T1提交之后，T2才可以更新 | ||
Oracle消除不可重复测试
Oracle不支持REPEATABLE READ这个隔离等级，我们需要用更高的Serializable级别来测试
| Steps | T1 | T2 | 备注 |
| 1 |
|
| T1事务启动，设置隔离等级到Serializable， 然后查询一下test，会看到3条记录 |
| 2 |
select * from test; | T2启动，更改一行并提交，这时候Oracle不会阻塞这个更新，然后T2成功提交 | |
| 3 | select * from test; | T1第二次查询仍然看到和第一次一样的记录，然后提交 | |
| 4 | select * from test; | 新事物开始，这时候T1才会看到刚才T2更改的记录 | |
从V$lock来看，整个过程Oracle没有加锁，Oracle使用了SCN从回滚段重建出来当初时刻的数据提供一个snapshot 来保证T1不受T2的影响。
MySQL消除不可重复测试
| Steps | T1 | T2 | 备注 |
| 1 |
|
| T1事务启动，设置隔离等级到Serializable， 然后查询一下test，会看到3条记录 |
| 2 |
select * from test; | T2启动，更改一行并提交，这时候MySQL不会阻塞这个更新. | |
| 3 | select * from test; | T1第二次查询应该看到和第一次一样的记录 | |
对于不可重复读，MySQL和Oracle一样采取了类似快照的方式，有点出乎意料，这点上MySQL实现比SQL Server要好一些。
测试脏读
Read Committed可以消除脏读，由于比较简单，各个数据库实现差别不大，不会加特殊的锁，不再详细测试。这也是我们大多数情况下的数据库默认事务隔离等级。
总结
事务隔离等级不同的数据库实现有差别，有时候必须要清楚的了解之间的差别才能避免应用程序在多个数据库上兼容性和稳定性。
对于REPEATABLE READS，SQL Server设定隔离等级到REPEATABLE READ，对所有select过的数据加锁，阻止其他事务更新数据。但是其他事务可以插入幻读记录。而Oracle不直接支持这个等级，必须设定更高的SERIALIZABLE，Oracle使用SCN和Rollback segment重构snapshot，MySQL也是使用类似的snapshot，因次理论上讲SQL Server的这个隔离级别上并发性能比较差。
对于PHANTOM幻读，三种数据库都是设定到SERIALIZABLE等级，但是SQL Server是通过加范围锁阻塞其他事务的插入和更新，Oracle使用snapshot, MySQL两种方式都支持。
另外，由于SERIALIZABLE级别上，SQL Server使用的是范围锁，所以其他数据无法插入或者更新，而Oracle不会阻塞其他事务更新数据，Oracle假设大多数情况下多个事务不会更新同一条记录，但是如果其他更新的记录和当前事务碰巧修改了同一条记录，Oracel会通过乐观锁发现这种情况，并报错臭名昭著的ORA-8177 Cannot serialize access for this transaction. 所以Oracle的Serializable虽然性能高，但是不可以用于长时间的事务或者频繁的OLTP系统。如果有这样的需要，必须通过lock table实现。
尽管这几个数据库都实现了ACID但是各有千秋，实现跨数据库的应用的时候需要小心。还有就是，不管数据库本身事务隔离等级和锁的实现效率差别如何，关键还是良好的架构，不好的架构往往在单节点低压力测试的时候速度很快但是在高并发的多处理器或者集群上横向伸缩的时候性能下降很快，Facebook或者LiveJournal用一大堆MySQL照样跑的很好， 不比昂贵的Oracle RAC差，架构决定伸缩性，而不应该迷信数据库本身。
博文
没有评论:
发表评论