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
@tValues key_int_pairs READONLY
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)
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