数据库事务，锁，隔离级别(Isolation Level)

数据库的隔离级别2(repeaable read)可实现重复读的功能，一直对其中一部分有困惑。

下面以Sybase官方文档中的例子来阐述，例子的详细内容请阅读下面的英文文档，

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20021_1251/html/locking/locking24.htm

Isolation Level 2, repeatable read

Level 2 prevents nonrepeatable reads. These occur when one transaction reads a row and a second transaction modifies that row. If the second transaction commits its change, subsequent reads by the first transaction yield results that are different from the original read. Isolation level 2 is supported only on data-only-locked tables. In a session at isolation level 2, isolation level 3 is also enforced on any tables that use the allpages locking scheme. Table 2-9 shows a nonrepeatable read in a transaction at isolation level 1.

Table 2-9: Nonrepeatable reads in transactions

T7

Event Sequence

T8

T7 begin transaction select balance from account where acct_number = 25 select balance from account where acct_number = 25 commit transaction	Event Sequence T7 and T8 start. T7 queries the balance for one account. T8 updates the balance for that same account. T8 ends. T7 makes same query as before and gets different results. T7 ends.	T8 begin transaction update account set balance = balance - 100 where acct_number = 25 commit transaction

 

If transaction T8 modifies and commits the changes to the account table after the first query in T7, but before the second one, the same two queries in T7 would produce different results. Isolation level 2 blocks transaction T8 from executing. It would also block a transaction that attempted to delete the selected row.

 

 

问题阐述：

T7这个事务里面有2个同样的select语句，T8事务有一个update语句

发生的顺序是:

T7开启事务

T7执行第一select语句

T8开启事务

T8执行update语句

T8提交事务

T7执行第二个select语句

T7提交事务

 

如果数据库的隔离级别是read committed，也就是1的话

结果是T7的两次select的结果不一样，因为符合T7 select语句条件的数据在途中被T8 update了。

 

如果数据库的隔离级别是repeatable read，也就是2的话，T7的两次select结果是一样的。

 

我的问题是？

既然T7已经开启了事务，而且T7的select语句已经获取了shared Lock,那么在T7没有提交事务之前，T8根本就获取不到Exclusive Lock,会一直等到T7提交事务之后才能获取到Exclusive Lock. 那么显而易见对于数据库隔离级别为1的情况下T7的两次select结果也是一样的，为什么还需要提高隔离级别至2呢？

 

原因是我对事务和锁没有完全理解。一个事务可能会持续很长时间，因为你可能要在一个事务里处理很多事情，比如查询数据库，然后执行业务逻辑，然后再更新数据库，再执行业务逻辑，之后又更新或查询数据库等等。一句话，一个事务执行的过程中，同时可能会有其他的事务也在执行。但是一个事务并不一定(有些是，有些不是)是获取某个锁之后要到事务结束时才会释放锁，获取有些锁是为了完成某个数据操作比如查询，一旦查询操作完成就会马上被释放掉，这样在某个事务执行过程中，其他事务是可以获取到不冲突的锁的，这取决于数据库隔离级别以及锁的设置。

比如对于shared locks, sybase 官方文档有如下描述

 

• Shared locks

  Adaptive Server applies shared locks for read operations. If a shared lock has been applied to a data page or data row or to an index page, other transactions can also acquire a shared lock, even when the first transaction is active. However, no transaction can acquire an exclusive lock on the page or row until all shared locks on the page or row are released. This means that many transactions can simultaneously read the page or row, but no transaction can change data on the page or row while a shared lock exists. Transactions that need an exclusive lock wait or “block” for the release of the shared locks before continuing.

  By default, Adaptive Server releases shared locks after it finishes scanning the page or row. It does not hold shared locks until the statement is completed or until the end of the transaction unless requested to do so by the user. For more details on how shared locks are applied, see “Locking for select queries at isolation Level 1”.

 

所以对本文中的例子来说，T7执行第一个select获取的是shared lock, 第一个select执行完后，T7就释放掉了这个shared lock，T7并不会等到事务提交或回滚结束时才释放这个锁, 这样如果现在也没有其他事务持有该数据块的锁，那么T8就可以获取到该数据块的Exclusive lock，从而完成update操作。所以数据库read committed隔离级别是实现不了repeatable read功能的。当隔离级别提升为2即repeatable read后，T7没有提交的话，T8就被block住了(至于怎么和怎样block，得另外看文档，这里不讨论)，这样就能实现repeatable  read的功能。

 

 

