|
相关存储过程：
1、一个查看锁定信息的SP：
?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- blocking and waiting processes
-- Blocked-proces.sql 5.1,? Joachim Verhagen 28-2-2003,19-3-2003 (Just some comment changes)
-- Recipe:
-- 1 Start isqlw
-- 2 Connect to server
-- 3 Select in the upper window (with the yellow cylinder) the database with the blocking problems
-- 4 Go to ASCII output with control-t
-- 4 Run the script by clicking the green arrow
ALTER? procedure mon_lock as
set nocount on
DECLARE @CursorVar CURSOR
DECLARE @waiter integer
DECLARE @blocker integer
SET @CursorVar = CURSOR FORWARD_ONLY STATIC
FOR
select spid,blocked
from master..sysprocesses
where blocked>0
OPEN @CursorVar
FETCH NEXT FROM @CursorVar into @waiter,@blocker
WHILE @@FETCH_STATUS = 0
BEGIN
set nocount on
??????? select '============================================================================================' as 'NEXT BLOCKER AND WAITER SET'
?select @blocker,' blocks ',@waiter
?select 'blocker',@blocker
-- statement blocker?
?DBCC INPUTBUFFER(@blocker)
-- who and what is blocking
?select
??p.spid,
??cast(p.status as varchar(10)) as 'status',
??p.waittime,
??cast(p.loginame as varchar(30)) as 'loginname',
??cast(p.hostname as varchar(20)) as 'hostname',
??cast(p.program_name as varchar(25)) as 'ProgramName',
??p.cmd,
??WaitStatus=case
???when waittype=0 then 'Previously Waiting for: '
???else? 'Now waiting for:'
???????????? end,
??????????????? TypeWait=case Lastwaittype
????when 'LCK_M_SCH_S' then 'Schema stability'
????when 'LCK_M_SCH_M' then 'Schema modification'
????when 'LCK_M_IS' then 'Intent-Share'
????when 'LCK_M_SIU' then 'Shared intent to update'
????when 'LCK_M_IS_S' then 'Intent-Share-Share (Key-Range Lock)'
????when 'LCK_M_IX' then 'Intent-Exclusive'
????when 'LCK_M_SIX' then 'Share-Intent-Exclusive'
????when 'LCK_M_S' then 'Share'
????when 'LCK_M_U' then 'Update'
????when 'LCK_M_II_NL' then 'Intent-Insert-NULL (Key-Range Lock)'
????when 'LCK_M_II_X' then 'Intent-Insert-Exclusive (Key-Range Lock)'
????when 'LCK_M_IU' then 'Intent-Update lock'
????when 'LCK_M_IS_U' then 'Intent-Share Update (Key-Range Lock)'
????when 'LCK_M_X' then 'Exclusive'
????when 'LCK_M_BU' then 'Bulk Update'
????else LastWaittype
???end,?
??p.waitresource
??????? from master..sysprocesses p
?where p.spid=@blocker
--locks?blocker
?select?distinct
??????? cast(o.name as varchar(20)) as 'object name',
??????? cast(i.name as varchar(20)) as 'index name',
??????? Object_type=
??case
???when rsc_type=1 then 'NULL Resource'
???when rsc_type=2 then 'Database'
???when rsc_type=3 then 'File'
???when rsc_type=4 then 'Index'
???when rsc_type=5 then 'Table'
???when rsc_type=6 then 'Page'
???when rsc_type=7 then 'Key'
???when rsc_type=8 then 'Extent'
???when rsc_type=9 then 'RID (Row id)'
???when rsc_type=10 then 'Application'
??????????????????????? else? 'Unknown'
??????? end,
??????? cast(rsc_text as varchar(15)) as 'lockresource',
??????? Lock_mode=
??case
???when req_mode=0 then 'No Access'
???when req_mode=1 then 'Sch-S (Schema Stability)'
???when req_mode=2 then 'Sch-M (Schema modification)'
???when req_mode=3 then 'S (Shared)'
???when req_mode=4 then 'U (Update)'
???when req_mode=5 then 'X (Exclusive)'
???when req_mode=6 then 'IS (Intent Shared)'
???when req_mode=7 then 'IU (Intent Update)'
???when req_mode=8 then 'IX (Intent Exclusive)'
???when req_mode=9 then 'SIU (Shared Intent Update)'
???when req_mode=10 then 'SIX (Shared Intent Exclusive)'
???when req_mode=11 then 'UIX (Update Intent Exclusive)'
???when req_mode=12 then 'BU (Bulk)'
???when req_mode=13 then 'RangeS_S'
???when req_mode=14 then 'RangeS_U'
???when req_mode=15 then 'RangeI_N'
???when req_mode=16 then 'RangeI_S'
???when req_mode=17 then 'RangeI_U'
???when req_mode=18 then 'RangeI_X'
???when req_mode=19 then 'RangeX_S'
???when req_mode=20 then 'RangeX_U'
???when req_mode=21 then 'RangeX_X'
???else 'Unknown'
??end,
?lock_status=
??case
???when l.req_status=1 then 'Granted'
???when l.req_status=2 then 'Converting'
???when l.req_status=3 then 'Waiting'
???else 'Unknown'
??end,
?l.req_refcnt as 'lock count',?????
??????? cast(d.name as varchar(20)) as 'database'
??????? from master..syslockinfo l,sysobjects o,sysindexes i,master..sysdatabases d
?where l.req_spid=@blocker
????????? and l.rsc_objid=i.id
????????? and l.rsc_indid=i.indid
?? and l.rsc_objid=o.id
????????? and o.id=i.id
?? and l.rsc_dbid=d.dbid
??? ?? and l.rsc_dbid=d.dbid
??????? union
?select?distinct
??????? cast(o.name as varchar(20)) as 'object name',
?cast(o.name as varchar(20)),
??????? Object_type=
??case
???when rsc_type=1 then 'NULL Resource'
???when rsc_type=2 then 'Database'
???when rsc_type=3 then 'File'
???when rsc_type=4 then 'Index'
???when rsc_type=5 then 'Table'
???when rsc_type=6 then 'Page'
???when rsc_type=7 then 'Key'
???when rsc_type=8 then 'Extent'
???when rsc_type=9 then 'RID (Row id)'
???when rsc_type=10 then 'Application'
??????????????????????? else? 'Unknown'
???????????????? end,????????
??????? Lock_mode=
??case
???when req_mode=0 then 'No Access'
???when req_mode=1 then 'Sch-S (Schema Stability)'
???when req_mode=2 then 'Sch-M (Schema modification)'
???when req_mode=3 then 'S (Shared)'
???when req_mode=4 then 'U (Update)'
???when req_mode=5 then 'X (Exclusive)'
???when req_mode=6 then 'IS (Intent Shared)'
???when req_mode=7 then 'IU (Intent Update)'
???when req_mode=8 then 'IX (Intent Exclusive)'
???when req_mode=9 then 'SIU (Shared Intent Update)'
???when req_mode=10 then 'SIX (Shared Intent Exclusive)'
???when req_mode=11 then 'UIX (Update Intent Exclusive)'
???when req_mode=12 then 'BU (Bulk)'
???when req_mode=13 then 'RangeS_S'
???when req_mode=14 then 'RangeS_U'
???when req_mode=15 then 'RangeI_N'
???when req_mode=16 then 'RangeI_S'
???when req_mode=17 then 'RangeI_U'
???when req_mode=18 then 'RangeI_X'
???when req_mode=19 then 'RangeX_S'
???when req_mode=20 then 'RangeX_U'
???when req_mode=21 then 'RangeX_X'
???else 'Unknown'
??end,?????
??????? cast(d.name as varchar(20)) as 'database'?????
?from master..syslockinfo l,master..sysdatabases d
?where l.req_spid=@blocker
????????? and l.rsc_indid=0
?? and l.rsc_objid=o.id
?? and l.rsc_dbid=d.dbid
?union
?select?distinct
??????? '-',
?'-',
??????? cast(d.name as varchar(20)) as 'database'
??from master..syslockinfo l,master..sysdatabases d
?where l.req_spid=@blocker
????????? and l.rsc_indid=0
?? and l.rsc_objid=0
?? and l.rsc_dbid=d.dbid
?
?select 'waiter',@waiter
-- statement waiter
?DBCC INPUTBUFFER (@waiter)
-- who and what is waiting
?select
??p.spid,?
??p.waitresource
??????? from master..sysprocesses p
?where p.spid=@waiter
-- locks waiter
?select?distinct
??????? cast(o.name as varchar(20)) as 'object name',?????
??????? cast(d.name as varchar(20)) as 'database'
?from master..syslockinfo l,master..sysdatabases d
?where l.req_spid=@waiter
????????? and l.rsc_objid=i.id
????????? and l.rsc_indid=i.indid
?? and l.rsc_objid=o.id
????????? and o.id=i.id
?? and l.rsc_dbid=d.dbid
??? ?? and l.rsc_dbid=d.dbid
??????? union
?select?distinct
??????? cast(o.name as varchar(20)) as 'object name',
?l.req_refcnt as 'lock count'?????,master..sysdatabases d
?where l.req_spid=@waiter
????????? and l.rsc_indid=0
?? and l.rsc_objid=o.id
?? and l.rsc_dbid=d.dbid
?union
?select?distinct
??????? '-',master..sysdatabases d
?where l.req_spid=@waiter
????????? and l.rsc_indid=0
?? and l.rsc_objid=0
?? and l.rsc_dbid=d.dbid
?
??? FETCH NEXT FROM @CursorVar? into @waiter,@blocker
END
CLOSE @CursorVar
DEALLOCATE @CursorVar
--? Explanation Proces Status
-- Background =? SPID is performing a background task.
-- Sleeping = SPID is not currently executing. This usually indicates that the SPID is awaiting a command from the application.
-- Runnable = SPID is currently executing.
-- Dormant = Same as Sleeping,except Dormant also indicates that the SPID has been reset after completing an RPC event. The reset cleans up resources used during the RPC event. This is a normal state and the SPID is available and waiting to execute further commands.?
-- Rollback = The SPID is in rollback of a transaction.
-- Defwakeup = Indicates that a SPID is waiting on a resource that is in the process of being freed. The waitresource field should indicate the resource in question.
-- Spinloop = Process is waiting while attempting to acquire a spinlock used for concurrency control on SMP systems.
--? Explanation lock mode
-- 0 = NULL. No access is granted to the resource. Serves as a placeholder.
-- 1 = Sch-S (Schema stability). Ensures that a schema element,such as a table or index,is not dropped while any session holds a schema stability lock on the schema element.
-- 2 = Sch-M (Schema modification). Must be held by any session that wants to change the schema of the specified resource. Ensures that no other sessions are referencing the indicated object.
-- 3 = S (Shared). The holding session is granted shared access to the resource.
-- 4 = U (Update). Indicates an update lock acquired on resources that may eventually be updated. It is used to prevent a common form of deadlock that occurs when multiple sessions lock resources for potential update at a later time.
-- 5 = X (Exclusive). The holding session is granted exclusive access to the resource.
-- 6 = IS (Intent Shared). Indicates the intention to place S locks on some subordinate resource in the lock hierarchy.
-- 7 = IU (Intent Update). Indicates the intention to place U locks on some subordinate resource in the lock hierarchy.
-- 8 = IX (Intent Exclusive). Indicates the intention to place X locks on some subordinate resource in the lock hierarchy.
-- 9 = SIU (Shared Intent Update). Indicates shared access to a resource with the intent of acquiring update locks on subordinate resources in the lock hierarchy.
-- 10 = SIX (Shared Intent Exclusive). Indicates shared access to a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
-- 11 = UIX (Update Intent Exclusive). Indicates an update lock hold on a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
-- 12 = BU. Used by bulk operations.
-- 13 = RangeS_S (Shared Key-Range and Shared Resource lock). Indicates serializable range scan.
-- 14 = RangeS_U (Shared Key-Range and Update Resource lock). Indicates serializable update scan.
-- 15 = RangeI_N (Insert Key-Range and Null Resource lock). Used to test ranges before inserting a new key into an index.
-- 16 = RangeI_S. Key-Range Conversion lock,created by an overlap of RangeI_N and S locks.
-- 17 = RangeI_U. Key-Range Conversion lock,created by an overlap of RangeI_N and U locks.
-- 18 = RangeI_X. Key-Range Conversion lock,created by an overlap of RangeI_N and X locks.
-- 19 = RangeX_S. Key-Range Conversion lock,created by an overlap of RangeI_N and RangeS_S. locks.
-- 20 = RangeX_U. Key-Range Conversion lock,created by an overlap of RangeI_N and RangeS_U locks.
-- 21 = RangeX_X (Exclusive Key-Range and Exclusive Resource lock). This is a conversion lock used when updating a key in a range.
-- Last waittype
--? Last waittype (text) and waittype (hex)
--??? Last Waittype Locks
-- LCK_M_SCH_S 0x01 Schema stability
-- LCK_M_SCH_M 0x02 Schema modification
-- LCK_M_IS 0x03 Intent-Share
-- LCK_M_SIU 0x04 Shared intent to update
-- LCK_M_IS_S 0x05 Intent-Share-Share (Key-Range Lock)
-- LCK_M_IX 0x06 Intent-Exclusive
-- LCK_M_SIX 0x07 Share-Intent-Exclusive
-- LCK_M_S 0x08 Share
-- LCK_M_U 0x09 Update
-- LCK_M_II_NL 0x0A Intent-Insert-NULL (Key-Range Lock)
-- LCK_M_II_X 0x0B Intent-Insert-Exclusive (Key-Range Lock)
-- LCK_M_IU 0x0C Intent-Update lock
-- LCK_M_IS_U 0x0D Intent-Share Update (Key-Range Lock)
-- LCK_M_X 0x0E Exclusive
-- LCK_M_BU 0x0F Bulk Update
--? Last Waittype Latches
-- PWait_LATCH_NL 0x400
-- PWait_LATCH_EX 0x401
-- PWait_LATCH_SH 0x402
-- PWait_LATCH_UP 0x403
-- PWait_PAGELATCH_NL 0x410
-- WAIT_PAGELATCH_EX 0x411
-- WAIT_PAGELATCH_SH 0x412
-- PWait_PAGELATCH_UP 0x413
-- PWait_PAGEIOLATCH_NL 0x420
-- PWait_PAGEIOLATCH_EX 0x421
-- PWait_PAGEIOLATCH_SH 0x422
-- PWait_PAGEIOLATCH_UP 0x423
--? Last Waittype Others
-- PWait_RESOURCE_SEMAPHORE 0x40 Waiting to a acquire a resource semaphore. Used for synchronization.
-- PWait_DTC 0x41 Waiting on Distributed Transaction Coordinator (DTC).
-- PWait_OLEDB 0x42 Waiting on an OLE DB provider.
-- PWait_WRITELOG 0x81 Waiting for log records for a transaction to be flushed to disk.
-- PWait_PSS_CHILD 0x101 Waiting on a child thread in asynchronous cursor operations.
-- PWait_EXCHANGE 0x200 Exchange synchronization up for parallel query threads.
-- PWait_XCB 0x201 Acquiring access to a transaction control block.
-- Transaction control blocks (XCBs) are usually private to a session,but can be shared between sessions when using the bound session feature or having multiple sessions enlist in the same DTC transaction. Only a single session can have access to the XCB at a time. This waittype likely indicates one session waiting for the XCB while the other session which is holding the XCB resource is waiting on a separate resource.
-- PWait_DBTABLE 0x202 Only used by Checkpoint process.
-- PWait_EC 0x203 Killing a connection subthread or Execution Context.?
-- PWait_TEMPOBJ 0x204 Dropping a Temporary Object.
-- PWait_XACTLOCKINFO 0x205 Waiting on Bulk Operation when releasingescalatingtransferring locks.
-- PWait_LOGMGR 0x206 Waiting on log writer.
-- PWait_CMEMTHREAD 0x207 Waiting on access to memory object.
-- PWait_CXPACKET 0x208 Waiting on packet synchronize up for exchange operator (parallel query).
-- PWait_PAGESUPP 0x209 Release Spinlock in parallel query thread.
-- PWait_SHUTDOWN 0x20A Wait for SPID to finish completion before shutdown.
-- PWait_WAITFOR 0x20B Wait initiated by a WAITFOR command.
-- PWait_CURSOR 0x20C Waiting for thread synchronization with asynchronous cursors.
--?? Waitresource
-- TAB: DatabaseID:ObjectID? (Table)
-- PAGE: DatabaseID:FileID:PageID
-- KEY: DatabaseID:ObjectID:IndexID (Hash value for index key)
-- RID: DatabaseID:FileID:PageID:Slot (row)
-- Lock Count: Each time a transaction asks for a lock on a particular resource,a reference count is incremented. The lock cannot be released until the reference count equals 0.
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
这个脚本可以查看引起锁定的头进程及被堵塞的相关进程的详细信息；
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
