More About Transactions and Composite Transact-SQL Constructs - Transaction Isolation Levels and Hints
(Page 4 of 6 )
You can change the default behavior of SQL Server using transaction isolation levels or lock hints. Transaction isolation levels set locking at the connection level, and lock hints set locking at the statement level. SQL Server can work on five different transaction isolation levels:
- Serializable The highest level in which transactions are completely isolated. The system behaves as though the transactions are occurring one after another. SQL Server will hold locks on both data and key records until the end of the transaction. This may lead to some performance issues.
- Repeatable Read Forces SQL Server to place shared locks on data records and hold them until the transaction is completed. Unfortunately, it allows phantoms, which occur when a transaction reads a range of records. There is no guarantee that some other concurrent transaction will not add records that fall in the range or modify keys of records so that they fall out of the range. If the uncommitted transaction repeats the read, the result will be inconsistent.
- Read Committed The default level in SQL Server. SQL Server places shared locks while reading. It allows phantoms and nonrepeatable reads. There is no guarantee that the value of the record that a transaction reads multiple times during execution will stay consistent. Some other transaction could change it.
- Read Uncommitted The lowest level of isolation in SQL Server. It ensures that physically corrupt data is not read. SQL Server will not place shared locks, and it will ignore exclusive locks. You will have the fewest performance issues when using this level, but you will also likely have many data integrity problems. It allows phantoms, nonrepeatable reads, and dirty reads (everybody can see the content of the changed record, even if a transaction is not yet committed and could potentially be rolled back).
- Snapshot A new level of isolation introduced in SQL Server 2005. It is designed to support databases that use row versioning to reduce blocking of readers and writers. The engine stores versions of rows before they were modified in tempdb and serves them to readers until the transaction is completed. This reduces blocking time but increases the need for processing power and memory. It could lead to concurrency conflicts. Readers do not issue shared locks.
NOTE
There is one database setting that changes the behavior of SQL Server by using the same row versioning mechanism as Snapshot isolation level. It is also introduced in SQL Server 2005. In some ways it is like Snapshot and in some ways like Read Committed isolation level. Like Snapshot, it is based on row versioning. Since this is a database setting, it will preserve earlier versions of all rows changed in the database (not just rows that are changed in a session set to Snapshot isolation level). Like with Read Committed, SQL Server can return to readers consistent versions of rows (before the transaction). But since SQL Server can return to readers earlier versions of all rows that are still being changed or locked in transactions, readers do not have to wait for transactions to be completed and therefore do not have to issue read locks.
The isolation level is specified in the Set Transaction Isolation Level statement. For example:
Set Transaction Isolation Level Repeatable Read
Locking hints change the behavior of the locking manager as it processes a single Transact-SQL statement. They overwrite behavior set by the transaction isolation level. The following table describes hints that can be used to control locking:
Hints | Description |
Holdlock or Serializable | Holds a shared lock until a transaction is completed. The lock will not be released when the resource is no longer needed, but rather when the transaction is completed. |
Nolock | This hint applies only to Select statements. SQL Server will not place shared locks and it will ignore exclusive locks. |
Updlock | Uses update instead of shared locks while reading a table. |
Rowlock | Specifies the granularity of locks at the row level. |
Paglock | Specifies the granularity of locks at the page level. |
Tablock | Specifies the granularity of locks at the table level. |
Tablockx | Specifies the granularity of locks at the table level and the type of lock to be exclusive. |
Readcommitted | Equivalent to the default isolation level (Read Committed). |
Readpast | This hint is applicable only in Select statements working under the Read Committed isolation level. Result sets created with this hint will not contain records locked by other transactions. |
| Readuncommitted | Equivalent to the Read Uncommitted isolation level. |
Repeatableread | Equivalent to the Repeatable Read isolation level. |
| |
Locking hints can be used in Select, Insert, Update, or Delete statements. They are set after the table reference in SQL statements (for example, in the From clause of a Select statement or in the Insert clause of an Insert statement). Their scope is just the table that they are used for. For example, the following command will hold a lock until the transaction is completed:
Select *
From Inventory With (HOLDLOCK)
Where InventoryId = @intInventoryId
Nobody will be able to change data records that were read and keys that match the criteria of this table until the transaction is completed. Therefore, this table cannot have phantoms, nonrepeatable reads, or dirty reads.
The next example demonstrates the use of hints in an Update statement and the use of more than one hint in a statement:
Update Inventory With (TABLOCKX, HOLDLOCK)
Set StatusId = 4
Where StatusId = @intStatusId
The complete table will be locked for the duration of the transaction.
Next: Distributed Transactions >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter five of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.
|
|
