April 16, 2008 at 2:55 am
I wrote a stored proc that does an "upsert" in a table. The stored proc is called by several (more than 2) threads concurrently, often with identical parameters.
At run time there were sporadic deadlocks.
Tentatively I enclosed the whole block in a read committed transaction. It worked fine and that could be it. But I am not satisfied with a solution that I cannot explain. As I understand it, at most one call at could delay another but deadlock? It would be nice if somebody could explain it to me.
create procedure hp_addDicomTagValue(@DicomTagId int,
@StudyInstanceUID varchar(64),
@SeriesInstanceUID varchar(64),
@SOPInstanceKey bigint,
@Value nvarchar(1024))
as
begin
-- update value that already exists
-- (because the 2nd and 3rd parameters may be null the where-clause has to take care of null values)
update DicomTagValue set [Value] = @Value, [TimeStamp] = GETDATE()
where
DicomTagId = @DicomTagId and
((StudyInstanceUID = @StudyInstanceUID and @SeriesInstanceUID is null and SOPInstanceKey is null) or
(StudyInstanceUID = @StudyInstanceUID and SeriesInstanceUID = @SeriesInstanceUID and SOPInstanceKey is null) or
(StudyInstanceUID = @StudyInstanceUID and SeriesInstanceUID = @SeriesInstanceUID and SOPInstanceKey = @SOPInstanceKey ))
-- else insert a new value
if(@@ROWCOUNT = 0)
begin
insert into DicomTagValue (DicomTagId, StudyInstanceUID, SeriesInstanceUID, SOPInstanceKey, [Value], [TimeStamp])
values
(@DicomTagId, @StudyInstanceUID, @SeriesInstanceUID, @SOPInstanceKey, @Value, GETDATE())
end
end
go
Additional info:
- there is a index on the 3 columns of the where-clause
- I already rewrote it with ANSI_NULLS OFF to simplify the where-clause, but I don't know if that affects the deadlock problem
April 16, 2008 at 3:14 am
Are there any triggers on the table?
Switch traceflag 1204 on (DBCC TRACEON (1204,-1)) and the deadlock graph will be written into the error log. That'll give you a lot more info on xactly what is deadlocking and on what resource.
Post it here if you need a hand interpreting it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2008 at 4:29 am
As Gail suggested, turn on the trace flag to get a better idea of what's happening.
Looking at the code though I've got a couple of ideas that would suggest why this might be happening.
If there's an index on the field you're upating then you're probably looking at a conversion deadlock. But given that it seems to work fine when you enclose the block in a transaction I'd say it's this that gives a clue to the answer.
You're not just doing an update but also an insert statement. Imagine that process 1 comes in and takes an update lock on the table. The update statement finishes (no rows were updated) so the locks are release (because you don't have a transaction surrounding the block).
So whilst process A is evaluating the @@ROWCOUNT statement, process B comes along and places update locks in order to evaluate the WHERE clause (probably key locks because you have indexes on the columns in the where clause). No problem. Normally after this happens the update locks are upgraded to exclusive locks. But before that can happen process A comes along and places an Intent Exclusive lock on the clustered index because it plans to do an insert. The problem is that process A now cannot upgrade to an exclusive lock and process B cannot either - so you end up with a deadlock.
By putting the whole thing in a transaction you ensure that no other process can begin the update statement until the first process finishes with everything.
Hope that helps.
April 16, 2008 at 5:18 am
Karl, thank you very much for the explanation, it sounds plausible and I think this is it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply