Basic MERGE upsert causing deadlocks - by Michael Kujawa

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


8
1
Sign in
to vote
ID 723696 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 2/6/2012 10:27:28 AM
Access Restriction Public

Description

I'm trying to improve on our implementation of the upsert pattern by switch from operating one row at a time to using MERGE.

However, when stressing the system by having four threads execute an identical workload, I occasionally have transactions terminated due to deadlocks.  This seems like a pretty ideal use of MERGE, so I'm not sure why there would be problems.  

The table has a two-part key and one value column.

CREATE TABLE key_to_int_table
(
  iid bigint not null,
  dotted_key char(240) not null,
  value bigint not null
)
ALTER TABLE key_to_int_table ADD CONSTRAINT iid_and_key_pk PRIMARY KEY CLUSTERED (iid, dotted_key)

The sproc to upsert into this table is as follows:
CREATE PROCEDURE proc_update_values3
(
  @iid bigint,
  @tValues key_int_pairs READONLY
)
AS
  MERGE key_to_int_table as target
    USING (select * from @tValues) as source (dotted_key, value)
      on (target.iid = @iid and target.dotted_key = source.dotted_key)
    WHEN MATCHED THEN
      UPDATE set value=source.value
    WHEN NOT MATCHED THEN
      INSERT (iid, dotted_key, value) 
        VALUES (@iid, source.dotted_key, source.value)
  ;

key_int_pairs is:
CREATE TYPE key_int_pairs AS TABLE (
    dk char(240) NOT NULL,
    v bigint NOT NULL
);


I have also tried a version of proc_update_values which took @tValues as xml instead, and then fed MERGE from OPENXML.  I also tried key_int_pairs with dk as a PRIMARY KEY, but this seemed to make the deadlocks more frequent instead of less!

I'm currently using a Serializable isolation level, though changing this didn't seem to affect the results at all.

SQL Server version 10.0.5770.0
Sign in to post a comment.
Posted by Microsoft on 4/26/2013 at 11:21 AM
Hello,

Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. The reasons for closing this bug is because the scenario reported in the bug are not common enough and due to the risk of implementing a fix it unfortunately does not meet the bar for the current version of the product.

If you have more supporting information and details or feel strongly that we should reconsider, please do resubmit this item for review.

Thanks again for reporting the product issue and continued support in improving our product.
Gus Apostol, SQL Server Program Manager
Posted by Michael Kujawa on 2/9/2012 at 3:52 PM
(I'm happy to know that a table works as source and not just a rowset, it looks so much better that way.)
Posted by Michael Kujawa on 2/9/2012 at 3:51 PM
Thanks for the suggestions! I tried the proposed sproc changes, but the plan didn't change and it still deadlocks in the same way.
Posted by Vladimir Moldovanenko on 2/8/2012 at 7:54 PM
oh, the rest with schema qualifications
--key_int_pairs is:
CREATE TYPE dbo.key_int_pairs AS TABLE (
    dk char(240) NOT NULL,
    v bigint NOT NULL
);
GO

CREATE TABLE dbo.key_to_int_table
(
iid bigint not null,
dotted_key char(240) not null,
value bigint not null
)
ALTER TABLE key_to_int_table ADD CONSTRAINT iid_and_key_pk PRIMARY KEY CLUSTERED (iid, dotted_key)
GO
-- The sproc to upsert into this table is as follows:
Posted by Vladimir Moldovanenko on 2/8/2012 at 7:53 PM
And please use schema when defining/referncing objects.
http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/11/bad-habits-to-kick-avoiding-the-schema-prefix.aspx
http://msdn.microsoft.com/en-us/library/ms181055.aspx
see in particular The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched.

How about this code below?
try it, see if it helps your cause. formatting helps; merge can reference table type directly

CREATE PROCEDURE dbo.proc_update_values3
(
@iid bigint,
@tValues key_int_pairs READONLY
)
AS


WITH d AS
(
    SELECT *
    FROM dbo.key_to_int_table
    WHERE iid = @iid
)
MERGE d
USING @tValues s
ON d.dotted_key = s.dk
WHEN MATCHED THEN UPDATE
SET value = s.v
WHEN NOT MATCHED THEN
INSERT
    (iid
    ,dotted_key
    ,value)
VALUES
    (@iid
    ,s.dk
    ,s.v)
;
GO

Posted by Michael Kujawa on 2/8/2012 at 8:32 AM
I was showing this to someone at the office, and the four threads started up and three of them immediately deadlocked on each other. This means that when they started running, none of the data was in the table yet and that all four threads were trying to merge the exact same data (in the exact same order.)

I neglected to mention that the values in @tValues were added in the same order as the clustered index ordering, trying to prevent a deadlock caused by different queries operating on different ranges of the table/index in different orders.
Posted by Michael Kujawa on 2/7/2012 at 10:46 AM
It's blocking me from switching to MERGE, but my existing stored procedures (originally authored for SQL 2000, hehe) still work.
Posted by Microsoft on 2/7/2012 at 9:58 AM
Dear Michael,

Thanks for the feedback. If you have a production-related issue that is blocking you, please contact Microsoft Customer Support Services.

Best regards,
Eric Hanson
Program Manager
SQL Server Query Processing