数据库事务 （二）事务隔离

数据库的事务隔离级别

在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别-Transaction Isolation Level。

解决方案：

未授权读取 （Read Uncommitted）：允许脏读取，但不允许更新丢失。 如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，单允许其他事务读此行数据。 该隔离级别可以通过“排他写锁”实现。




授权读取 (Read Committed): 允许不可重复读取，单不允许脏读取。 这可以通过“瞬间共享读锁”和“排他写锁”实现。  读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问改行。

在Oracle官方文档Oracle Concept 11gR2里面讲到Read Committed:
In the read committed isolation level, which is the default, every query executed by a transaction sees only data committed before the query-not the transaction-began. This level of isolation is appropriate for database environments in which few transactions are likely to conflict. A query in a read committed transaction avoids reading data that commits while the query is in progress.
For example, if a query is halfway through a scan of a million-row table, and if a different transaction commits an update to row 950,000, then the query does not see this change when it reads row 950,000. However, because the database does not prevent other transactions from modifying data read by a query, other transactions may change data between query executions. Thus, a transaction that runs the same query twice may experience fuzzy reads and phantoms.
这里有两个重点：
1. ORACLE DBMS的默认事务隔离级别正式Read Committed.
Read committed
This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data.
Oracle的请求从来不读取脏数据。

2. Read Committed可能会出现幻读。


可重复读取（Repeatable Read）： 禁止不可重复读取和脏读取， 到那时有事可能出现幻影数据。 这可以通过“共享读锁”和“排他写锁”实现。 读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

序列化（Serializable）： 提供严格的事务隔离。 它要求事务序列化执行， 事务只能一个接一个的执行， 但不能并行。 如果仅仅通过”行级锁“是无法实现事务序列化的， 必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

图1






设定隔离级别的原则
1. 隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。
2. 对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能避免脏读，而且有较好的并发性能。 尽管它会导致不可重复读，虚读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。


在Hibernate中设置隔离级别

在Hibernate的配置文件中可以显式的设置隔离级别。每一种隔离级别都对应一个整数。

-1 ： Read Uncommited
-2 :  Read Committed
-4 :  Repeatable Read
-8 :  Serializable

例如，以下代码把hibernate.cfg.xml文件中的隔离级别设为Read Committed:
    hibernate.connection.isolation=2
对于从数据库连接池中获得的每个连接，Hibernate都会把它改为使用Read Committed隔离级别。












======================================
关于各种读
虚读 phantom read:
转自维基： [url]http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Phantom_reads
[/url]
A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.
This can occur when range locks are not acquired on performing a SELECT... WHERE operation. The phantom reads anomaly is a special case of Non-repeatable reads when Transaction 1 repeats a ranged SELECT...WHERE query and ,between both operations, Transaction 2 creates (i.e. INSERT) new rows (in the target table) which fulfill that WHERE clause.

Transaction 1 Transaction 2
/* Query 1 */
SELECT * FROM users
WHERE age BETWEEN 10 AND 30;
/* Query 2 */
INSERT INTO users VALUES ( 3, 'Bob', 27 );
COMMIT;
/* Query 1 */
SELECT * FROM users
WHERE age BETWEEN 10 AND 30;

Note that Transaction 1 executed the same query twice. If the highest level of isolation were maintained, the same set of rows should be returned both times, and indeed that is what is mandated to occur in a database operation at the SQL SERIALIZABLE isolation level. However, at the lesser isolation levels, a different set of rows may be returned the second time.

In the SERIALIZABLE isolation mode, Query 1 would result in all records with age in the range 10 to 30 being locked, thus Query 2 would block until the first transaction was commited. In REPEATABLE READ mode, the range would not be locked, allowing the record to be inserted and the second execution of Query 1 to include the new row in its results.

最后这一段这里讲到了Seriliazable模式和Reapeatable-read模式。 前者通过锁住T1所读取的信息，一直到提交事务，也就是后面那个SELECT被执行以后。 这段事件里这个10到30岁的范围的数据一直处于被锁状态，因此T2就会被阻塞(block),直到T1被提交。
而Repeatable-read模式不会对对象信息加锁，因此T1在第二次执行SELECT语句时，会查到T2插入的新的数据。
这也就如图1所示，隔离级别Serializable可以防止虚读(幻读),而Repeatable Read不可以。 但是就并发性能来说，Serializable是最差的，从上面的例子就可以看出,T2会一直等待T1释放资源。



如果符合搜索条件的一行数据在后面的读取操作中出现，但该行数据却不属于最初的数据，就会发生这种事件。

用Oracle进行试验如下：
t1: select * from test where id = 1 for update;
t1: update test set id = 3 where id =1 ;
t2: select * from test where id = 1;
t1: commit;
t2: select * from test where id  =1;
这里t2的第一次select语句可以查到数据，因为此时t1的事务还没提交。
第二次就查不到了， 此时t1事务已经提交，该row数据已经被修改。