当然Exclusive lock是符合我原本理解的，即如果一个事务持有Exclusive lock，那么这个锁只会在事务结束时才会被释放掉，并不会在事务执行过程中释放，这样，不管是隔离级别1还是2，其他事务都没有机会再去持有同一数据块的任何其他锁，从而其他事务都会被block住，当然必须是其他事务针对的是有冲突的数据块。

 

 

数据库事务隔离级别，主要是关于是否会发生脏读、不可重复读、幻读。参考

sybase官方文档:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20021_1251/html/locking/locking24.htm

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20021_1251/html/locking/locking24.htm

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20021_1251/html/locking/locking24.htm

数据库事物隔离级别另外一方面要处理的问题是2类丢失更新问题

Lock compatibility and lock sufficiency

Two basic concepts underlie issues of locking and concurrency:

• Lock compatibility: if task holds a lock on a page or row, can another row also hold a lock on the page or row?

• Lock sufficiency: for the current task, is the current lock held on a page or row sufficient if the task needs to access the page again?

Lock compatibility affects performance when users needs to acquire a lock on a row or page, and that row or page is already locked by another user with an incompatible lock. The task that needs the lock waits, or blocks, until the incompatible locks are released.

Lock sufficiency works with lock compatibility. If a lock is sufficient, the task does not need to acquire a different type of lock. For example, if a task updates a row in a transaction, it holds an exclusive lock. If the task then selects from the row before committing the transaction, the exclusive lock on the row is sufficient; the task does not need to make an additional lock request. The opposite case is not true: if a task holds a shared lock on a page or row, and wants to update the row, the task may need to wait to acquire its exclusive lock if other tasks also hold shared locks on the page.

Table 2-4 summarizes the information about lock compatibility, showing when locks can be acquired immediately.

Table 2-4: Lock compatibility  

Can another process immediately acquire:

If one process has:

A Shared Lock?

An Update Lock?

An Exclusive Lock?

A Shared Intent Lock?

An Exclusive Intent Lock?

A Shared Lock	Yes	Yes	No	Yes	No
An Update Lock	Yes	No	No	N/A	N/A
An Exclusive Lock	No	No	No	No	No
A Shared Intent Lock	Yes	N/A	No	Yes	Yes
An Exclusive Intent Lock	No	N/A	No	Yes	Yes

Table 2-5 shows the lock sufficiency matrix.

Table 2-5: Lock sufficiency  

Is that lock sufficient if the task needs:

If a task has:

A Shared Lock

An Update Lock

An Exclusive Lock

	A Shared Lock	Yes	No	No
	An Update Lock	Yes	Yes	No
	An Exclusive Lock	Yes	Yes	Yes

How isolation levels affect locking

The SQL standard defines four levels of isolation for SQL transactions. Each isolation level specifies the kinds of interactions that are not permitted while concurrent transactions are executing—that is, whether transactions are isolated from each other, or if they can read or update information in use by another transaction. Higher isolation levels include the restrictions imposed by the lower levels.

The isolation levels are shown in Table 2-6, and described in more detail on the following pages.

Table 2-6: Transaction isolation levels

Number

Name

Description

0	read uncommitted	The transaction is allowed to read uncommitted changes to data.
1	read committed	The transaction is allowed to read only committed changes to data.
2	repeatable read	The transaction can repeat the same query, and no rows that have been read by the transaction will have been updated or deleted.
3	serializable read	The transaction can repeat the same query, and receive exactly the same results. No rows can be inserted that would appear in the result set.

You can choose the isolation level for all select queries during a session, or you can choose the isolation level for a specific query or table in a transaction.

At all isolation levels, all updates acquire exclusive locks and hold them for the duration of the transaction.

For tables that use the allpages locking scheme, requesting isolation level 2 also enforces isolation level 3.

Isolation Level 0, read uncommitted

Level 0, also known as read uncommitted, allows a task to read uncommitted changes to data in the database. This is also known as a dirty read, since the task can display results that are later rolled back. Table 2-7 shows a select query performing a dirty read.

Table 2-7: Dirty reads in transactions

T3

Event Sequence

T4

begin transaction update account set balance = balance - 100 where acct_number = 25 rollback transaction	T3 and T4 start. T3 updates balance for one account by subtracting $100. T4 queries current sum of balance for accounts. T4 ends. T3 rolls back, invalidating the results from T4.	begin transaction select sum(balance) from account where acct_number < 50 commit transaction

If transaction T4 queries the table after T3 updates it, but before it rolls back the change, the amount calculated by T4 is off by $100.The update statement in transaction T3 acquires an exclusive lock on account. However, transaction T4 does not try to acquire a shared lock before querying account, so it is not blocked by T3. The opposite is also true. If T4 begins to query accounts at isolation level 0 before T3 starts, T3 could still acquire its exclusive lock on accounts while T4’s query executes, because T4 does not hold any locks on the pages it reads.

