In a previous post i've shown what locks are held when doing "update if exists, insert if not" problem in 2 different ways.
Of course i had to see how MERGE statement (new in SQL Server 2008) behaves in this matter.
The MERGE statement basicaly merges data from a source resultset to a target table base on a condition you specify if the data
from the source already exists in the target or not.
Here's the code we'll use:
IF OBJECT_ID('t1') IS NOT NULL
DROP TABLE t1
GO
CREATE TABLE t1 (id INT PRIMARY KEY, name1 VARCHAR(10))
INSERT INTO t1
SELECT 1, 'name 1' UNION ALL
SELECT 2, 'name 2' UNION ALL
SELECT 3, 'name 3' UNION ALL
SELECT 4, 'name 4' UNION ALL
SELECT 5, 'name 5'
GO
---------------------------
-- I N S E R T R O W ---
---------------------------
BEGIN TRANSACTION
DECLARE @id INT = 6, @name1 VARCHAR(10) = 'name 6'
MERGE t1
USING (SELECT @id AS id, @name1 AS name1) AS t2 ON t1.id = t2.id
WHEN MATCHED
THEN UPDATE SET t1.name1 = t2.name1
WHEN NOT MATCHED
THEN INSERT VALUES(@id, @name1 );
SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
SELECT * FROM t1
ROLLBACK
go
---------------------------
-- U P D A T E R O W ---
---------------------------
BEGIN TRANSACTION
DECLARE @id INT = 5, @name1 VARCHAR(10) = 'name 6'
MERGE t1
USING (SELECT @id AS id, @name1 AS name1) AS t2 ON t1.id = t2.id
WHEN MATCHED
THEN UPDATE SET t1.name1 = t2.name1
WHEN NOT MATCHED
THEN INSERT VALUES(@id, @name1 );
SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
SELECT * FROM t1
ROLLBACK
We can see that the MERGE statement takes the same kinds of locks no matter what the operation is.
It takes two or three Intent Exclusive (IX) locks and one Exclusive (X) lock that changes the data.
The IX locks are held on the PAGE and database OBJECT (Table and/or HOBT*), while an X Lock is held on the Index KEY.
Compared to SQL Server 2005 we're better off by one Intent Shared (IS) Lock that was due to the select statemnt in the exists.
So there is improvement :)
* A HOBT is an allocation unit (Heap Or B-Tree, pronounced 'hobbit', yes, as in Lord Of The Rings). Found that here.
