SQL事宜的隔离级别

SQL4种隔离级别的定义

隔离级别的定义涉及到三种现象，读脏数据，不可重复读，幻读。定义来自postgresql的最新文档

dirty read  A transaction reads data written by a concurrent uncommitted transaction.
nonrepeatable read A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read).
phantom read A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.

The four transaction isolation levels and the corresponding behaviors are described in Table 13-1.

Isolation LevelDirty ReadNonrepeatable ReadPhantom Read| Read uncommitted | Possible | Possible | Possible |

下面我举例来说明这三种现象和4种隔离级别的区别。

==mytable==

id   age  name

1     20   zhang

2     22    li

3     23   wang

读脏数据

   事务A   select * from mytable where id=1   事务B update mytable set age=age+1 where id=1 rollback

   如果隔离级别是Read Uncommitted，那么A可能读到的age是21岁，虽然21这个值只是一个临时的值，事务B最终回归了它。

   如果能避免这种现象，那么隔离级别就至少是Read committed了。

不可重复读

  事务A select * from mytable where id=1  select * from mytable where id=1    事务B update mytable set age=age+1

   如果隔离级别不是Repeatable read，那么A第一次读的值可能是20，第二次变成了21

  如果能避免这种现象，那么隔离级别就至少是Repeatable read

幻读

  事务A select * from mytable where age<23  select * from mytable where age<23   事务B insert into mytable values(4, 21,'zhou')

  如果能避免这种现象就是Serializable。

  注意上面的例子，可重复读但不能幻读的情况：比如在基于锁的实现里，可能只锁定了age为20和22的行，但并没有锁定范围，那么可以插入age为21的新行，这是可以保证可重复读的。注意不可重复读和幻读的定义的文字细节：

    A transaction re-reads data it has previously read and finds that data has been modified by another transaction

    A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.

    很多资料没有把这两种分清楚。那上面的例子来说，A select * from mytable where age<23 这个query应该分为两步：首先找到age<23的记录，然后读取这些记录。

   可重复读指的是：我在同一个事务里两次读取这些记录的值是相同的，但它不能避免幻读，因为下次执行同一个查询时，满足条件的记录可能变多了。

   举个例子： 事务A select id from mytable where age<23  对于满足上面要求的id，找到这个id对应的name。

   这个例子在现实中不太可能发生，不过可以清楚的解释第二和第三中隔离级别的区别。这个事务其实就是找到年龄小于23的人的名字。

   如果只要满足第二种隔离级别，那么这个事务的结果就是我想要的结果。在这个事务的过程中，可能修改了zhang的名字，但是我拿到的还是zhang

   但是如果它不满足第三种隔离级别，那么可能再次执行这条语句会多出一个人来。而如果满足第三种隔离级别，那么select出来的结果是不变的。

   不过很多数据库系统的实现里都使用了比较强的实现，所以也不必特意强调它们的区别。

真正串行

   两个事务的结果和一个一个运行完全一样。具体的例子会在下面Postgresql的说明里提到。

   这个SQL 92 标准并没有定义，也就是说，即使是Serializable级别的，也可能不能保证这个要求，不过PostgreSQL是能够做到这点的。

PostgreSQL的隔离级别

   注意：我这里说的是最新开发版本9.1的情况，9.1之前的版本只能到Serializable级别的，而不能到“真正串行”的级别。

Read Committed Isolation Level

     这是PG的默认隔离级别，保证不会读到脏数据，PG认为这个级别能够满足一般的事务需求，而且非常高效。这种级别下，select只能看到执行前这一瞬间前提交的修改，在它执行过程中的提交是看不到的。（当然，它所在事务之前的修改即使未提交也是对它可见的）。如果这个select是update，delete等语句的条件，那么它select结果也是这样，如果在它修改的时候，发现有其它事务在做修改(那么这个事务至少删除或者锁定了某些对象，否则当前更新就在它之前)，那么它会等其它事务完成，如果其它事务roll back了，它会继续更新；如果其它事务commit了，那么它会在其它事务的结果的基础上更新------如果前面的事务把它删除了，那么它就忽略这条记录；如果前面的事务修改了它，那么它会尝试从新判断这条记录是否满足条件。这是比较常见的，如果别的事务把记录修改了，那么原来满足条件的记录现在可能不满足了。

     比如银行转账的例子：

BEGIN;UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;COMMIT;

从账号7534转账100元到12345，这个事务先给12345加100元，然后给7534减100元。如果两个事务同时进行，那么后一个事务将会在前一个事务的基础上更新，这样就是我们需要的逻辑：给12345
加了200元而不是100元。

