DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_deadlock_verbose  14 déc. 14Defects Propagation 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 or replace 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   

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 219
 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: 18 = 19dec - 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