Locking is a major part of any relational database, including SQL Server. The concept of Locking and how database engine issues them and how can you troubleshoot to determine some of the problems your DBMS may be facing is quite large and complex. As a result, we will be covering it in multiple parts. In this part, I will explain the types of locks available to SQL Server and how they are used. Subsequent posts will cover them in more details.
Types of Locks
Shared Locks
Shared locks are issued at the database level when you connect to a database. They are also issued when you run a SELECT statement against a table i.e. when you read data. Shared locks are issued when you are using the pessimistic concurrency model. When a shared lock is issued, more than one transaction can read the data but the data cannot be modified. After the data has been read, the lock is released, unless you run your queries with READCOMMITTED OR READCOMMITTEDLOCK locking hint (more on these later) or you have a more restrictive isolation level.
Update Locks
They are a combination of shared and exclusive locks. When you issue an update statement, SQL server first searches for the data to be modified. Since SQL engine has to first read the data to be modified and then modify it, it can in theory issue a shared lock when reading the data and then convert it to exclusive lock when updating this data, but this could result in a deadlock, hence instead of using a shared lock then upgrading it to exclusive lock, SQL uses an Update Lock. It is very similar to an exclusive lock in that only one update lock can be held on the data at one time. The difference between an update lock and exclusive lock is that the before the data is modified, update lock has to be converted to an exclusive lock, since an update lock cannot modify the data. You can also provide an UPDLOCK hint in your statements to force an update lock.
Exclusive Locks
These locks are issued to lock the data that is being modified by one transaction to prevent modifications by other concurrent transactions. You cannot even read the data held by exclusive locks, unless you provide "NOLOCK" hint to your select query or you are using read uncommitted isolation level. Since data must be first read before it can be modified, exclusive locks are accompanied by shared or update locks on the same data. NOLOCK hint allows you to read data locked by an exclusive lock, but you may end up reading dirty data i.e. before the data is committed.
Intent Locks
Intent Locks are a mechanism for one transaction to notify others that it is intending to lock the data. Generally a transaction will issue an intent to lock the object that is higher in lock hierarchy than what is currently locked by this transaction. For example, if a transaction has exclusive lock at the row level, it may issue an intent lock on the page level or table level. This prevents others transactions from locking the higher objects. The way this works is before a transaction obtains a lock at the row or page level, it first sets an intent lock on the table. Thus, you get a more granular lock (update lock or exclusive lock) at the lower level after setting an intent level lock on its parent.
Schema Locks
When database engine is generating execution plans, it issues Schema Stability Lock. It doesn't block access to the underlying data. Schema modification lock is used when a DDL statement (Data Definition Language) is being executed i.e. schema is being modified. This lock blocks the access to the underlying data.
Bulk Update Locks
These locks are issues when performing bulk operations and you use TABLOCK hint. They allows for multiple inserts concurrently and prevent data read by other transactions. In essence this is a table lock.
Conversion Locks
Converting one type of lock to other lock results in a conversion lock. There are three types of conversion locks.
- Shared With Intent Exclusive - A transaction with a shared lock also has exclusive intent lock on some pages/rows.
- Shared with Intent Update - A transaction with a shared lock also has an update intent lock on some pages/rows.
- Update with Intent Exclusive- A transaction with an update lock also has an exclusive intent lock on some pages/rows.
When using serializable transaction isolation level, any query that is executed more than once in a transaction must obtain the same set of rows. For example, if a query when executed first time returns 10 rows, any subsequent execution of the same query within the same transaction must return the same 10 set of rows. If this query tries to fetch a row that doesn't exist, it cannot be inserted by other transactions until the first transaction reading the row completes, because if the second transaction was allowed to insert a row, it would appear as a phantom to the first transaction. A key-range lock locks the index rows and the ranges between those index rows. Any attempt to insert/update or delete any row within this range by a second transaction would modify the index, the key-range lock blocks the second transaction until the first transaction completes.
There are two types of Key-Range Locks
- RangeX-X - Exclusive lock is issued on the interval between the keys and an exclusive lock is issued on the last key in the range.
- RangeS-U - Shared lock is issued on the interval between the keys and update lock is issued on the last key in the range.
Spin Locks
A light-weight locking in which data is not locked but the transaction waits for a short period of time for a lock to be free if data is already locked. It is a mutual exclusivity mechanism to reduce the context switching between multiple threads.
Lock Granularity
Locks can be issued against a table, page or a row. If a table has a clustered index, there is also a Key lock. When you lock at the lower level with intent to lock at the higher level, it increases concurrency since multiple operations can get the locks they need. It also depends on the isolation level we choose. More restricted the isolation level, more higher level locks to keep data intact. SQL Server allows you to pass hints such as NOLOCK, ROWLOCK, PAGLOCK or TABLOCK to override the default locking based on the isolation level. Although I would discourage using locking hints except in very rare situations.
As I mentioned at the beginning of this post, Locking is a complex topic and we will be covering it in multiple posts. So, watch out for the next post.
Thank you and your comments are welcome!
No comments:
Post a Comment
As always, your comments are welcome and appreciated!