Concurrency Series: Minimizing blocking between updaters

Author: Sunil Agarwal
Reviewers: Kun Cheng, Chuck Heinzelman, Shaun Tinline-Jones, Sanjay Mishra, And Kevin Liu

When a transaction T1 modifies a row, it takes an X lock on the row for the duration of the transaction. If another concurrent transaction T2 wants to modify the same row, it gets blocked waiting for T1 to complete. It is easy to understand why this blocking is necessary. SQL Server does not know the fate of T1 until it completes so it forces T2 to wait. However, there are situations where a transaction updating a row gets blocked by a transaction updating a totally different row. Let me illustrate this using the following 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: The update below wants to update a different row

-- but gets blocked on the X lock held by session-1.

--

update basic_locking set c2 = 1000 where c1 = 2

To understand, why it is blocked, let us first take a look at the query plan for the update statement in session-2. The query plan shows that the query optimizer has chosen a table scan for the udpate because there is no index on column C1. With the result, the UPDATE statement in session-2 gets blocked while acquiring a UPDLOCK on the row with C1 =2.

The trick of using NOLOCK does not work here because update statement forces the use of UPDLOCK and it will generate the error as follows:

update basic_locking with (NOLOCK)

set c2 = 1000 where (c1 = 2)

Msg 1065, Level 15, State 1, Line 15

The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.

The second trick of using RCSI does not work either and the update gets blocked behind X lock by session-1 on row with (C1 = 1). The reason is that the following UPDATE statement executes at a higher isolation level than read committed.

update basic_locking set c2 = 1000 where c1 = 2

Here is the output showing the blocking.

Solutions: Here are two choices to address this issue

1) Create an index on column C1 and make sure it gets used either using index hint or using a plan guide. When the index on column C1 is chose, the update statement will directly access the qualifying row with (C1 = 2) and therefore will not get blocked.

2) You can execute the update in session-2 under snapshot isolation level as follows

-- set isolation level to SNAPSHOT

set transaction isolation level snapshot

begin tran

     -- enable Snapshot Isolation and now update the row

     -- without blocking on session-1

     update basic_locking set c2 = 1000 where c1 = 2

The transaction under SI does not take UPDLOCK and therefore it does not get blocked on session-1 and can successfully updates the qualifying row with C1 = 2. However, like under any other transaction, an X lock is acquired on the row before modifying it.

You may wonder what happens if we update the same row that was modified under session-1? Will it lead to lost update for the changes done under session-1? Here is the new UPDATE statement in session-2

-- set isolation level to SNAPSHOT

set transaction isolation level snapshot

begin tran

     -- enable Snapshot Isolation and now update the row

     -- this will not block

     update basic_locking set c2 = 1000 where c1 = 1

In this case, the transaction under SI will get blocked while acquiring the X lock on the row as it has been concurrently modified by session-1. When the lock is ultimately acquired (i.e. the transaction in session-1 completes), a check is made if the same row was modified by a concurrent transaction that had committed and in that case, the concurrent change under the SI transaction is aborted by raising the following error. The update could have succeeded in session-2 if the update transaction in session-1 was aborted.

Msg 3960, Level 16, State 4, Line 4

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.basic_locking' directly or indirectly in database 'locktest' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Thanks

Sunil Agarwal