下面是另一个例子

BEGIN;UPDATE website SET hits = hits + 1;-- run from another session:  DELETE FROM website WHERE hits = 10;COMMIT;

这个例子是两个事务，一个事务把hits加1；另一个事务删除hits==10的记录。

在这种隔离级别下，删除的事务其实是删除不了任何记录的。因为它的where选择hits==10的记录，因为是Read Committed级别，所以它只会找到在这瞬间hits==10的记录，这是前面的事务把hits==10的记录的hits改成了11了，它发现这点，然后重新用where的条件判断一下，发现不满足了，就什么也不做了。

Repeatable Read Isolation Level

    这个级别的select只能看到这个事务开始前提交的更改。可以这么理解：在开始事务时，给数据库拍个快照，然后所以的操作都在这个快照上进行；而上一个隔离级别呢，在select前个数据库拍个快照。注意：PG的这种实现方式不仅实现了Repeatable Read，而且同时实现了Serializable，这是合乎规范的，规范只是定义了每种隔离级别需要避免哪种现象。我们甚至可以只实现最高的一种隔离级别Serializable然后说自己实现了4种隔离级别。那SQL规范为什么还要定义这么多级别呢？因为实现的级别越高，效率也就越低，我的应用可能只需要较低级别的隔离，你用最高级别的隔离来实现，那么就效率太低了。说过极端的例子，我可以这样实现Serializable隔离级别------不允许事务并发执行，这必然避免了所有的情况，但是有什么意义呢？效率极低！

    其实最常用的隔离级别就是第二种和第四种，所以Oracle只实现了这两种（第一种就是没有隔离），所以不能理解为Oracle没有实现规范，它可以用第4中隔离级别来代替第3种（PostgreSQL也是这么做的），只不过有这样一种可能：有一种算法只实现了第三种级别没有实现第四种级别，它比第四种级别的高效，而没有实现它在某些应用场景下有点可惜！

   这个级别的update有所不同，如果它要修改的时候发现别人正在修改，那么它也会等待。如果别人rollback，它正常修改，如果别人commit，那么它就会失败，因为别人已经修改了，那么就不可重复读了，这个事务就得失败。失败后重新来一次，这是读到的数据已经是最新的了。

   失败的话会出现 ERROR: could not serialize access due to concurrent update

   因此，只有只读的Repeatable Read事务从来不会冲突，有写的事务时才可能冲突。

Note: Prior to PostgreSQL version 9.1, a request for the Serializable transaction isolation level provided exactly the same behavior describedhere. To retain the legacy Serializable behavior, Repeatable Read should now be requested.

  注意：在9.1之前的版本，这个隔离级别就是最高级别了。

Serializable Isolation Level

   前面说了，PG9.1的Repeatable Read Level其实就满足SQL规范的Serializable级别了。那么这个级别是什么呢？

   我们先来看一个例子，看看Serializable和真正串行执行事务的区别。

   这个级别以及是SQL规范的最高级别了，但是它还是不能等价于真正的串行化（一个事务完成了另一个事务才能开始），比如下面的例子：

class | value
------+------
1 | 10
1 | 20
2 | 100
2 | 200
事务B：SELECT SUM(value) FROM mytab WHERE class = 1 把这个值以class=2插入

事务A：SELECT SUM(value) FROM mytab WHERE class = 2; 把这个值以class=1插入

先解释一下这个两个事务在干什么：多个事务协作计算这个表格value的值——事务A计算class=1的value和，事务B计算class=2，然后将结果以对方的class保持下来，一般对方可以方便的
求和。
如果以上面的隔离级别，也就是SQL规范的Serializable级别，那么两个事务都能提交，并且它们的结果都是300；而串行的结果呢？至少一个应该得到330

因此PG9.1的Serializable是真正的串行隔离级别，如果A插入(2,30)，那么事务B的select或者insert都会出错，强迫回滚事务：

ERROR: could not serialize access due to read/write dependencies among transactions
这是用户再次重试事务B，它读到的就是330了。

这个级别需要使用谓词锁(Predicate Lock)，它会锁定这个事务里用到过的记录，它会同时使用细粒度的tuple locks和粗粒度的page locks，并且不会造成block和死锁，但是它需要跟踪
事务用过的记录，所以还是比上一个级别慢的。另外如果是只读事务的话，那么它如果发现后面的逻辑不依赖于某些锁定的对象的话，它可能提前释放这些对象。所以知道自己不会修改，
那么最好告诉PG。

MySQL InnoDB的隔离级别

  To be continued.