|
|
|
|
In order to become a good T-SQL programmer, you need to know how SQL Server manages resources to guarantee data integrity. There are several different kinds of locks SQL Server can use. Certain locks are compatible with others, for instance, the shared locks, acquired with the SELECT statement are compatible with other shared locks - multiple readers can read the data at the same time. Some other types of locks restrict the rest of the users from reading and / or modifying the data.
Locking with SQL Server represents a trade off between concurrency (and thereby, performance) and data integrity. If you are not concerned about the quality of data being read by your users, then you can allow data reads and modifications at the same time. On the other hand, if you need to have highly consistent results when reading your data, then SQL Server will allow for only one user making modifications at one time, therefore reducing the number of users simultaneously accessing data.
The following table lists the types of locks available with SQL Server:
| Lock Type | Description |
| Intent | The intent lock shows the future intention of the lock manager to acquire locks on a specific unit of data for a particular transaction. SQL Server uses intent locks to queue exclusive locks, thereby ensuring that these locks will be placed on the data elements in the order the transactions were initiated. Intent locks come in three flavors: intent shared (IS), intent exclusive (IX) and shared with intent exclusive (SIX). IS locks indicate that the transaction will read some (but not all) resources in the table or page by placing shared locks. IX locks indicate that the transaction will modify some (but not all) resources in the table or page by placing exclusive locks. SIX locks indicate that the transaction will read all resources, and modify some of them (but not all). This will be accomplished by placing the shared locks on the resources read and exclusive locks on the rows modified. Only one SIX lock is allowed per resource at one time, therefore, SIX locks prevent other connections from modifying any data in the resource (page or table), although they do allow reading the data in the same resource. |
| Shared | Shared locks (S) allow transactions to read data with SELECT statements. Other connections are allowed to read the data at the same time; however, no transactions are allowed to modify data until the shared locks are released. Shared locks are normally released as soon as the data is read. However, there are ways to override this default behavior through query hints and transaction isolation levels. |
| Update | Update locks (U) are acquired just prior to modifying the data. If a transaction modifies a resource, then the update lock is escalated to an exclusive lock, otherwise it is converted to a shared lock. Only one transaction can acquire update locks to a resource at one time. Using update locks prevents multiple connections from having a shared lock that wishes to eventually modify a resource using an exclusive lock. Shared locks are compatible with other shared locks, but are not compatible with Update locks. Update locks are also used for inserts into a table with a clustered key. |
| Exclusive | Exclusive locks (X) completely lock the resource from any type of access including reads. They are issued when data is being modified through INSERT, UPDATE and DELETE statements. You should try to minimize the time during which any resource is locked exclusively by making your data modifications as short as possible. Exclusive locks are usually the cause of blocking in the applications. |
| Schema | Schema modification locks (Sch-M) are acquired when data definition language (DDL) statements are being executed. This includes modifying tables or views through adding columns, dropping columns, adding or dropping constraints, etc. Schema stability locks (Sch-S) are acquired when queries are being compiled; these will not block any other types of locks, including exclusive locks. Therefore, schema locks will not cause any blocking. However, when queries are compiled, the tables affected by the queries cannot be altered through DDL statements. |
| Bulk Update | Bulk update locks (BU) are used when performing a bulk-copy of data into a table with the TABLOCK hint. These locks improve performance while bulk copying data into a table; however, they reduce concurrency by effectively disabling any other connections to read or modify data in the table. Another way of acquiring BU locks is by specifying the "table lock on bulk load" option with the system stored procedure sp_tableoption. |
The majority of transactional applications will require a variety of locks being acquired and released on resources. Therefore, it is useful to know which locks are compatible with other types of locks. The following table shows the compatibility among different types of locks:
**Existing Lock Type (Across)
**Lock Type Requested (Down)
