Microsoft SQL Server – Isolation Levels & Lock Hints

Microsoft SQL Server – Isolation Levels & Lock

Hints

Manu Kapoor

Senior Technical Consultant – Technical Services Group

&
Senior Member – Distinguished Member of Technical Staff

WIPRO Limited ©2015 Internal & Confidential

Microsoft SQL Server – Isolation Levels & Lock Hints

Disclaimer

1. This training material is created for internal training purpose only .

2. This PPT should not be shared to any body outside the organization
through personal Email ID s (Gmail, Hotmail etc.) or through any
electronic storage media.

3. This PPT has been checked for IP and is compliant as per the
norms.

WIPRO Limited ©2015 Internal & Confidential

Microsoft SQL Server – Isolation Levels & Lock Hints

Introduction
Concurrency Effects:

Lost Updates: Lost updates occur when two or more transactions select the same row and then update
the row based on the value originally selected. Each transaction is unaware of the other transactions.
The last update overwrites updates made by the other transactions, which results in lost data.

Uncommitted Dependency (Dirty Read): Uncommitted dependency occurs when a second transaction
selects a row that is being updated by another transaction. The second transaction is reading data that
has not been committed yet and may be changed by the transaction updating the row.

Inconsistent Analysis (Non-repeatable Read): Inconsistent analysis occurs when a second transaction
accesses the same row several times and reads different data each time. Inconsistent analysis is similar
to uncommitted dependency in that another transaction is changing the data that a second transaction
is reading.

Phantom Reads: Phantom reads occur when an insert or delete action is performed against a row that
belongs to a range of rows being read by a transaction. The transaction's first read of the range of rows
shows a row that no longer exists in the second or succeeding read as a result of a deletion by a
different transaction. Similarly, the transaction's second or succeeding read shows a row that did not
exist in the original read as the result of an insertion by a different transaction.

WIPRO Limited ©2015 Internal & Confidential

Microsoft SQL Server – Isolation Levels & Lock Hints

Locking
Locking is an Integral part every RDBMS without which multi-user environment cannot work. Locking is
used to ensure transactional integrity at the cost of level & amount of concurrency it provides to other
transactions.

Few things: In sys.dm_tran_locks we could see

1. resource_type: This tells us what resource in the database the locks are being taken on. It can
be one of these values: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION,
METADATA, HOBT, ALLOCATION_UNIT.
2. request_mode: This tells us the mode of our lock.
3. resource_description: This shows a brief description of the resource. Usually holds the id of the
page, object, file, row, etc. It isn't populated for every type of lock

All shown outputs are from the sys.dm_tran_locks dynamic management view. In some examples it is
truncated to display only locks relevant for the example. For full output you can run these yourself.

WIPRO Limited ©2015 Internal & Confidential

Microsoft SQL Server – Isolation Levels & Lock Hints

Lock Modes:

Shared locks (S)

1. Shared locks are held on data being read under the pessimistic concurrency model.
2. While a shared lock is being held other transactions can read but can't modify locked data.
3. After the locked data has been read the shared lock is released unless we are using a very
restrictive isolation level.

Update locks (U)

1. Update locks are a mix of shared and exclusive locks. When a DML statement is executed SQL
Server has to find the data it wants to modify first & places an update lock on it.
2. Only one update lock can be held on the data at one time, similar to an exclusive lock.
3. Since update lock itself can't modify the underlying data. It has to be converted to an exclusive
lock before the modification takes place. You can also force an update lock with the UPDLOCK
hint

Exclusive locks (X)

1. Exclusive locks are used to lock data being modified by one transaction thus preventing
modifications by other concurrent transactions.
2. You can read data held by exclusive lock only by specifying a NOLOCK hint or using a read
uncommitted isolation level.

Intent locks (I)

1. Intent locks are a means in which a transaction notifies other transaction that it is intending to
lock the data.
2. Their purpose is to assure proper data modification by preventing other transactions to acquire
a lock on the object higher in lock hierarchy. What this means is that before you obtain a lock on
the page or the row level an intent lock is set on the table.
3. This prevents other transactions from putting exclusive locks on the table that would try to
cancel the row/page lock.

