Introduction:
Locking, Locks and Deadlocks are words that a developer/DBA does not want to hear in real life but still there are possibilities that we hear them because of some bad code written. This article takes you on to the type of locks available in SQL server and the ways of avoiding them using the hints available. Before getting into know the types of locks and the hints available, let us see what is meant by a lock and a deadlock.
Lock:
Whenever there is a data change, the object that gets changed is locked by the user who wants to make the change. So that there is no other way that the same object might get changed. Also this ensures data consistency by not allowing the user to read the uncommitted data. Locks are the way to control concurrency among multiple users in SQL Server database.
Deadlocks:
Deadlock occurs when one or more users try to access the same set of resources and neither process has access to it or proceeds further.
Objects that can be locked:
* Rows – entire row from a database table
* Pages – a collection of rows (usually a few kilobytes)
* Extents – usually a collection of a few pages
* Table – entire database table
* Database – the entire database table is locked
Know we know what a lock, dead lock is and the objects that can be locked. Let us look into the type of locks that is in SQL Server.
|
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 table given below shows the locks that are compatible and those that are not compatible. For those locks that are not compatible leads to deadlock as they raise a conflicting lock type that can be processes by the resource that holds the lock.
Locking Hints:
Transact-SQL provides you with a set of table-level locking hints that you can use with SELECT, INSERT, UPDATE, and DELETE statements to tell SQL Server how you want it to lock the table by overriding any other system-wide or transactional isolation level.
FASTFIRSTROW will optimize the query to retrieve the first row of the result set.
HOLDLOCK (equivalent to SERIALIZABLE) applies only to the table specified and only for the duration of the transaction, and will hold a shared lock for this duration instead of releasing it as soon as the required table, data page, row or data is no longer required. HOLDLOCK cannot be used in a SELECT statement with the FOR BROWSE option specified.
NOLOCK (equivalent to READUNCOMMITTED) permits dirty reads. Dirty reads will not issue shared locks and will ignore exclusive locks placed by other processes. It is possible to receive error messages if the read takes place on an uncommitted transaction or a set of pages being rolled back.
PAGLOCK will force the use of a page lock instead of a table lock.
READCOMMITTED specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, which will result in non-repeatable reads and may cause phantom data. READCOMMITTED is the default table hint in SQL Server.
READPAST specifies that locked rows be skipped during the read. READPAST only applies to transactions operating at the default READ COMMITTED isolation level, and will only read past row-level locks. READPAST can only be used in SELECT statements. Normal blocking can be worked around by having transactions read past rows being locked by other transactions.
READUNCOMMITTED (equivalent to NOLOCK) permits dirty reads. Dirty reads will not issue shared locks and will ignore exclusive locks placed by other processes. It is possible to receive error messages if the read takes place on an uncommitted transaction or a set of pages being rolled back.
REPEATABLEREAD specifies that locks be placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction.
ROWLOCK forces the use of row-level locks instead of page or table level locks.
SERIALIZABLE (equivalent to HOLDLOCK) applies only to the table specified and only for the duration of the transaction, and it will hold a shared lock for this duration instead of releasing it as soon as the required table, data page, row or data is no longer required.
TABLOCK specifies that a table lock to be used instead of a page or row level lock. This lock will be held until the end of the statement.
TABLOCKX specifies that an exclusive lock be held on the table until the end of the statement or transaction, and will prevent others from reading or updating the table.
UPDLOCK specifies that update locks will be used instead of shared locks, and will hold the locks until the end of the statement or transaction.
XLOCK specifies that an exclusive lock be used and held until the end of the end of the transaction on all data being processed by the statement. The granularity of XLOCK will be adjusted if it is used with the PAGLOCK or TABLOCK hints.
How to use Hints:
[ FROM { < table_source > } [ ,...n ] ]
< table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
< table_hint > ::=
{ INDEX ( index_val [ ,...n ] )
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}
Leave a Reply to Rob Cancel reply