DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_deadlock_driver  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** sp_monitor_deadlock_driver
4     **
5     **	Main driver procedure to perform deadlock analysis.
6     **
7     ** Parameters:
8     **	@opt_verbose		- Verbose output.
9     **	@opt_pagediag		- Generate page diagnostics also.
10    **				  (Only works if @opt_verbose is also 1.)
11    **	@num_deadlocks		- (Output) # of deadlock events processed.
12    **
13    ** Returns:
14    **	Nothing.
15    {
16    */
17    create or replace procedure sp_monitor_deadlock_driver(
18        @opt_verbose int = 0
19        , @opt_pagediag int = 0
20        , @num_deadlocks int output
21    ) as
22        begin
23            declare @dlk_id int
24                , @retval int
25                , @resolve_time datetime
26                , @dbname varchar(30)
27    
28                -- Variables to retrieve info about each line-item for
29                -- a given deadlock ID.
30    
31                , @dlk_id_inner int
32                , @held_spid int
33                , @wait_spid int
34                , @held_cmd varchar(30)
35                , @held_xactname varchar(255)
36    
37                -- , @wait_cmd	varchar(30)
38                -- , @wait_xactname	varchar(255)
39                , @servername varchar(30)
40                , @objname varchar(255)
41                , @instancename varchar(30)
42                , @objdbid int
43                , @pagenumber int
44                , @pagediag int --whether to call page diagnostics
45                , @heldlocktype varchar(20)
46                , @waitlocktype varchar(20)
47                , @heldusername varchar(30)
48                , @heldapplname varchar(30)
49                , @waitusername varchar(30)
50    
51                -- Location in code where deadlock occurred.
52                , @heldsrcID varchar(30)
53                , @waitsrcID varchar(30)
54    
55                -- Get identity of proc (if any) of holding spid.
56                --
57                , @heldprocdbid int
58                , @heldprocedureid int
59                , @heldlinenumber int
60                , @heldprocdbname varchar(30)
61                , @heldprocedurename varchar(255)
62    
63                -- Common phrases used in print outputs. Centralize for
64                -- easy 'bgrep' processing.
65                , @deadlockid_str varchar(12)
66    
67            select @num_deadlocks = 0
68                , @deadlockid_str = "Deadlock ID"
69    
70            -- Process one row for given deadlock ID or all rows in the table,
71            -- and produce a report for each deadlock occurence, to show the
72            -- wait-for graph in English-language 1204'esque output.
73            --
74            declare dlitem_cur cursor for
75            select DISTINCT ServerName, DeadlockID, ObjectDBName,
76                ResolveTime, InstanceName
77            from #mon__deadlock_info
78    
79            open dlitem_cur
80    
81            declare dlitem_cur_inner cursor for
82            select DeadlockID
83                , HeldSPID
84                , WaitSPID
85                , HeldCommand
86                , HeldTranName
87                , ObjectDBID
88                , ObjectName
89                , PageNumber
90                , HeldLockType
91                , WaitLockType
92    
93                -- Following Fields reported under 'verbose' output model
94                , HeldUserName
95                , HeldApplName
96                , WaitUserName
97    
98                , HeldProcDBID
99                , HeldProcedureID
100               , HeldProcDBName
101               , HeldProcedureName
102               , HeldLineNumber
103               , HeldSourceCodeID
104               , WaitSourceCodeID
105   
106           from #mon__deadlock_info
107           where DeadlockID = @dlk_id
108               and ResolveTime = @resolve_time
109   
110           while (1 = 1)
111           begin -- {
112               fetch dlitem_cur into @servername, @dlk_id
113                   , @dbname
114                   , @resolve_time
115                   , @instancename
116               if (@@sqlstatus != 0)
117                   break
118   
119               select @num_deadlocks = @num_deadlocks + 1
120   
121               print ' '
122               if (@@clustermode = 'shared disk cluster')
123               begin
124                   print "****** Server: '%1!' %2! %3! Dbname: '%4!' Resolve Time: %5! Detected by Instance: '%6!' ****** "
125                       , @servername
126                       , @deadlockid_str
127                       , @dlk_id, @dbname
128                       , @resolve_time
129                       , @instancename
130               end
131               else
132               begin
133                   print "****** Server: '%1!' %2! %3! Dbname: '%4!' Resolve Time: %5! ****** "
134                       , @servername
135                       , @deadlockid_str
136                       , @dlk_id, @dbname
137                       , @resolve_time
138               end
139               -- For this DeadlockID, go back and process all rows.
140               open dlitem_cur_inner
141   
142               while (1 = 1)
143               begin -- {
144   
145                   fetch dlitem_cur_inner into @dlk_id_inner
146                       , @held_spid
147                       , @wait_spid
148                       , @held_cmd
149                       , @held_xactname
150                       , @objdbid
151                       , @objname
152                       , @pagenumber
153                       , @heldlocktype
154                       , @waitlocktype
155                       , @heldusername
156                       , @heldapplname
157                       , @waitusername
158                       , @heldprocdbid
159                       , @heldprocedureid
160                       , @heldprocdbname
161                       , @heldprocedurename
162                       , @heldlinenumber
163                       , @heldsrcID
164                       , @waitsrcID
165   
166                   if (@@sqlstatus != 0)
167                       break
168   
169                   print "  %1! %2! Spid %3! was waiting for '%4!' lock on dbid=%5! object '%6!' page=%7!"
170                       , @deadlockid_str
171                       , @dlk_id_inner
172                       , @wait_spid
173                       , @waitlocktype
174                       , @objdbid
175                       , @objname
176                       , @pagenumber
177   
178                   print "  %1! %2! Spid %3! was holding '%4!' lock on object '%5!' page=%6!. Command: '%7!', Holding TranName: '%8!'"
179                       , @deadlockid_str
180                       , @dlk_id_inner
181                       , @held_spid
182                       , @heldlocktype
183                       , @objname
184                       , @pagenumber
185                       , @held_cmd
186                       , @held_xactname
187   
188                   if (@opt_verbose = 1)
189                   begin -- {
190   
191                       exec @retval = sp_monitor_deadlock_verbose
192                           @deadlockid_str
193                           , @dlk_id_inner
194                           , @held_spid
195                           , @heldusername
196                           , @heldapplname
197                           , @waitusername
198   
199                           , @heldprocdbid
200                           , @heldprocedureid
201                           , @heldprocdbname
202                           , @heldprocedurename
203                           , @heldlinenumber
204   
205                           , @objdbid
206                           , @pagenumber
207                           , @objname
208                           , @opt_pagediag
209   
210                           , @heldsrcID
211                           , @waitsrcID
212   
213                   end -- }
214   
215               end -- }
216   
217               close dlitem_cur_inner
218   
219           end -- }
220           close dlitem_cur
221   
222           deallocate cursor dlitem_cur_inner
223           deallocate cursor dlitem_cur
224   
225       end -- }
226   

DEFECTS
 MTYP 4 Assignment type mismatch @objname: varchar(30) = varchar(255) 207
 VRUN 4 Variable is read and not initialized @dlk_id 107
 VRUN 4 Variable is read and not initialized @resolve_time 108
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause dlitem_cur 75
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause dlitem_cur_inner 82
 MNER 3 No Error Check should check return value of exec 191
 MUCO 3 Useless Code Useless Brackets in create proc 17
 MUCO 3 Useless Code Useless Begin-End Pair 22
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 188
 QGWO 3 Group by/Distinct/Union without order by 75
 VUNU 3 Variable is not used @pagediag 44
 CRDO 2 Read Only Cursor Marker (has a 'distinct' option) 75
 CUPD 2 Updatable Cursor Marker (updatable by default) 82
 MTR1 2 Metrics: Comments Ratio Comments: 18% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 8dec - 1exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 38 17

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_monitor_deadlock_verbose  
reads table tempdb..#mon__deadlock_info (1) 

CALLERS
called by proc sybsystemprocs..sp_monitor_deadlock  
   called by proc sybsystemprocs..sp_monitor