Schema locks (Sch)

There are two types of schema locks:

1. Schema Stability lock (Sch-S): Used while generating execution plans. These locks don't block
access to the object data.
2. Schema modification lock (Sch-M): Used while executing a DDL statement. Blocks access to the
object data since its structure is being changed.

Bulk Update locks (BU)

1. Bulk Update locks are used by bulk operations when TABLOCK hint is used by the import.
2. This allows for multiple fast concurrent inserts by disallowing data reading to other transactions.

WIPRO Limited ©2015 Internal & Confidential

Microsoft SQL Server – Isolation Levels & Lock Hints

Lock Compatibility Matrix

Source:
https://technet.microsoft.com/enus/library/jj856598%28v=sql.110%29.aspx?f=255&MSPPError=-
2147217396)

WIPRO Limited ©2015 Internal & Confidential

Microsoft SQL Server – Isolation Levels & Lock Hints

Isolation Levels:

Isolation levels defines how SQL Server control the way Transactions work with each other or in other
words they define amount of tolerance one transaction will have with the other one when they run
concurrently

Read Uncommitted: Is the lowest Isolation level that does not issues shared locks or honor locks on the
resources and subsequently allow you to read UNCOMMITTED data. At the same time, it also allows
other transactions to update the data that you might be reading

Example:
Query Session 1:
BEGIN TRAN
UPDATE Employee SET Salary = 3000 WHERE EmpID = 2
WAITFOR DELAY '00:00:50'
ROLLBACK

Query Session 2:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Dirty Reads
-- The transaction in the first session will be rolled-back
SELECT * FROM Employee

Read Committed: Is the default Isolation Level in SQL Server & under this Isolation data selects will only
return data that is committed (Which is not under-going any change whatsoever)

Key Points:
1. Issues shared locks on the requested data, you may have to wait if other transaction is
modifying the data that you have requested
2. While you are reading the data, other transaction may have to wait until you finish the read and
release the lock

Query Session 1:
BEGIN TRANSACTION
UPDATE Employee SET Salary = 4000 WHERE EmpID = 2
WAITFOR DELAY '00:00:50'
COMMIT

Query Session 2:
-- SELECT is Blocked
SELECT * FROM Employee

WIPRO Limited ©2015 Internal & Confidential

Microsoft SQL Server – Isolation Levels & Lock Hints

Repeatable Read: This is quite similar to Read Committed but with the additional guarantee that if you
issue the same select twice in a transaction you will get the same results both times. It does this by
HOLDING on to the shared locks it obtains on the records it reads until the end of the transaction

Key Points:
1. Issues shared locks on the requested data using HOLDLOCK and does not release until end of the
transaction
2. Other transaction wanting to update same records will have to wait

Query Session 1:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN
SELECT * FROM Employee
WAITFOR DELAY '00:00:50'
SELECT * FROM Employee
ROLLBACK

Query Session 2:
-- Updates are blocked until the first transaction completes
UPDATE Employee SET Salary = 3000 WHERE EmpID = 2

-- HOWEVER Inserts are not blocked (That’s why we have SERIALIZABLE)

INSERT INTO Employee SELECT 7, 7000

Serializable: Repeatable Read does not guarantee that NO new inserts will be happening during the
transaction period and it may result into PHANTOM reads however this isolation guarantees that no
new data will be added.

Key Points:
1. Places range locks on the queried data. This causes any other transactions trying to modify or
insert data touched on by this transaction to wait until it has finished

Query Session 1:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
SELECT * FROM Employee
WAITFOR DELAY '00:00:50'
SELECT * FROM Employee
ROLLBACK

Query Session 2:
-- Inserts are also blocked now !
INSERT INTO Employee SELECT 6, 6000

WIPRO Limited ©2015 Internal & Confidential

Microsoft SQL Server – Isolation Levels & Lock Hints

Snapshot: refer below Key pints:

Key Points:
1. Read Committed: SELECT is blocked if other transaction is updating the records
2. Repeatable Read: Updates are blocked if our Transaction is reading the records
3. Serializable: Inserts are blocked if our Transaction is reading the records

But in this Isolation level, updates or inserts are not blocked and also selects are not blocked. It uses row
versioning is in TEMPDB to maintain committed versions so when data is updated the old version is kept
in TEMPDB

Additionally, when all transactions that started before the changes are complete the previous row
version is removed from tempdb. This means that even if another transaction has made changes you
will always get the same results as you did the first time in that transaction.

Query Session 1:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

ALTER DATABASE TrainingDB SET ALLOW_SNAPSHOT_ISOLATION ON

GO
ALTER DATABASE TrainingDB SET READ_COMMITTED_SNAPSHOT ON
GO

BEGIN TRANSACTION
SELECT * FROM Employee
WAITFOR DELAY '00:00:20'
SELECT * FROM Employee
ROLLBACK

Query Session 2:
-- Inserts are also blocked now !
INSERT INTO Employee SELECT 6, 6000

-- Updates are NOT blocked

UPDATE Employee SET Salary = 20000 WHERE EmpID = 2

-- Inserts are also NOT blocked

INSERT INTO Employee SELECT 8, 8000

WIPRO Limited ©2015 Internal & Confidential

Microsoft SQL Server – Isolation Levels & Lock Hints

Locking Hints:

UPDLOCK
Definition: Use update locks instead of shared locks while reading a table, and hold locks until the end of
the statement or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking
other readers) and update it later with the assurance that the data has not changed since you last read it.

BEGIN TRANSACTION
SELECT * FROM Employee WITH(UPDLOCK) WHERE EmpID = 2
sp_lock

spid dbid ObjId IndId Type Resource Mode Status

58 9 2105058535 1 PAG 1:3525 IU GRANT
58 9 2105058535 1 KEY (9d6bf8154a2a) U GRANT
58 1 1131151075 0 TAB IS GRANT
58 9 2105058535 0 TAB IX GRANT
58 9 0 0 DB S GRANT
58 9 2105058535 1 PAG 1:154 IU GRANT

In the above example:

1. Selection of any row + Insertion of record will be possible; for example select where EmpID in
(1,2), because S & U are compatible lock types
2. Update for EmpID = 2 will be dis-allowed because no more than one session can have U lock on
same data rows (Conflicting lock type)
3. Update for EmpID = 1 will be allowed because this row is not Locked

HOLDLOCK
Definition: Hold a shared lock until completion of the transaction instead of releasing the lock as soon as
the required table, row, or data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE.

BEGIN TRANSACTION
SELECT * FROM Employee WITH(HOLDLOCK)
sp_lock
spid dbid ObjId IndId Type Resource Mode Status
58 9 0 0 DB S GRANT
58 1 1131151075 0 TAB IS GRANT
58 9 2105058535 0 TAB S GRANT

In the above example:

1. Selection of any row will be possible; for example select where EmpID in (1,2)
2. No updates, deletes or Inserts are allowed

WIPRO Limited ©2015 Internal & Confidential

Microsoft SQL Server – Isolation Levels & Lock Hints

TABLOCKX
Definition: Use an exclusive lock on a table. This lock prevents others from reading or updating the table
and is held until the end of the statement or transaction.

BEGIN TRANSACTION
SELECT TOP(10) * FROM EMPLOYEE WITH(TABLOCKX)
sp_lock

spid dbid ObjId IndId Type Resource Mode Status

58 9 0 0 DB S GRANT
58 1 1131151075 0 TAB IS GRANT
58 9 2105058535 0 TAB X GRANT

In the above example, no operation (DML, DQL) will be allowed on the table

ROWLOCK
Definition: Use row-level locks instead of the coarser-grained page- and table-level locks. This is
particularly useful in situations when you want to allow other applications to be able to read the data but
not update them, this will guarantee you that you can update the record with the assurance that no one
else has updated it

We must combine ROWLOCK with other LOCKING Hint to realize the effect such as with UPDLOCK

Without using any other lock, the shared is taken at released

BEGIN TRANSACTION
SELECT * FROM Employee WITH ( ROWLOCK) WHERE EmpID = 2