At isolation level 0, Adaptive Server performs dirty reads by:

• Allowing another task to read rows, pages, or tables that have exclusive locks; that is, to read uncommitted changes to data.

• Not applying shared locks on rows, pages or tables being searched.

Any data modifications that are performed by T4 while the isolation level is set to 0 acquire exclusive locks at the row, page, or table level, and block if the data they need to change is locked.

If the table uses allpages locking, a unique index is required to perform an isolation level 0 read, unless the database is read-only. The index is required to restart the scan if an update by another process changes the query’s result set by modifying the current row or page. Forcing the query to use a table scan or a non unique index can lead to problems if there is significant update activity on the underlying table, and is not recommended.

Applications that can use dirty reads may see better concurrency and reduced deadlocks than when the same data is accessed at a higher isolation level. If transaction T4 requires only an estimate of the current sum of account balances, which probably changes frequently in a very active table, T4 should query the table using isolation level 0. Other applications that require data consistency, such as queries of deposits and withdrawals to specific accounts in the table, should avoid using isolation level 0.

Isolation level 0 can improve performance for applications by reducing lock contention, but can impose performance costs in two ways:

• Dirty reads make in-cache copies of dirty data that the isolation level 0 application needs to read.

• If a dirty read is active on a row, and the data changes so that the row is moved or deleted, the scan must be restarted, which may incur additional logical and physical I/O.

During deferred update of a data row, there can be a significant time interval between the delete of the index row and the insert of the new index row. During this interval, there is no index row corresponding to the data row. If a process scans the index during this interval at isolation level 0, it will not return the old or new value of the data row. See “Deferred updates” on page 95 in Performance and Tuning: Optimizer.

 

 

Isolation Level 1, read committed

Level 1, also known as read committed, prevents dirty reads. Queries at level 1 can read only committed changes to data. At isolation level 1, if a transaction needs to read a row that has been modified by an incomplete transaction in another session, the transaction waits until the first transaction completes (either commits or rolls back.)

For example, compare Table 2-8, showing a transaction executed at isolation level 1, to Table 2-7, showing a dirty read transaction.

Table 2-8: Transaction isolation level 1 prevents dirty reads

T5

Event Sequence

T6

begin transaction update account set balance = balance - 100 where acct_number = 25 rollback transaction	T5 and T6 start. T5 updates account after getting exclusive lock. T6 tries to get shared lock to query account but must wait until T5 releases its lock. T5 ends and releases its exclusive lock. T6 gets shared lock, queries account, and ends.	begin transaction select sum(balance) from account where acct_number < 50 commit transaction

When the update statement in transaction T5 executes, Adaptive Server applies an exclusive lock (a row-level or page-level lock if acct_number is indexed; otherwise, a table-level lock) on account.

If T5 holds an exclusive table lock, T6 blocks trying to acquire its shared intent table lock. If T5 holds exclusive page or exclusive row locks, T6 can begin executing, but is blocked when it tries to acquire a shared lock on a page or row locked by T5. The query in T6 cannot execute (preventing the dirty read) until the exclusive lock is released, when T5 ends with therollback.

While the query in T6 holds its shared lock, other processes that need shared locks can access the same data, and an update lock can also be granted (an update lock indicates the read operation that precedes the exclusive-lock write operation), but no exclusive locks are allowed until all shared locks have been released.

 

 

Isolation Level 2, repeatable read

Level 2 prevents nonrepeatable reads. These occur when one transaction reads a row and a second transaction modifies that row. If the second transaction commits its change, subsequent reads by the first transaction yield results that are different from the original read. Isolation level 2 is supported only on data-only-locked tables. In a session at isolation level 2, isolation level 3 is also enforced on any tables that use the allpages locking scheme. Table 2-9 shows a nonrepeatable read in a transaction at isolation level 1.

Table 2-9: Nonrepeatable reads in transactions

T7

Event Sequence

T8

begin transaction select balance from account where acct_number = 25 select balance from account where acct_number = 25 commit transaction	T7 and T8 start. T7 queries the balance for one account. T8 updates the balance for that same account. T8 ends. T7 makes same query as before and gets different results. T7 ends.	begin transaction update account set balance = balance - 100 where acct_number = 25 commit transaction

If transaction T8 modifies and commits the changes to the account table after the first query in T7, but before the second one, the same two queries in T7 would produce different results. Isolation level 2 blocks transaction T8 from executing. It would also block a transaction that attempted to delete the selected row.

 

 

