Control the locking behavior and row version control behavior of the Transact-SQL statement issued by the connection to SQL Server

Set transaction isolation level (Transact-SQL)

 

Control the locking behavior and row version control behavior of the Transact-SQL statement issued by the connection to SQL Server.

Transact-SQL syntax conventions

Syntax

SET TRANSACTION ISOLATION LEVEL    { READ UNCOMMITTED    | READ COMMITTED    | REPEATABLE READ    | SNAPSHOT    | SERIALIZABLE    }[ ; ]

Remarks

Only one isolation level option can be set at a time, and the setting option will always be valid for that connection until the option is explicitly changed. All read operations performed in the transaction will run under the specified isolation level rule, unless the table prompt in the from clause of the statement specifies other locking behaviors or version control behaviors for the table.

The transaction isolation level defines the lock types that can be obtained for read operations. The row lock obtained for read committed or repeatable read is usually a row lock. Although a row lock can be upgraded to a page lock or table lock when a large number of rows are referenced in a page or table. If a row is modified by the transaction after it is read, the transaction obtains an exclusive lock to protect the row, and the exclusive lock remains until the transaction is completed. For example, if a repeatable read transaction has a shared lock for a row and the transaction subsequently modifies the row, the shared row lock is converted to an exclusive row lock.

When a transaction is in progress, you can change the transaction from one isolation level to another at any time. After a transaction is changed from one isolation level to another, the changed read resources are protected according to the new rules. Resources READ before the change will continue to be protected according to previous rules. For example, a transaction is changed from repeatable read to SERIALIZABLE. The rows read by the SELECT statement before the change are protected by row-level, page-level, or table-level shared locks. These locks are retained until the transaction ends. Rows read after the SELECT statement is changed are protected by range locks.

This table shows the locking behavior when a transaction is changed from one isolation level to another.

Isolation level before change	Isolation level after change
READ UNCOMMITTED	Read uncomitted: Not changed. Read committed: This behavior depends on the settings of the READ_COMMITTED_SNAPSHOT database option: If it is OFF, the transaction obtains the shared lock and retains the lock during the read. If it is ON, the transaction uses row version control. SNAPSHOT: The transaction must have been started as a SNAPSHOT. The transaction will fail and all changes will be rolled back. Repeatable read: Now the transaction will get the shared lock and keep the lock during the transaction. SERIALIZABLE: Now the transaction will get the range lock and keep the lock during the transaction.
READ COMMITTED	Read uncomitted: Transactions no longer obtain the lock used for read operations. Read committed: Not changed. SNAPSHOT: The transaction must have been started as a SNAPSHOT. The transaction will fail and all changes will be rolled back. Repeatable read: Now the transaction will get the shared lock and keep the lock during the transaction. SERIALIZABLE: Now the transaction will get the range lock and keep the lock during the transaction.
SNAPSHOT	Read uncomitted: Transactions no longer use row version control and no longer obtain the lock for read operations. Read committed: This behavior depends on the settings of the READ_COMMITTED_SNAPSHOT database option: If it is OFF, the transaction obtains the shared lock and retains the lock during the read. If it is ON, the transaction uses row version control. SNAPSHOT: Not changed. Repeatable read: This transaction no longer uses row version control. Now it acquires the shared lock and keeps it during transaction execution. SERIALIZABLE: This transaction no longer uses row version control. Now it acquires the range lock and keeps it during transaction execution.
REPEATABLE READ	Read uncomitted: The transaction no longer obtains the lock during the read operation. The shared lock obtained under repeatable read is retained until the transaction ends. Read committed: This behavior depends on the settings of the READ_COMMITTED_SNAPSHOT database option: If it is OFF, the transaction acquires the shared locks and retains these new locks during read. If it is ON, the transaction uses row version control. The shared lock obtained under repeatable read is retained until the transaction ends. SNAPSHOT: The transaction must have been started as a SNAPSHOT. The transaction will fail and all changes will be rolled back. Repeatable read: Not changed. SERIALIZABLE: Now the transaction will get the range lock and keep the lock during the transaction. The shared lock obtained under repeatable read is retained until the transaction ends.
SERIALIZABLE	Read uncomitted: The transaction no longer obtains the lock during the read operation. The range lock obtained under SERIALIZABLE is retained until the end of the transaction. Read committed: This behavior depends on the settings of the READ_COMMITTED_SNAPSHOT database option: If it is OFF, the transaction acquires the shared locks and retains these new locks during read. If it is ON, the transaction uses row version control. The range lock is obtained at the SERIALIZABLE level and remains until the transaction ends. SNAPSHOT: The transaction must have been started as a SNAPSHOT. The transaction will fail and all changes will be rolled back. Repeatable read: Now, the transaction obtains the shared lock and keeps the lock during the transaction execution. The range lock obtained under SERIALIZABLE is retained until the end of the transaction. SERIALIZABLE: Not changed.

If the SET transaction isolation level is issued in the stored procedure, trigger, user-defined function, or user-defined type, when the object returns control, the isolation level is reset to the effective level when the object is called. For example, if you set Repeatable read in a batch and the batch calls a stored procedure that sets the isolation level to serializable, when the stored procedure returns the control to the batch, the isolation level will be restored to Repeatable read.

When you use sp_bindsession to bind two sessions, each session retains its isolation level settings. When you use SET transaction isolation level to change the isolation level settings of a session, the settings of any other sessions bound to the session are not affected.

SET transaction isolation level takes effect during execution or running, rather than during analysis.

The optimized large-capacity import operation on a table blocks queries running at the following isolation levels:

• Snapshot
• Read uncommitted
• Read committed with row Version Control
• On the contrary, queries running at these isolation levels will also block the optimization of large-capacity import operations.

Parameters

READ UNCOMMITTED

