This blog will no longer be updated.

New content is available on my new blog

updlock, holdlock and deadlocks - Piotr Rodak

updlock, holdlock and deadlocks

We have a process that manages sliding window over a number of tables in our reporting database. This process is triggered by first call to our reporting ETL. There is table that contains two dates that are important for the partitioning and business requirements. The table is build as the following example:

if object_id('dbo.tDateTest') is not null

drop table dbo.tDateTest

go

create table dbo.tDateTest

(

FromDate datetime not null,

ToDate datetime not null

)


The table contains only one row:

insert dbo.tDateTest(FromDate, ToDate) values('20090102', '20090101')


 
It is important that the code is called only once and other processes do not overlap, as this would lead to incorrect partition range and missing data in the interface views.

Just a few days we had a production issue, and then another one, next day - deadlocks. That surprised me to say the least, because I thought I had secured myself against this problem. The code looked like this:

declare @newFrom datetime, @newTo datetime, @oldFrom datetime

set @newFrom = '20090115'

set @newTo = '20090114'


begin tran

--######### original approach

select @oldFrom = FromDate from dbo.tDateTest with(updlock, holdlock)


while @oldFrom < @newFrom

begin

set @oldFrom = dateadd(day, 1, @oldFrom)

update dbo.tDateTest set FromDate = @newFrom, ToDate = @newTo

--some other processing here..

end

commit

select * from dbo.tDateTest


Query 1, Original approach.

As you see I added the updlock and holdlock hints to the select statement. This technique effectively changes isolation level of the transaction to serializable, by acquiring update lock at the time select statement is executed and holding it to the end of the transaction.
Well, it turned out that when multiple processes execute the above code, there may be a situation when a deadlock occurs.
At the beginning, I had trouble to actually reproduce the behavior. Fiddling with waitfor statement and multiple windows proved to be rather cumbersome. Then I remembered that Adam Machanic's little utility SQLQueryStress can do exactly what I want - call some code many times from different threads. And indeed, when I set up the utility and pasted above code (picture below), I encountered deadlocks exactly as in the production.



I turned on trace flag 1222 which writes detailed deadlock information to the error log to see what actually is going on.
dbcc traceon(1222, -1)
The -1 parameter indicates that you want to set the status flag globally for all connections to the server. If it is not provided, the flag applies only to the current connection.

After I had enabled the flag, I pasted query 1 to the SQLQueryStress util. I configured the tool to run the code 40 times in two threads. During the run errors were indicated and when I clicked the ellipsis (...) button I could verify the errors were indeed deadlocks. So I opened the SQL Server error log and got the following output from it:

deadlock-list
deadlock victim=process1cdf68c58
process-list
process id=process79a72e8 taskpriority=0 logused=0 waitresource=OBJECT: 15:1622453004:0  waittime=1812 ownerId=4097956687 transactionname=user_transaction lasttranstarted=2009-12-21T13:56:52.703 XDES=0x8031d0c0 lockMode=X schedulerid=1 kpid=1856 status=suspended spid=117 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-12-21T13:56:52.703 lastbatchcompleted=2009-12-21T13:56:52.563 clientapp=.Net SqlClient Data Provider hostname=MYHOST hostpid=16244 loginname=testlogin isolationlevel=read committed (2) xactid=4097956687 currentdb=15 lockTimeout=4294967295 clientoption1=671096864 clientoption2=128056
executionStack
frame procname=adhoc line=7 stmtstart=336 stmtend=480 sqlhandle=0x020000000144d3314c9019b950c94aa9630febde7d049222
select @oldFrom = FromDate from dbo.tDateTest with(updlock<c/> holdlock)
inputbuf
declare @newFrom datetime<c/> @newTo datetime<c/> @oldFrom datetime
set @newFrom = '20090115'
set @newTo = '20090114'
begin tran
--#########        original approach
select @oldFrom = FromDate from dbo.tDateTest with(updlock<c/> holdlock)
while @oldFrom < @newFrom
begin
set @oldFrom = dateadd(day<c/> 1<c/> @oldFrom)
update dbo.tDateTest set FromDate = @newFrom<c/> ToDate = @newTo
#NAME?
end
commit
select * from dbo.tDateTest
process id=process1cdf68c58 taskpriority=0 logused=0 waitresource=OBJECT: 15:1622453004:0  waittime=1828 ownerId=4097956685 transactionname=user_transaction lasttranstarted=2009-12-21T13:56:52.703 XDES=0x35527f6d0 lockMode=X schedulerid=3 kpid=3324 status=suspended spid=118 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-12-21T13:56:52.703 lastbatchcompleted=2009-12-21T13:56:52.533 clientapp=.Net SqlClient Data Provider hostname=MYHOST hostpid=16244 loginname=testlogin isolationlevel=read committed (2) xactid=4097956685 currentdb=15 lockTimeout=4294967295 clientoption1=671096864 clientoption2=128056
executionStack
frame procname=adhoc line=7 stmtstart=336 stmtend=480 sqlhandle=0x020000000144d3314c9019b950c94aa9630febde7d049222
select @oldFrom = FromDate from dbo.tDateTest with(updlock<c/> holdlock)
inputbuf
declare @newFrom datetime<c/> @newTo datetime<c/> @oldFrom datetime
set @newFrom = '20090115'
set @newTo = '20090114'
begin tran
--#########        original approach
select @oldFrom = FromDate from dbo.tDateTest with(updlock<c/> holdlock)
while @oldFrom < @newFrom
begin
set @oldFrom = dateadd(day<c/> 1<c/> @oldFrom)
update dbo.tDateTest set FromDate = @newFrom<c/> ToDate = @newTo
#NAME?
end
commit
select * from dbo.tDateTest
resource-list
objectlock lockPartition=0 objid=1622453004 subresource=FULL dbid=15 objectname=Database_Perf.dbo.tDateTest id=lockea708480 mode=IX associatedObjectId=1622453004
owner-list
owner id=process79a72e8 mode=IX
owner id=process1cdf68c58 mode=IX
waiter-list
waiter id=process1cdf68c58 mode=X requestType=convert
waiter id=process79a72e8 mode=X requestType=convert



There a couple of facts to be noticed here. As you see in the second line od the output, process process1cdf68c58 has been chosen as victim of the deadlock. The lines beginning with >process id< contain also information about what object was deadlocked. This is the waitresource part: =OBJECT: 15:1622453004:0. The identifier tells you that the deadlocked resource is an object (table), in database with db_id() 15, then there is id of the object within this database and eventually id of the index on the table. The index id 0 indicates that this is heap - either there there is no clustered index defined or it was not chosen to run the query.
By the end of the output in the resource-list section you can also see the name of the table and the types of locks that were in place before the deadlock occured. As you see, both processes (owner id lines) had intent exclusive (IX) lock on the table and both of them decided to convert the lockx to exclusive (X)) and since these locks (IX and X) are not compatible, deadlock occured and one of the processes was killed. There is a MSDN article here with overview of the locks compatibility.

To say the least, I was pretty surprised. I never thought this would occur, although when I think of this, the deadlocking is pretty reasonable. I just made (uneducated) assumption, that when there is only one row in a table, the enginge will get straight to this row and (updlock, holdlock) will work as planned. I was wrong as it turns out.

Anyway, I needed to fix the problem.

1. The first idea was to replace the updlock with tablockx and this resolved the deadlocking issue.
Note that the TABLOCKX hint causes the engine to take an exclusive lock on the whole table. Use it with caution, as if you have really concurrent environment, this hint can affect performance seriously. In my case, this is not an issue, so I am OK with this approach. Here is the query that I used to resolve the problem:

declare @newFrom datetime, @newTo datetime, @oldFrom datetime

set @newFrom = '20090115'

set @newTo = '20090114'


begin tran

--############ tablockx approach

select @oldFrom = FromDate from dbo.tDateTest with(tablockx, holdlock)


while @oldFrom < @newFrom

begin

set @oldFrom = dateadd(day, 1, @oldFrom)

update dbo.tDateTest set FromDate = @newFrom, ToDate = @newTo

--some other processing here..

end

commit

select * from dbo.tDateTest



