I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server 2008: Locking with MERGE statement compared to Update/Insert in SQL Server 2005

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.

 

kick it on DotNetKicks.com
 

Print | posted on Friday, August 03, 2007 9:30 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server 2008: Locking with MERGE statement compared to Update/Insert in SQL Server 2005

Not only UPDATES and INSERTS. You can make DELETES too!

-- Prepare sample data
DECLARE @Base TABLE (ID INT, Data INT)

INSERT @Base
SELECT 1, 1 UNION ALL
SELECT 2, 1

DECLARE @New TABLE (ID INT, Data INT)

INSERT @New
SELECT 1, 0 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1

MERGE @Base AS b
USING (
SELECT ID,
SUM(Data)
FROM @New
GROUP BY ID
) AS src (ID, Data) ON src.ID = b.ID
WHEN MATCHED AND src.Data = 0
THEN DELETE;
WHEN MATCHED
THEN UPDATE SET b.data = src.Data;
WHEN NOT MATCHED THEN
INSERT (ID, Data) VALUES (src.ID, src.Data);
9/14/2007 10:36 AM | Peter Larsson
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET