UPDLOCK HOLDLOCK.sql

SET STATISTICS TIME OFF
SET STATISTICS IO OFF
SET NOCOUNT ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.MyTable') AND type in (N'U'))
	DROP TABLE dbo.MyTable
GO


CREATE TABLE dbo.MyTable
(
	MyTableId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
	, Filler CHAR(4000) DEFAULT REPLICATE('A', 4000)
)
GO

INSERT dbo.MyTable DEFAULT VALUES
GO 10

DBCC TRACEON(-1, 1200, 3604)

BEGIN TRANSACTION

SELECT * FROM dbo.MyTable WITH (UPDLOCK) WHERE MyTableId = 1 

PRINT 'AFTER UPDLOCK'

SELECT * FROM dbo.MyTable WITH (UPDLOCK, HOLDLOCK) WHERE MyTableId = 10

PRINT 'AFTER UPDLOCK WITH HOLDLOCK'

COMMIT TRANSACTION

PRINT 'AFTER COMMIT'

DBCC TRACEOFF(-1, 1200, 3604)

UPDLOCK, HOLDLOCK

SET STATISTICS TIME OFF
SET STATISTICS IO OFF
SET NOCOUNT ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.MyTable') AND type in (N'U'))
	DROP TABLE dbo.MyTable
GO


CREATE TABLE dbo.MyTable
(
	MyTableId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
	, Filler CHAR(4000) DEFAULT REPLICATE('A', 4000)
)
GO

INSERT dbo.MyTable DEFAULT VALUES
GO 10

DBCC TRACEON(-1, 1200, 3604)

BEGIN TRANSACTION

SELECT * FROM dbo.MyTable WITH (UPDLOCK) WHERE MyTableId = 1 

PRINT 'AFTER UPDLOCK'

SELECT * FROM dbo.MyTable WITH (UPDLOCK, HOLDLOCK) WHERE MyTableId = 10

PRINT 'AFTER UPDLOCK WITH HOLDLOCK'

COMMIT TRANSACTION

PRINT 'AFTER COMMIT'

DBCC TRACEOFF(-1, 1200, 3604)