Without using UPDLOCK, the UPADTE lock is taken & never released
BEGIN TRANSACTION
SELECT * FROM Employee WITH ( ROWLOCK, UPDLOCK ) WHERE EmpID = 2

spid dbid ObjId IndId Type Resource Mode Status

58 9 2105058535 1 KEY (9d6bf8154a2a) U GRANT
58 1 1131151075 0 TAB IS GRANT
58 9 2105058535 0 TAB IX GRANT
58 9 0 0 DB S GRANT
58 9 2105058535 1 PAG 1:153 IU GRANT

XLOCK
Definition: Use an exclusive lock that will be held until the end of the transaction on all data processed by
the statement. This lock can be specified with either PAGLOCK or TABLOCK, in which case the exclusive
lock applies to the appropriate level of granularity.

BEGIN TRANSACTION
SELECT * FROM Employee WITH (XLOCK)
WHERE EmpID = 2

WIPRO Limited ©2015 Internal & Confidential

Microsoft SQL Server – Isolation Levels & Lock Hints

spid dbid ObjId IndId Type Resource Mode Status

58 9 2105058535 1 KEY (9d6bf8154a2a) X GRANT
58 1 1131151075 0 TAB IS GRANT
58 9 2105058535 0 TAB IX GRANT
58 9 2105058535 1 KEY (e94538932c7c) X GRANT
58 9 2105058535 1 KEY (0971161ce8c0) X GRANT
58 9 0 0 DB S GRANT
58 9 2105058535 1 PAG 1:153 IX GRANT

In the above example: Rows having X lock Key will be forbidden for any sort of operation (DML or DQL)
For example:
SELECT * FROM EMPLOYEE WHERE EmpID = 2 -- Fails
SELECT * FROM EMPLOYEE WHERE EmpID = 1 -- Succeeds

NOLOCK
Definition: Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is
possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read.
Dirty reads are possible. Only applies to the SELECT statement.

Session 1:
BEGIN TRANSACTION
UPDATE Employee SET Salary = 5000 WHERE EmpID = 3
sp_lock

spid dbid ObjId IndId Type Resource Mode Status

58 1 1131151075 0 TAB IS GRANT
58 9 2105058535 0 TAB IX GRANT
58 9 0 0 DB S GRANT
58 9 2105058535 1 PAG 1:153 IX GRANT
58 9 2105058535 1 KEY (052c8c7d9727) X GRANT

Session 2:
SELECT * FROM Employee -- This will have to wait until the trasnaction is
either comitted or rolled-back
BUT
SELECT * FROM Employee (NOLOCK)-- This dis-regards exclusive locks but at the
cost of possible dirty read

READPAST
Definition: Skip locked rows. This option causes a transaction to skip rows locked by other transactions
that would ordinarily appear in the result set, rather than block the transaction waiting for the other
transactions to release their locks on these rows. The READPAST lock hint applies only to transactions

WIPRO Limited ©2015 Internal & Confidential

Microsoft SQL Server – Isolation Levels & Lock Hints

operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the
SELECT statement.

Session 1:
BEGIN TRANSACTION
UPDATE Employee SET Salary = 5000 WHERE EmpID = 3
sp_lock

spid dbid ObjId IndId Type Resource Mode Status

58 1 1131151075 0 TAB IS GRANT
58 9 2105058535 0 TAB IX GRANT
58 9 0 0 DB S GRANT
58 9 2105058535 1 PAG 1:153 IX GRANT
58 9 2105058535 1 KEY (052c8c7d9727) X GRANT

Session 2:
SELECT * FROM Employee (READPAST)-- This will display records but will also
skip the one which is being currently locked (052c8c7d9727)

READCOMMITTED
Definition: Same as Read Committed Isolation Level
SELECT * FROM Employee WITH (READCOMMITTED)

READUNCOMMITTED
Definition: Same as Read Uncommitted Isolation Level or NOLOCK
SELECT * FROM Employee WITH (READUNCOMMITTED)

REPEATABLEREAD
Definition: Same as Repeatable Read Isolation Level
SELECT * FROM Employee WITH (REPEATABLEREAD)

WIPRO Limited ©2015 Internal & Confidential