Author: Sunil AgarwalReviewers: Kun Cheng, Chuck Heinzelman, Shaun Tinline-Jones, Sanjay Mishra, Kevin Liu
Recently I was working with a customer who was surprised to see blocking when accessing a data row which was not locked? Let me describe the scenario using an example.
-- create a simple table for our examples
create table basic_locking (c1 int, c2 int, c3 int)
-- insert 500 rows into the table
declare @i int = 0
while (@i < 500)
insert into basic_locking values (@i, @i+ 100, @i+10000)
set @i += 1
-- Session-1: locks the data rows that satisfies the
-- predicate C1 = 1. Based on the data inserted, there
-- is only one row that qualifies
update basic_locking set c2 = 100 where c1 = 1
-- Session-2: Set the isolation level to default
set transaction isolation level read committed
-- select a different row and show that it blocks
select c3 from basic_locking where c1 = 2
The customer wondered why session-2 is seeing blocking even though no other transaction has locked the row satisying the predicate c1=2? To understand this, let us look at the query plan for the SELECT operation
Since there is no index on this table, the optimizer chooses the table scan to execute the query in session-2. As part of this plan, the predicate (C1 = 2) is applied to each row (including the one that has C1 set to 1). Since the data row with (C1 = 1) is already exclusively locked (X) by the transaction in session-1, it will block session-2. I strongly recommend customers to look at query plans while troubleshooting blocking issues.
Solutions: You have multiple choices here.
1) Use NOLOCK locking hint
select c3 from basic_locking with(NOLOCK)where (c1 = 2).
However, it requires an application change
2) Enable RCSI (read committed snapshot). This provide non blocking access to the data row with (c1 = 2). This will work and will not require application change.
3) Enable SI (Snapshot Isolation) but it will require application change.
4) Create an index on column C1. The blocking will be eliminated if optimizer chooses the index to execute SELECT statement in session-2.
create nonclustered index basic_locking_nci on basic_locking(c1)
-- force the access through an index to remove
from basic_locking with (index(basic_locking_nci))
where c1 = 2
Here is how the query plan looks like
There is a catch here. If optimizer does not choose this index, you may still get blocking. In that case, you will need to change your application for force the index hint or use plan-guide to direct optimizer to choose this index. Note, with the new index, the DML operations (Insert, Delete, and Update) will incur overhead of maintaining additional index.
Loved the article Sunil, specially the workaround