SQL Server – Viewing Lock Types in T-SQL

lockingsql server

I am looking for a way to determine whether SQL will take a shared lock or an update lock.

Take these three queries. I believe in that in theory:

  • Query one will take a shared lock on the pk, due to using SELECT TOP 1
  • Query two will use an update lock due to using MIN
  • Query three… not sure whether the use of group by and order by will make it do a shared lock?

However how do I prove this theory? Also if anyone can tell me how to format sql in these posts that would be great 🙂

Query one:

/* Uses Shared Lock? */

CREATE TABLE #TABLE1
(ID int PRIMARY KEY,
Name nvarchar(20),
DateModified datetime)

INSERT INTO #TABLE1 
VALUES (1,'Dave', '2014-01-01')
INSERT INTO #TABLE1 
VALUES (2,'Tim', '2013-01-01')

UPDATE #TABLE1
SET Name = 'Jeff'
WHERE ID = (SELECT TOP 1 ID FROM #TABLE1 ORDER BY DateModified ASC)

Query two:

/* Uses Update Lock? */

CREATE TABLE #TABLE2 
(ID int PRIMARY KEY,
Name nvarchar(20),
DateModified datetime)

INSERT INTO #TABLE2
VALUES (1,'Dave', '2014-01-01')
INSERT INTO #TABLE2
VALUES (2,'Tim', '2013-01-01')

UPDATE #TABLE2
SET Name = 'Jeff'
WHERE ID = (SELECT MIN(ID) FROM #TABLE2)

Query three:

CREATE TABLE #TABLE3 
(ID int PRIMARY KEY,
Name nvarchar(20),
DateModified datetime)

CREATE TABLE #StagingTable 
(ID int, 
DateModified datetime)

INSERT INTO #TABLE3
VALUES (1,'Dave', '2014-01-01')
INSERT INTO #TABLE3
VALUES (2,'Tim', '2013-01-01')

INSERT INTO #StagingTable

SELECT MIN(ID), DateModified FROM #Table3 GROUP BY DateModified ORDER BY DateModified

UPDATE #TABLE3
SET Name = 'Jeff'
WHERE ID = (SELECT MAX(ID) FROM #StagingTable)

You may be wondering what the idea with query three is. However if you take a look at the data the date is not in line with the ID column. So the oldest date may not be the lowest ID number, which means the MIN logic in query two is invalid.

Thanks

Best Answer

I you try to find out which lock is currently been taking. Just take a short look in the tran_locks. There you find all current locks. 

SELECT *
FROM sys.dm_tran_locks 
WHERE resource_database_id = DB_ID() and request_session_id = [YOURSESSION]

Hope this helps you.

Another way could be the usage of WITH(HOLDLOCK) on your statement and append this code to the end of your transaction.

SELECT *
FROM sys.dm_tran_locks 
WHERE resource_database_id = DB_ID() and request_session_id = @@SPID
Related Question