Locks and Isolation levels

 Lock types 

SQL Server can lock a number of different types of resource, the most obvious being tables (OBJECT locks), pages (PAGE locks), and rows (RID or KEY locks), in order of- increasing granularity. Locks are granted and released on these objects as needed, in order to satisfy the requirements of the isolation levels in use by the various sessions. 

In the locking hierarchy, row and key locks are the lowest level, most granular, forms of lock. The more granular the lock, the higher the degree of concurrent access which can be supported. With that, however, comes a higher memory overhead, from having to manage a large number of individual locks. SQL Server automatically chooses locks of the highest possible granularity, suitable for the given workload. However, if too many individual locks are being held on an index or heap, or if forced to do so due to memory pressure, SQL Server may use lock escalation to reduce the total number of locks being held. For example, a large number of individual row locks may be escalated to a single table lock, or a number of page locks may be escalated to a table lock (escalation is always to a table lock). 

 

One other lock type that needs discussion here is the database-level lock. People are often concerned when they see a large number of database-level locks listed in the results returned by querying the lock-related DMVs. In reality this is of no concern at all. Every session takes a shared database lock on the database to which it is connected. 

This is to ensure that the database cannot be restored, dropped, closed, or detached while there are still sessions using it. These will not cause blocking, except in the case of restoring, dropping, altering, detaching or closing an in-use database. 

 

 

Lock escalation 

Lock escalation is the process in which SQL Server escalates a low-level, granular lock to a higher-level lock. The locks can start as either row or page locks; it makes no difference to the process of lock escalation. If SQL decides to escalate the locks it will in all cases escalate straight to table locks (except when this behavior is explicitly altered using the LOCK_ESCALATION option). Locks do not, under any circumstances, escalate from row to page and then to table. 

In some cases, lock escalation can cause blocking because SQL has escalated locks, and so locked the entire table, with the result that concurrent access to that table is restricted. To identify lock escalations, SQL Trace or Profiler can be used with the Lock:Escalation event, in conjunction with the SP:Started or T-SQL:StmtStarted and SP:Completed or T-SQL:StmtCompleted events, to try to correlate the lock escalation 

with currently executing statements and procedures. 

If this event is logged and there is measurable blocking around the time of the event being logged, then lock escalation may be causing problems, and the procedures that were running at the time of the event being logged may need to be optimized to reduce the amount or duration of the locking. 

 

 

Concurrency and the transaction isolation levels 

The degree to which concurrent sessions can access the same resource simultaneously is dictated by the transaction isolation levels in force for the database sessions that are accessing the database. 

In the ANSI standards, the isolation levels are defined by what data anomalies they allow, not the manner they are enforced. Each isolation level (from the least restrictive to the most restrictive) allows fewer data anomalies. SERIALIZABLE must allow no data anomalies; REPEATABLE READ is defined as an isolation level that only allows phantom rows; READ COMMITTED is identified as an isolation level that allows phantom rows and non-repeatable reads; READ UNCOMMITTED is identified as an isolation level that allows phantom rows, non-repeatable reads and dirty reads. 

 

READ UNCOMMITTED 

In this isolation level SQL Server takes no shared locks at all. Since it takes no locks, a session will not get blocked by exclusive locks. Update and exclusive locks are still taken when modifying data. Because no shared locks are taken, this isolation level allows dirty reads (reads of uncommitted data) as well as all of the data anomalies of the higher isolation levels. 

 

READ COMMITED 

This is the default mode of operation. SQL Server will prevent "dirty reads" (i.e. it will ensure that transactions can only return committed data). To do this, SQL Server will acquire short-lived S locks on each row that is read, releasing the lock as processing of the statement moves on to the next row, although it may hold these locks till the whole statement has finished processing, should that be necessary in order to guarantee consistent results. The shared locks are retained for, at most, the duration of the executing statement, even if the transaction of which the statement is a part is still active. 

So if the same statement is run again, as part of the same transaction, it could return different results. This is the data anomaly known as a "non-repeatable read." 

 

REPEATABLE READ 

Shared locks are held for longer; for the duration of whole transaction. So, if a statement runs again within the same transaction, you'll get the same result. Hence this isolation level no longer allows non-repeatable reads. It still allows for new rows to have been inserted in the meantime that match the query criteria. These new rows that appear within a result set are known as phantom rows. 

 

SERIALIZABLE 

Transaction A will never see any of the effects of Transaction B for the entire duration of Transaction A. In order to enforce this level, Transaction A will cause SQL Server to acquire key-range locks on rows that have been read, which prevents any other transaction from modifying them, or inserting new rows, until Transaction A completes. The SERIALIZABLE isolation level allows for no data anomalies at all, at the cost of severely restricting the degree to which the database can support concurrent access. 

 

 

SNAPSHOT Isolation levels 

There are two optimistic concurrency isolation levels: READ_COMMITTED_SNAPSHOT 

and SNAPSHOT. They are often confused with each other, so I will try to touch on the 

significant differences here. Both of these isolation levels use row versions for read 

consistency, not locks. So read queries take no locks. Therefore, instead of blocking when they encounter a row that is subject to an exclusive lock, they will simply "read around" the lock, retrieving from the version store (in TempDB) the version of the row consistent with a certain point in time, either the the time the statement started, or the time at which the parent transaction started. 

 

READ_COMMITTED_SNAPSHOT is an optimistic concurrency version of the READ COMMITTED isolation level, so it allows the same data anomalies. However, as discussed earlier, if a statement running under the traditional READ COMMITTED isolation level encounters data that is being modified by another transaction, it must wait until those changes are either committed or rolled back before it can acquire a shared lock and proceed. In the READ_COMMITTED_SNAPSHOT isolation level, when a locked row is encountered, SQL Server fetches the version of the row from the version store, as it existed when the statement began.  

In other words, data returned will reflect only what was committed at the time the current statement began. This means that consistency is guaranteed within the statement, not within the transaction as a whole. 

 

SNAPSHOT isolation is an optimistic concurrency version of the SERIALIZABLE isolation 

level. As such, it allows no data anomalies at all. In this isolation level, when a locked row is encountered, SQL fetches the latest row version as it existed when the transaction began. In other words, data returned will reflect only what was committed at the time the current transaction began. 

 

The other difference between the two levels is in how they are enabled. If the database option READ_COMMITTED_SNAPSHOT is enabled, then the database's default isolation level becomes READ_COMMITTED_SNAPSHOT, and hints or explicit SET TRANSACTION 

ISOLATION LEVEL statements will be needed to get the READ COMMITTED isolation level(which will use locks). If the database option ALLOW_SNAPSHOT_ISOLATION is enabled, then all that happens is that sessions may request the SNAPSHOT isolation using a SET TRANSACTION ISOLATION LEVEL statement.