This would indicate that updlock has different path of execution than tablockx and there is transition state from intent lock to exclusive lock that is prone to deadlock issues if exclusive table lock is not explicitly requested. Please note that while I accepted this approach as the solution, it may not be the best idea if you have heavily accessed table with many rows.


Since I was at it, I decided to try several various approaches and see if they work. Here are some approaches I tried:
2. I thought that maybe deadlocking is caused by query without where clause narrowing the set to one row (yeah, there is one row there anyway, but maybe optimizer chooses different optimization technique?). I changed the select statement to the following:

select @oldFrom = FromDate from dbo.tDateTest with(updlock, holdlock)

where FromDate = '20090102'



Obviously, because there is no index on the table, the select statement produces table scan in execution plan, so there is not a big change to the plan produced by query without where clause. The deadlocks are still there, too.

3. Next idea I had was to create a nonclustered index on the table and use it to select rows from it.

--add nonclustered index

if object_id('dbo.tDateTest') is not null

drop table dbo.tDateTest

go

create table dbo.tDateTest

(

FromDate datetime not null,

ToDate datetime not null

)

go

create index ix1 on dbo.tDateTest(FromDate)

go

insert dbo.tDateTest(FromDate, ToDate) values('20090102', '20090101')

go



Now, this query

select @oldFrom = FromDate from dbo.tDateTest with(updlock, holdlock)



uses index scan because optimizer chooses to use index instead of table to retrieve all required data. What is interesting - there are no deadlocks here. So it looks like index scan is somewhat safer in this case than table scan, in terms of deadlocking.

4. I added new variable to the script and changed the query so it has to use in some way a table.
The query looks now like this:

select @oldFrom = FromDate, @oldTo = ToDate from dbo.tDateTest with(updlock, holdlock)


Because there is no where clause, the optimizer chose to use table scan and bypass the index altogether. This takes us to the square one and the deadlocking issue.

5. I wondered what would happen if I enforced use of the index in the query:

select @oldFrom = FromDate, @oldTo = ToDate from dbo.tDateTest with(updlock, holdlock, index=ix1)



This query produces plan that contains index scan and RID lookup on the table. And there are no deadlocks interestingly.

6. So, next approach is to use index and search for the only row in the table using WHERE clause.

select @oldFrom = FromDate, @oldTo = ToDate from dbo.tDateTest with(updlock, holdlock, index=ix1) where FromDate = '20090102'



Since the optimizer goes directly to the searched row, the locking seems to be less prone to deadlocking.

7. The last change I checked was to modify the table and add surrogate clustered primary key:

--change definition of the table - add clustered key

if object_id('dbo.tDateTest') is not null

drop table dbo.tDateTest

go

create table dbo.tDateTest

(

PK int identity(1, 1) primary key clustered,

FromDate datetime not null,

ToDate datetime not null

)

go

insert dbo.tDateTest(FromDate, ToDate) values('20090102', '20090101')


go



Now, the query was modified to seek for the row with PK = 1,

select @oldFrom = FromDate from dbo.tDateTest with(updlock, holdlock)

where PK = 1



Since this is a Clustered Index seek in execution plan, there is no deadlocking as well.


In summary, this is another example that it is better to have a clustered index than not, even on small single-row tables. It is surprising that table-scan is more susceptible to deadlocks than index scan as idea number 5 showed.
I suppose these findings may be inaccurate if there are more rows in the table or in the index. I haven't tested such scenario yet, but definitely SQLStressQuery utility is coming to my toolbelt.

It is worth to mention, that there is another way of serializing execution of your code. There is a, somewhat not well known procedure sp_getapplock which, together with sp_releaseapplock allows for creating critical section around your code withouth applying locks to the table. I checked this approach as well and obviously it prevents deadlocks from occur. This is pretty interesting option in certain situations in my opinion.


The code I used for testing is available here: testing updlock, holdlock hint.zip, and the SQLStressQuery utility is here, please have your go if you find it interesting and have some time to spend.
Published 04 January 2010 22:10 by Piotr Rodak

Comments

# http://stackoverflow.com/questions/2275946/t-sql-insert-or-update/2277595

18 February 2010 14:37 by TrackBack