Transactions and Locking

SQL Server issues and holds onto locks for the duration of a transaction to ensure the isolation and consistency of the modifications. Data modifications that occur within a transaction will acquire exclusive locks, which are then held until the completion of the transaction. Shared locks, or read locks, are held for only as long as the statement needs them; usually, a shared lock is released as soon as data has been read from the resource (row, page, table). The length of time a shared lock is held can be modified by the use of keywords such as HOLDLOCK in a query. If this option is specified, shared locks are held onto until the completion of the transaction.

What this means for database application developers is that you should try to hold onto as few locks or as small a lock as possible for as short a time as possible to avoid locking contention between applications and to improve concurrency and application performance. The simple rule when working with transactions is "Keep them short and keep them simple!" In other words, do what you need to do in the most concise manner in the shortest possible time. Keep any extraneous commands that do not need to be part of the logical unit of work ”such as select statements, dropping temp tables, setting up local variables , and so on ”outside of the transaction.

To modify the manner in which a transaction and its locks can be handled by a SELECT statement, you can issue the SET TRANSACTION ISOLATION LEVEL statement. This statement allows the query to choose how much it is protected against other transactions modifying the data being used. The SET TRANSACTION ISOLATION LEVEL statement has the following mutually exclusive options:

• READ COMMITTED ” This setting is the default for SQL Server. Modifications made within a transaction are locked exclusively, and the changes cannot be viewed by other user processes until the transaction completes. Commands that read data only hold shared locks on the data for as long as it is reading it. Because other transactions are not blocked from modifying the data after you have read it within your transaction, subsequent reads of the data within the transaction might encounter non-repeatable reads or phantom data.

• READ UNCOMMITTED ” With this level of isolation, one transaction can read the modifications made by other transactions prior to being committed. This is, therefore, the least restrictive isolation level, but one that allows the reading of dirty and uncommitted data. This option has the same effect as issuing NOLOCK within your SELECT statements, but it only has to be set once for your connection. This should never be used in an application in which accuracy of the query results is required.

• REPEATABLE READ ” When this option is set, as data is read, locks are placed and held on the data for the duration of the transaction. These locks prevent other transactions from modifying the data you have read, so that you can carry out multiple passes across the same information and get the same results each time. This isolation level is obviously more restrictive and can block other transactions. However, although it prevents non-repeatable reads, it does not prevent the addition of new rows or phantom rows because only existing data is locked.

• SERIALIZABLE ” This option is the most restrictive isolation level because it places a range lock on the data. This prevents any modifications to the data being read from until the end of the transaction. It also avoids phantom reads by preventing rows from being added or removed from the data range set.

For more information on lock types, locking behavior, and performance, see Chapter 38, "Locking and Performance."