Showing posts with label blocking. Show all posts
Showing posts with label blocking. Show all posts

Wednesday, 25 April 2007

TSQL : Blocking & Locking

Tasks waiting right now -
SELECT * FROM Sys.dm_os_waiting_tasks

Tasks being blocked right now -
SELECT * FROM Sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL

Bit difficult to test when your system is running fine, but a query for clearly seeing the details of blocking issues -
SELECT PROCESS_BLOCKING.SPID                 'Holding ID',
RTRIM(PROCESS_BLOCKING.STATUS)        'Status',
'Lock Type' = CASE SYSLOCKINFO.RSC_TYPE
WHEN 1 THEN NULL
WHEN 2 THEN 'DATABASE'
WHEN 3 THEN 'FILE'
WHEN 4 THEN 'INDEX'
WHEN 5 THEN 'TABLE'
WHEN 6 THEN 'PAGE'
WHEN 7 THEN 'KEY'
WHEN 8 THEN 'EXTENT'
WHEN 9 THEN 'RID'
WHEN 10 THEN 'APPLICATION'
ELSE NULL
END,
SUSER_SNAME(PROCESS_BLOCKING.SID)     'Holding User',
SUSER_SNAME(PROCESS_WAITING.SID)     'Waiting User',
PROCESS_WAITING.SPID                 'Waiting ID',
'Database' = CASE
WHEN SYSLOCKINFO.RSC_DBID = 0 THEN '[NULL]'
ELSE DB_NAME(SYSLOCKINFO.RSC_DBID)
END,
SYSLOCKINFO.RSC_OBJID            'Object ID',
RTRIM(PROCESS_BLOCKING.HOSTNAME)      'Holding Host',
RTRIM(PROCESS_WAITING.HOSTNAME)      'Waiting Host',
RTRIM(PROCESS_BLOCKING.PROGRAM_NAME)  'Holding Program',
RTRIM(PROCESS_WAITING.PROGRAM_NAME)  'Waiting Program',
PROCESS_BLOCKING.CMD                  'Holding Command',
PROCESS_WAITING.CMD                  'Waiting Command',
PROCESS_BLOCKING.CPU                  'CPU Time',
PROCESS_BLOCKING.PHYSICAL_IO          'I/O',
PROCESS_BLOCKING.MEMUSAGE             'Mem Usage'
FROM   MASTER.DBO.SYSLOCKINFO
JOIN MASTER.DBO.SYSPROCESSES PROCESS_BLOCKING
ON SYSLOCKINFO.REQ_SPID = PROCESS_BLOCKING.SPID
JOIN MASTER.DBO.SYSPROCESSES PROCESS_WAITING
ON SYSLOCKINFO.REQ_SPID = PROCESS_WAITING.BLOCKED
AND PROCESS_BLOCKING.SPID = PROCESS_WAITING.BLOCKED
WHERE  PROCESS_BLOCKING.SPID <> @@SPID

Thursday, 27 April 2006

Purposely generating a record lock

Why? Is most probably what you're thinking.
I just use this technique for testing LOCKING check sql/reports.
Run this sql in 2 or more sessions -

-- open transaction
BEGIN TRANSACTION

-- run any select statement that brings back some data.
SELECT * FROM [DBO].[TABLENAME]

-- leave transaction open! Not commiting will cause a lock.
--COMMIT TRANSACTION