The specified statement can read rows modified by other transactions but not committed.

Transactions running at the read uncommitted level do not issue a shared lock to prevent other transactions from modifying the data READ by the current transaction. The read uncommitted transaction will not be blocked by the exclusive lock. The exclusive lock will prevent the current transaction from reading rows that have been modified but not committed by other transactions. After this option is set, uncommitted changes can be read, which is called dirty read. Before the transaction ends, you can change the value in the data, or the row can appear in the data set or disappear from the data set. This option is used to set NOLOCK for all tables in all SELECT statements in the transaction. This is the minimum isolation level.

In SQL Server 2005, you can also use any of the following methods to minimize lock contention while protecting transactions from dirty reads and uncommitted data modifications:

• Read committed isolation level, and set the READ_COMMITTED_SNAPSHOT database option to ON.
• SNAPSHOT isolation level.

READ COMMITTED

The specified statement cannot read data modified by other transactions but not committed. This avoids dirty reads. Other transactions can change data between the statements of the current transaction, resulting in non-repeated read and phantom data. This option is the default setting of SQL Server.

The read committed behavior depends on the setting of the READ_COMMITTED_SNAPSHOT database option:

• If READ_COMMITTED_SNAPSHOT is set to OFF (default), the database engine uses a shared lock to prevent other transactions from modifying the row during the read operation of the current transaction. The shared lock also prevents the statement from reading the rows modified by these transactions before other transactions are completed. After the statement is complete, the shared lock is released.
• If you set READ_COMMITTED_SNAPSHOT to ON, the database engine uses row version control to provide a transaction-consistent data snapshot for each statement, because the data exists at the beginning of the statement. Do not use locks to prevent other transactions from updating data.

When the READ_COMMITTED_SNAPSHOT database option is set to ON, you can use the READCOMMITTEDLOCK table to request the shared lock for each statement in the transaction running at the READ_COMMITTED isolation level, instead of row version control.

REPEATABLE READ

The specified statement cannot read the rows modified but not committed by other transactions. It is specified that no other transaction can modify the data read by the current transaction before the current transaction is completed.

A shared lock is set for all data read by each statement in the transaction, and the shared lock is kept until the transaction is completed. This prevents other transactions from modifying any row read by the current transaction. Other transactions can insert new rows that match the search conditions of the statements issued by the current firm. If the current transaction then re-runs the statement, it will retrieve the new row, resulting in phantom read. Because the shared lock is kept until the transaction ends, rather than released at the end of each statement, the concurrency level is lower than the default read committed isolation level. This option is used only when necessary.

SNAPSHOT

Specify that the data read by any statement in the transaction will be consistent in the transaction version of the data that exists at the beginning of the transaction. A transaction can only identify the data changes committed before it starts. The statements executed in the current transaction cannot see the data modifications made by other firms after the current transaction starts. The effect is as if the statement in the transaction has obtained a snapshot of the committed data, because the data exists at the beginning of the transaction.

Unless the database is being restored, the SNAPSHOT transaction will not request a lock when reading data. The SNAPSHOT transaction that reads data does not prevent other transactions from writing data. Transactions that write data do not prevent the SNAPSHOT transaction from reading data.

In the rollback phase of database recovery, if you try to read data locked by another transaction that is being rolled back, the SNAPSHOT transaction will request a lock. Before the Transaction Completes rollback, the SNAPSHOT transaction will be blocked all the time. When the transaction obtains authorization, the lock is immediately released.

You must set the ALLOW_SNAPSHOT_ISOLATION database option to ON to start a SNAPSHOT-level transaction. If you use SNAPSHOT to isolate transactions to access data in multiple databases, you must set ALLOW_SNAPSHOT_ISOLATION to ON in each database.

Transactions starting from other isolation levels cannot be set to the SNAPSHOT isolation level; otherwise, the transaction will be aborted. If a transaction starts at the SNAPSHOT isolation level, you can change it to another isolation level and then return SNAPSHOT. A transaction starts from executing the begin transaction statement.

Transactions running at the SNAPSHOT isolation level can view the changes made by the transaction. For example, if the transaction executes UPDATE on the table and then issues a SELECT statement to the same table, the modified data will be included in the result set.

SERIALIZABLE

Specify:

• The statement cannot read data modified by other transactions but not committed.
• No other transaction can modify the data read by the current transaction before the current transaction is completed.
• Before the current transaction is completed, other transactions cannot use the key value read by any statement in the current transaction to Insert a new row.

The range lock is within the range of key values that match the search criteria of each statement executed in the transaction. This prevents other transactions from updating or inserting any rows to limit any statements executed by the current firm. This means that if you execute any statements in the transaction again, these statements will read the same group of rows. The range lock will be maintained until the transaction is completed. This is the maximum isolation level, because it locks the entire scope of the key and keeps the range lock until the transaction is completed. Because the concurrency level is low, this option should be used only when necessary. This option is used to set HOLDLOCK for all tables in all SELECT statements in the transaction.

Example

In the following exampleTRANSACTION ISOLATION LEVEL. For each subsequent Transact-SQL statement, SQL Server keeps all the shared locks until the end of the transaction.

USE AdventureWorks;GOSET TRANSACTION ISOLATION LEVEL REPEATABLE READ;GOBEGIN TRANSACTION;GOSELECT *     FROM HumanResources.EmployeePayHistory;GOSELECT *     FROM HumanResources.Department;GOCOMMIT TRANSACTION;GO

See

Alter database (TRANSACT-SQL)
DBCC useroptions (TRANSACT-SQL)
Select (TRANSACT-SQL)
Set (TRANSACT-SQL)
Table prompt (TRANSACT-SQL)
Other resources

Custom transaction isolation level
Database Engine isolation level

Help and information

Obtain SQL Server 2005 help