| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_monitor_deadlock_verbose | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** sp_monitor_deadlock_verbose 4 ** 5 ** Generate 'verbose' output for deadlock tracing. This involves stuff 6 ** like user/application names. Also, if a stored procedure was 7 ** involved in a deadlock, decode the stored procedure's name from the 8 ** various dbid/objectid data we have, and call the text analyzer to 9 ** spit out the fragment of SQL that was probably running when the 10 ** deadlock event occured. 11 ** 12 ** Parameters: 13 ** @deadlockid_str - 'Deadlock ID' string. 14 ** @dlk_id_inner - Deadlock ID being processed. 15 ** @heldspid - SPID of task holding locks. 16 ** @heldusername - User name of holding SPID. 17 ** @heldapplname - Application name of holding SPID. 18 ** @waitusername - User name of waiting SPID. 19 ** @heldprocdbid - DBID of sproc (if any) of holding task. 20 ** @heldproecdureid - Object ID of sproc (if any) of holding task. 21 ** @heldprocdbname - Name of db of sproc executed by holding task. 22 ** @heldprocedurename - Name of sproc executed by holding task. 23 ** @heldlinenumber - Line # where holding SPID was executing. 24 ** @objdbid - DBID of object whose page was involved. 25 ** @pagenumber - Page # of page involved in deadlock. 26 ** @objname - Name of object holding lock on page. 27 ** @pagediag - Whether to call page diagnostics built-ins. 28 ** 29 ** Returns: 30 ** 0 - If everything went well; 31 ** !=0 - In case of any errors. 32 { 33 */ 34 create procedure sp_monitor_deadlock_verbose( 35 @deadlockid_str varchar(12) = NULL 36 , @dlk_id_inner int = NULL 37 , @heldspid int = NULL 38 , @heldusername varchar(30) = NULL 39 , @heldapplname varchar(30) = NULL 40 , @waitusername varchar(30) = NULL 41 42 , @heldprocdbid int = NULL 43 , @heldprocedureid int = NULL 44 , @heldprocdbname varchar(30) = NULL 45 , @heldprocedurename varchar(255) = NULL 46 , @heldlinenumber int = NULL 47 , @objdbid int = NULL 48 , @pagenumber int = NULL 49 , @objname varchar(30) = NULL 50 , @pagediag int = 0 51 , @heldsrcID varchar(30) = NULL 52 , @waitsrcID varchar(30) = NULL 53 ) as 54 begin 55 56 declare 57 -- Variable to store name of sp_showtext with db-context. 58 @sp_showtext varchar(100) 59 60 -- Fields extracted from a page header. 61 -- 62 , @page_objid int 63 , @page_indid int 64 , @page_ptnid int 65 , @curr_objname varchar(30) 66 , @pagetype varchar(20) 67 , @hpdbname_lcl varchar(30) 68 , @hprocname_lcl varchar(255) 69 , @noname varchar(6) 70 71 print " %1! %2! Holding: [user name: '%3!' Appl Name: '%4!' procdbid='%5!' procid='%6!'] Waiting: [user name: '%7!']" 72 , @deadlockid_str 73 , @dlk_id_inner 74 , @heldusername 75 , @heldapplname 76 , @heldprocdbid 77 , @heldprocedureid 78 , @waitusername 79 80 -- Don't try to report these if they come out NULL, which they 81 -- will if the data being reported on is from a 125x archive. 82 -- 83 if ((@heldsrcID IS NOT NULL) or (@waitsrcID IS NOT NULL)) 84 begin 85 print " %1! %2! Held lock at '%3!' Waiting for lock at '%4!'" 86 , @deadlockid_str 87 , @dlk_id_inner 88 , @heldsrcID 89 , @waitsrcID 90 end 91 92 select @noname = "<null>" 93 94 -- ================================================================== 95 -- Extract page's object identity, validate that it is still allocated 96 -- to @objname. Then find out its partition id, and index ID. Report 97 -- what kind of page it is (data, index, text etc.) 98 -- 99 if ((@pagediag = 1) 100 and (@objname IS NOT NULL) and (@pagenumber > 0) 101 and (@objdbid > 0)) 102 begin 103 select @page_objid = pageinfo(@objdbid, @pagenumber, 104 "object id on extent") 105 106 -- If page still belongs to the same object as it was when 107 -- the deadlock was recorded, go back to the page and find 108 -- out if it's an index/data/text page. Report it. 109 -- 110 select @curr_objname = object_name(@page_objid, @objdbid) 111 112 -- @objname is NOT NULL from above, so following will fail if 113 -- @curr_objname is NULL (i.e. page is not currently allocated 114 -- to any object) and will pass if page is still allocated 115 -- to the same object as it was when the deadlock happened. 116 -- 117 if (@curr_objname = @objname) 118 begin 119 select @page_indid = pageinfo(@objdbid, @pagenumber 120 , "index id") 121 122 select @pagetype = case @page_indid 123 when 0 then "data" 124 when 1 then "clustered index" 125 when 255 then "text" 126 else "non-clustered index" 127 end 128 129 print " %1! %2! Page %3! is a '%4!' page, with index id=%5!, of object '%6!'" 130 , @deadlockid_str, @dlk_id_inner 131 , @pagenumber, @pagetype, @page_indid, @objname 132 end 133 else 134 begin 135 if (@curr_objname IS NULL) 136 select @curr_objname = "<unallocated>" 137 138 print " %1! %2! Page %3! in dbid %4! now belongs to object '%5!' and not to object '%6!'" 139 , @deadlockid_str, @dlk_id_inner 140 , @pagenumber, @objdbid 141 , @curr_objname, @objname 142 end 143 end 144 else if (@pagediag = 1) 145 begin 146 print " %1! %2! Requested page diagnostics skipped, as page IDs involved in deadlock were 0. Deadlock possibly due to table-level locks." 147 , @deadlockid_str, @dlk_id_inner 148 149 end 150 151 if ((@heldprocdbid IS NULL) 152 or (@heldprocedureid IS NULL) 153 or (@heldprocdbid = 0) 154 or (@heldprocedureid = 0) 155 ) 156 begin 157 print " %1! %2! Holding task [dbid=%3! procedureid=%4!] was not executing a procedure at the time of the deadlock." 158 , @deadlockid_str, @dlk_id_inner 159 , @heldprocdbid, @heldprocedureid 160 return 161 end 162 163 -- ================================================================== 164 -- Check to see if the derived dbname/procname values are NULL. If so, 165 -- print them as null and bail out. We can't process for SQL text 166 -- if the names themselves are NULL. 167 -- 168 select @hpdbname_lcl = case when @heldprocdbname IS NULL 169 then @noname 170 else @heldprocdbname 171 end 172 , @hprocname_lcl = case when @heldprocedurename IS NULL 173 then @noname 174 else @heldprocedurename 175 end 176 177 print " %1! %2! Holding procedure [dbname: '%3!' (dbid=%4!) name: '%5!' (id=%6!)]" 178 , @deadlockid_str, @dlk_id_inner 179 , @hpdbname_lcl, @heldprocdbid 180 , @hprocname_lcl, @heldprocedureid 181 182 if (@heldprocdbname IS NULL) or (@heldprocedurename IS NULL) 183 begin 184 print " %1! %2! Cannot generate SQL text that was running at the time of the deadlock as the procedure/database names are NULL." 185 , @deadlockid_str, @dlk_id_inner 186 return 0 187 end 188 189 -- When we run deadlock analysis from an archive table, the archived 190 -- data might not be in the original server where the deadlock event 191 -- occurred. In that case, it's possible that the procedure involved 192 -- and the database that the sproc lives (lived) in, does not even 193 -- exist in the server in which the deadlock analyzer is running. 194 -- Check for the existence and match of identity of the sproc that 195 -- was involved in the deadlock before calling for its SQL text. 196 -- 197 if ((@heldprocdbname != db_name(@heldprocdbid)) 198 or (@heldprocedurename != object_name(@heldprocedureid, 199 @heldprocdbid)) 200 ) 201 begin 202 print " %1! %2! Could not find procedure with id=%3! in database: '%4!'. Guessing that SQL text using db/procedure names might still match." 203 , @deadlockid_str, @dlk_id_inner 204 , @heldprocedureid, @heldprocdbid 205 end 206 207 print " " 208 209 if (@heldprocedurename IS NOT NULL) 210 begin 211 print " %1! %2! Holding SPID %3! was executing following SQL fragment at line %4!:" 212 , @deadlockid_str, @dlk_id_inner 213 , @heldspid, @heldlinenumber 214 215 select @sp_showtext = @heldprocdbname 216 + case 217 when @heldprocdbname IS NOT NULL 218 then ".." 219 else "" 220 end 221 + "sp_showtext" 222 223 -- Execute the sproc to pull out the context block where 224 -- the deadlock occured. 225 -- 226 exec @sp_showtext @objname = @heldprocedurename 227 , @startline = @heldlinenumber 228 , @numlines_or_ctxt = 10 229 , @printopts = 'context,linenumbers' 230 print " " 231 end 232 return 0 233 234 end -- } 235
exec sp_procxmode 'sp_monitor_deadlock_verbose', 'AnyMode' go Grant Execute on sp_monitor_deadlock_verbose to public go
| DEFECTS | |
 MEST 4 Empty String will be replaced by Single Space | 219 |
 MGTP 3 Grant to public sybsystemprocs..sp_monitor_deadlock_verbose | |
| MUCO 3 Useless Code Useless Brackets in create proc | 34 |
| MUCO 3 Useless Code Useless Begin-End Pair | 54 |
| MUCO 3 Useless Code Useless Brackets | 83 |
| MUCO 3 Useless Code Useless Brackets | 99 |
| MUCO 3 Useless Code Useless Brackets | 117 |
| MUCO 3 Useless Code Useless Brackets | 135 |
| MUCO 3 Useless Code Useless Brackets | 144 |
| MUCO 3 Useless Code Useless Brackets | 151 |
| MUCO 3 Useless Code Useless Brackets | 197 |
| MUCO 3 Useless Code Useless Brackets | 209 |
| VUNU 3 Variable is not used @page_ptnid | 64 |
 MDYE 2 Dynamic Exec Marker exec @sp_showtext | 226 |
| MTR1 2 Metrics: Comments Ratio Comments: 38% | 34 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 17 = 18dec - 3exi + 2 | 34 |
| MTR3 2 Metrics: Query Complexity Complexity: 56 | 34 |
| DEPENDENCIES |
| CALLERS called by proc sybsystemprocs..sp_monitor_deadlock_driver called by proc sybsystemprocs..sp_monitor_deadlock  called by proc sybsystemprocs..sp_monitor |