Skip to content

Instantly share code, notes, and snippets.

@johngibb

johngibb/UPDLOCK HOLDLOCK.sql

Created December 21, 2011 16:24
Show Gist options
  • Save johngibb/1506635 to your computer and use it in GitHub Desktop.
Save johngibb/1506635 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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)
Raw
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment