Concurrency Series: Why do I get blocked when no one has locked the row(s) being queried?

  • Article

 Author: Sunil Agarwal
Reviewers: 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.

Example:

-- create a simple table for our examples

create table basic_locking (c1 int, c2 int, c3 int)

go

-- insert 500 rows into the table

declare @i int = 0

while (@i < 500)

begin

     insert into basic_locking values (@i, @i+ 100, @i+10000)

     set @i += 1

end

-- Session-1: locks the data rows that satisfies the

-- predicate C1 = 1. Based on the data inserted, there

-- is only one row that qualifies

begin tran

     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)

go

-- force the access through an index to remove

-- blocking

select c3

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.

Thanks

Sunil Agarwal