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.