Isolation Level 3, serializable reads

Level 3 prevents phantoms. These occur when one transaction reads a set of rows that satisfy a search condition, and then a second transaction modifies the data (through an insert,delete, or update statement). If the first transaction repeats the read with the same search conditions, it obtains a different set of rows. In Table 2-10, transaction T9, operating at isolation level 1, sees a phantom row in the second query.

Table 2-10: Phantoms in transactions

T9

Event Sequence

T10

begin transaction select * from account where acct_number < 25 select * from account where acct_number < 25 commit transaction	T9 and T10 start. T9 queries a certain set of rows. T10 inserts a row that meets the criteria for the query in T9. T10 ends. T9 makes the same query and gets a new row. T9 ends.	begin transaction insert into account (acct_number, balance) values (19, 500) commit transaction

If transaction T10 inserts rows into the table that satisfy T9’s search condition after the T9 executes the first select, subsequent reads by T9 using the same query result in a different set of rows.

Adaptive Server prevents phantoms by:

• Applying exclusive locks on rows, pages, or tables being changed. It holds those locks until the end of the transaction.

• Applying shared locks on rows, pages, or tables being searched. It holds those locks until the end of the transaction.

• Using range locks or infinity key locks for certain queries on data-only-locked tables.

Holding the shared locks allows Adaptive Server to maintain the consistency of the results at isolation level 3. However, holding the shared lock until the transaction ends decreases Adaptive Server’s concurrency by preventing other transactions from getting their exclusive locks on the data.

Compare the phantom, shown in Table 2-10, with the same transaction executed at isolation level 3, as shown in Table 2-11.

Table 2-11: Avoiding phantoms in transactions

T11

Event Sequence

T12

begin transaction select * from account holdlock where acct_number < 25 select * from account holdlock where acct_number < 25 commit transaction	T11 and T12 start. T11 queries account and holds acquired shared locks. T12 tries to insert row but must wait until T11 releases its locks. T11 makes same query and gets same results. T11 ends and releases its shared locks. T12 gets its exclusive lock, inserts new row, and ends.	begin transaction insert into account (acct_number, balance) values (19, 500) commit transaction

In transaction T11, Adaptive Server applies shared page locks (if an index exists on the acct_number argument) or a shared table lock (if no index exists) and holds the locks until the end of T11. The insert in T12 cannot get its exclusive lock until T11 releases its shared locks. If T11 is a long transaction, T12 (and other transactions) may wait for longer periods of time. As a result, you should use level 3 only when required.

 

 

 

或者

[url]http://singo107.iteye.com/blog/1175084[/url]

 

另外java.sql.Connection里面的有关于Isolation Level定义,简单明了,也可以参考这个.

    /**

     * A constant indicating that transactions are not supported.

     */

    int TRANSACTION_NONE             = 0;

 

    /**

     * A constant indicating that

     * dirty reads, non-repeatable reads and phantom reads can occur.

     * This level allows a row changed by one transaction to be read

     * by another transaction before any changes in that row have been

     * committed (a "dirty read").  If any of the changes are rolled back,

     * the second transaction will have retrieved an invalid row.

     */

    int TRANSACTION_READ_UNCOMMITTED = 1;

 

    /**

     * A constant indicating that

     * dirty reads are prevented; non-repeatable reads and phantom

     * reads can occur.  This level only prohibits a transaction

     * from reading a row with uncommitted changes in it.

     */

    int TRANSACTION_READ_COMMITTED   = 2;

 

    /**

     * A constant indicating that

     * dirty reads and non-repeatable reads are prevented; phantom

     * reads can occur.  This level prohibits a transaction from

     * reading a row with uncommitted changes in it, and it also

     * prohibits the situation where one transaction reads a row,

     * a second transaction alters the row, and the first transaction

     * rereads the row, getting different values the second time

     * (a "non-repeatable read").

     */

    int TRANSACTION_REPEATABLE_READ  = 4;

 

    /**

     * A constant indicating that

     * dirty reads, non-repeatable reads and phantom reads are prevented.

     * This level includes the prohibitions in

     * <code>TRANSACTION_REPEATABLE_READ</code> and further prohibits the

     * situation where one transaction reads all rows that satisfy

     * a <code>WHERE</code> condition, a second transaction inserts a row that

     * satisfies that <code>WHERE</code> condition, and the first transaction

     * rereads for the same condition, retrieving the additional

     * "phantom" row in the second read.

     */

    int TRANSACTION_SERIALIZABLE     = 8;