MSSQL Transaction ISOLATION Level detailed (SET TRANSACTION isolation Levels)

Controls the locking behavior and row versioning behavior of SQL Server statements issued by connections to Transact-SQL.

Transact-SQL Syntax conventions

Grammar

-- Syntax for SQL Server and Azure SQL DatabaseSET TRANSACTION ISOLATION LEVEL    { READ UNCOMMITTED    | READ COMMITTED    | REPEATABLE READ    | SNAPSHOT    | SERIALIZABLE    }

-- Syntax for Azure SQL Data Warehouse and Parallel Data WarehouseSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Parameters

READ Uncommitted
Specifies that the statement can read rows that have been modified by other transactions but have not yet been committed.

A transaction that runs at the read uncommitted level does not issue a shared lock to prevent other transactions from modifying the data read by the current transaction. READ UNCOMMITTED transactions are not blocked by exclusive locks, and exclusive locks prohibit the current transaction from reading rows that have been modified but not yet committed by other transactions. After you set this option, you can read uncommitted modifications, which are called dirty reads. before the end of the transaction, you can change the values in the data, and the rows can appear in the dataset or disappear from the data set. This option works the same as setting NOLOCK on all tables in all SELECT statements within a transaction. This is the least restrictive level in the isolation level.

in SQL Server, you can also use any of the following methods to minimize lock contention while protecting transactions from dirty READ UNCOMMITTED data modifications:

• READ COMMITTED Isolation level and set the READ_COMMITTED_SNAPSHOT database option to ON.

• snapshot isolation level.

  read COMMITTED
  Specifies that the statement cannot read data that has been modified by another transaction but has not yet been committed. Other transactions can change data between the various statements of the current transaction, resulting in non-repeatable reads and virtual data. sql Server.

  read COMMITTED behavior depends on Read_ Committed_snapshot Settings for database options:

• If Read_committed_snapshot is set to OFF (the default), the database engine uses a shared lock to prevent other transactions from modifying rows during the read operation of the current transaction. shared locks also prevent statements from reading rows modified by these transactions before other transactions are completed. the shared lock type determines when it will be released. The row lock is disposed before the next row is processed. page locks are released when the next page is read, and table locks are released when the statement completes.

  Note

  if Read_committed_snapshot is set to ON, the database engine uses row versioning to provide a transactionally consistent snapshot of the data for each statement because the data exists at the beginning of the statement. do not use locks to prevent other transactions from updating data.

  Snapshot Isolation supports FILESTREAM data. in snapshot isolation mode, the FILESTREAM data read by any statement in a transaction will be a transactionally consistent version of the data that existed at the beginning of the transaction.

  When the Read_committed_snapshot database option is set to ON, you can use the Readcommittedlock table hint to request a shared lock for each statement in a transaction running on the READ COMMITTED isolation level, rather than row versioning.

Note

when you set the Read_committed_snapshot option, only the connection that executes the ALTER database command is allowed in the database. No other open connections are allowed in the database until the ALTER database is complete. The database does not have to be in single-user mode.

Repeatable READ
Specifies that a statement cannot read a row that has been modified by another transaction but has not yet been committed, and specifies that no other transaction can modify the data read by the current transaction before the current transaction completes.

a shared lock is set on all data read by each statement in the transaction, and the shared lock remains until the transaction completes. This prevents other transactions from modifying any rows that are read by the current transaction. Other transactions can insert new rows that match the search criteria of the current transaction issuing statement. if the current transaction then retries to execute the statement, it retrieves the new row, resulting in a virtual read. The concurrency level is lower than the default READ COMMITTED isolation level because the shared lock remains at the end of the transaction instead of being released at the end of each statement. This option is used only when necessary.

SNAPSHOT
specifies that the data read by any statement in the transaction will be a transactionally consistent version of the data that existed at the beginning of the transaction. transactions can only identify data modifications that were committed before they were started. statements executed in the current transaction will not see data modifications made by other firms after the start of the current transaction. The effect is as if the statement in the transaction obtains a snapshot of the submitted data, because the data exists at the beginning of the transaction.

the SNAPSHOT transaction does not request a lock when the data is being read, unless the database is being recovered. SNAPSHOT transactions that read data do not prevent other transactions from writing to the data. transactions that write data do not prevent SNAPSHOT transactions from reading data.

in the rollback phase of database recovery, if you attempt to read data that is locked by another transaction that is being rolled back, the SNAPSHOT transaction will request a lock. the SNAPSHOT transaction is blocked until the transaction has finished rolling back. when a transaction is authorized, the lock is released immediately.

The allow_snapshot_isolation database option must be set to on to start a transaction that uses the SNAPSHOT isolation level. If you use SNAPSHOT isolation level transactions to access data from multiple databases, you must set allow_snapshot_isolation to on in each database.

transactions that start with other isolation levels cannot be set to the SNAPSHOT isolation level, which will cause the transaction to abort. If a transaction starts at the SNAPSHOT isolation level, you can change it to another isolation level, and then return to SNAPSHOT. The transaction starts when the data is accessed for the first time.

transactions that run under the SNAPSHOT isolation level can view changes made by the transaction. For example, if a transaction performs an UPDATE on a table and then issues a SELECT statement on the same table, the modified data is included in the result set.

Note

in snapshot isolation mode, the FILESTREAM data read by any statement in a transaction will be a transactionally consistent version of the data that existed at the start of the transaction rather than at the beginning of the statement.

SERIALIZABLE
Please specify the following:

• Statement cannot read data that has been modified by another transaction but has not yet been committed.

• No other transaction can modify the data read by the current transaction before the current transaction completes.

• No other transaction can insert a new row with the key value read by any statement in the current transaction until the current transaction completes.

  A range lock is within the range of key values that match the search criteria for each statement executed in the transaction. This prevents other transactions from being updated or inserting any rows, thereby qualifying any statements executed by the current transaction. This means that if any of the statements in the transaction are executed again, the statements will read the same set of rows. The range lock is maintained until the transaction is complete. This is the most restrictive isolation level because it locks the entire range of keys and maintains a range lock until the transaction is complete. because the concurrency level is low, you should use this option only when necessary. This option works the same as setting HOLDLOCK on all tables in all SELECT statements within a transaction.

Remarks

you can set only one Isolation level option at a time, and the options you set will always work on that connection until you explicitly change the option. all read operations performed in a transaction are run under the rules of the specified isolation level, unless the table hint in the FROM clause of the statement specifies additional locking behavior or versioning behavior for the table.

The transaction isolation level defines the type of lock that can be obtained for a read operation. shared locks acquired for read COMMITTED or repeatable read are typically row locks, although row locks can be promoted to page or table locks when reading a large number of rows in a page or table. If a row is modified by a transaction after it has been read, the transaction acquires an exclusive lock to protect the row, and the exclusive lock remains until the transaction completes. For example, if the 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.

during a transaction, you can switch transactions from one isolation level to another at any time, except in one case. that is, you cannot do this when you change from either isolation level to SNAPSHOT isolation. Doing so will cause the transaction to fail and roll back. However, transactions initiated in SNAPSHOT isolation can be changed to any other isolation level.

After you change a transaction from one isolation level to another, the resources that are read after the change are protected according to the rules of the new level. Resources Read before the change will continue to be protected at the previous level of the rule. For example, if a transaction is changed from read COMMITTED to SERIALIZABLE, the shared lock acquired after the change is persisted until the transaction ends.

if SET TRANSACTION isolation level is emitted in a stored procedure or trigger, the isolation level is reset to the levels that are valid when the object is called when the object returns control. For example, if repeatable READ is set in a batch and the batch calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level reverts to repeatable READ when the stored procedure returns control to the batch.

Note

user-defined functions and common language Runtime (CLR) user-defined types cannot execute SET TRANSACTION isolation level. However, you can use table hints to override the isolation level. For more information, see table hints (Transact-SQL).

when you use sp_bindsession to bind two sessions, each session retains its own isolation level setting. When you change the isolation level setting for a session by using Set TRANSACTION isolation, the settings for any other sessions bound to that session are not affected.

The SET TRANSACTION isolation level will take effect at execution or runtime, rather than at parse time.

optimized bulk load operations for heaps block queries that run under the following isolation levels:

• SNAPSHOT

• READ UNCOMMITTED

• Using row versioning for READ COMMITTED

  Conversely, queries running under these isolation levels block optimized bulk load operations on the heap. For more information about bulk load operations, see batch Import and Export data (SQL Server).

  The following transaction isolation levels are supported for FILESTREAM-enabled databases.

Isolation Level	Transact SQL Access	File system Access
Non-committed read	SQL Server	Not supported
Read Committed	SQL Server	SQL Server
REPEATABLE READ	SQL Server	Not supported
Serializable	SQL Server	Not supported
Snapshot of Read commit	SQL Server	SQL Server
Snapshot	SQL Server	SQL Server

Example

The following example sets the session TRANSACTION ISOLATION LEVEL . SQL Server maintains all shared locks until the end of the transaction for each subsequent Transact-SQL statement.

USE AdventureWorks2012;  GO  SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  GO  BEGIN TRANSACTION;  GO  SELECT *       FROM HumanResources.EmployeePayHistory;  GO  SELECT *       FROM HumanResources.Department;  GO  COMMIT TRANSACTION;  GO  

MSSQL Transaction ISOLATION Level detailed (SET TRANSACTION isolation Levels)