DatabaseProcApplicationCreatedLinks
sybsystemprocssp_transactions  31 Aug 14Defects Dependencies

1     
2     /*
3     ** This stored procedure displays information about active
4     ** transactions. sp_transactions can be invoked in one of the following
5     ** modes:  
6     **
7     ** o sp_transactions 			  -- Display about all txns 
8     **
9     ** o sp_transactions "xid", 	  -- Display info about a
10    **					     particular xid.  
11    ** o sp_transactions "state", 
12    **		     "heuristic_commit|heuristic_abort|prepared|indoubt"
13    **		     [,"xactname"]
14    **					  -- Display a transaction info 
15    **					     about all transactins in the 
16    **					     specified state. If the optional
17    **					     xactname parameter is specified,
18    **					     we will select only the xactname
19    **					     column. 
20    **
21    **
22    ** o sp_transactions "gtrid",  -- Display info for a particular 
23    **					     gtrid which is one of the
24    **					     components of xid.
25    **
26    ** o sp_transactions "xa_recover" 	  -- Used only for xa support.
27    **					     Do not document this option.
28    **				
29    */
30    
31    /*
32    ** Messages for "sp_transactions"		18nnn
33    ** 
34    ** 18703  "Please execute the procedure '%1!' from master database".
35    ** 18563, "A transaction name should be specified."
36    ** 18564, "Invalid option. Possible values are: xid and state."
37    ** 18565, "Invalid value for state. Legal values are: heuristic_commit 
38    **	   heuristic_abort  prepared indoubt."
39    */
40    
41    create procedure sp_transactions
42        @query_type varchar(30) = NULL,
43        @parm1 varchar(255) = NULL,
44        @parm2 varchar(255) = NULL
45    as
46    
47        declare @transtate int
48        declare @transtat2 int
49        declare @xid_only int
50        declare @msg varchar(250)
51    
52        if @@trancount = 0
53        begin
54            set chained off
55        end
56    
57        set transaction isolation level 1
58    
59        select @xid_only = 0
60    
61        if @query_type is NULL
62        begin
63            select xactkey,
64                type = convert(char(11), v3.name),
65                coordinator = convert(char(10), v4.name),
66                starttime = convert(char(20), starttime),
67                state = convert(char(17), v1.name),
68                connection = convert(char(9), v2.name),
69                dbid = masterdbid, spid, loid,
70                failover = convert(char(26), v5.name),
71                s.srvname, namelen, xactname
72    
73            from master..systransactions s, master..spt_values v1,
74                master..spt_values v2, master..spt_values v3,
75                master..spt_values v4, master..spt_values v5
76            where
77                s.state = v1.number and v1.type = 'T1'
78                and s.connection = v2.number and v2.type = 'T2'
79                and s.type = v3.number and v3.type = 'T3'
80                and s.coordinator = v4.number and v4.type = 'T4'
81                and s.failover = v5.number and v5.type = 'T5'
82            order by xactkey, s.srvname, s.failover
83            return (0)
84        end
85    
86        if @query_type = "xid"
87        begin
88            if @parm1 is NULL
89            begin
90                raiserror 18563, "sp_transactions"
91                return (1)
92            end
93            else
94            begin
95                select xactkey,
96                    type = convert(char(11), v3.name),
97                    coordinator = convert(char(10), v4.name),
98                    starttime = convert(char(20), starttime),
99                    state = convert(char(17), v1.name),
100                   connection = convert(char(9), v2.name),
101                   dbid = masterdbid, spid, loid,
102                   failover = convert(char(26), v5.name),
103                   s.srvname, namelen, xactname,
104                   commit_node =
105                   coord_node_name(xactname, s.coordinator, s.type, 7),
106                   parent_node =
107                   coord_node_name(xactname, s.coordinator, s.type, 8),
108                   gtrid =
109                   gtrid(xactname, s.coordinator, s.xactkey, s.failover)
110   
111               from master..systransactions s, master..spt_values v1,
112                   master..spt_values v2, master..spt_values v3,
113                   master..spt_values v4, master..spt_values v5
114               where
115                   s.xactname like "%" + @parm1 + "%"
116                   and s.state = v1.number and v1.type = 'T1'
117                   and s.connection = v2.number and v2.type = 'T2'
118                   and s.type = v3.number and v3.type = 'T3'
119                   and s.coordinator = v4.number and v4.type = 'T4'
120                   and s.failover = v5.number and v5.type = 'T5'
121               order by xactkey, s.srvname, s.failover
122   
123               return 0
124           end
125       end
126   
127   
128       /*
129       ** If we are interested in finding a row for a particular gtrid,
130       ** try to find it.
131       */
132       if @query_type = "gtrid"
133       begin
134           if @parm1 is NULL
135           begin
136               raiserror 18563, "sp_transactions"
137               return (1)
138           end
139           else
140           begin
141               select xactkey,
142                   type = convert(char(11), v3.name),
143                   coordinator = convert(char(10), v4.name),
144                   starttime = convert(char(20), starttime),
145                   state = convert(char(17), v1.name),
146                   connection = convert(char(9), v2.name),
147                   dbid = masterdbid, spid, loid,
148                   failover = convert(char(26), v5.name),
149                   s.srvname, namelen, xactname,
150                   commit_node =
151                   coord_node_name(xactname, s.coordinator, s.type, 7),
152                   parent_node =
153                   coord_node_name(xactname, s.coordinator, s.type, 8)
154   
155               from master..systransactions s, master..spt_values v1,
156                   master..spt_values v2, master..spt_values v3,
157                   master..spt_values v4, master..spt_values v5
158               where
159                   s.state = v1.number and v1.type = 'T1'
160                   and s.connection = v2.number and v2.type = 'T2'
161                   and s.type = v3.number and v3.type = 'T3'
162                   and s.coordinator = v4.number and v4.type = 'T4'
163                   and s.failover = v5.number and v5.type = 'T5'
164                   and gtrid(s.xactname, s.coordinator,
165                       s.xactkey, s.failover) = @parm1
166                   and s.type != 99
167               order by xactkey, s.srvname, s.failover
168               return 0
169           end
170       end
171   
172       /*
173       ** Select a list of transactions that satisfies xa requirements.
174       ** Do not change this select without taking xalib into account.
175       */
176       if @query_type = "xa_recover"
177       begin
178           select xactname
179   
180           from master..systransactions s
181           where s.state in ((65536 | 12), (65536 | 13),
182                   4, (65536 | 4))
183               and s.coordinator = 3
184   
185           /*
186           ** Find if there is any database that is not recovered.
187           ** If so return with a status of 1 to indicate to XA Interface
188           ** that not all databases have been recovered and hence the
189           ** list of in-doubt transactions may be incomplete. XA Interface
190           ** knows what to do with this information.
191           */
192           if exists (select * from master..sysdatabases
193                   where (status & 64) != 0)
194               return (1)
195           else
196               return (0)
197       end
198   
199       /* The only query possible is by "state" */
200       if @query_type != "state"
201       begin
202           raiserror 18564, "sp_transactions"
203           return (1)
204       end
205   
206       if @parm1 = "heuristic_commit"
207       begin
208           select @transtate = (65536 | 12)
209           select @transtat2 = (65536 | 12)
210       end
211       else if @parm1 = "heuristic_abort"
212       begin
213           select @transtate = (65536 | 13)
214           select @transtat2 = (65536 | 13)
215       end
216       else if @parm1 = "prepared"
217       begin
218           select @transtate = 4
219           select @transtat2 = (65536 | 4)
220       end
221       /* else, the only other valid value is indoubt in which case we return
222       ** all the above states.  
223       */
224       else if @parm1 != "indoubt"
225       begin
226           raiserror 18565, "sp_transactions"
227           return (1)
228       end
229   
230       if @parm2 = "xactname"
231       begin
232           select @xid_only = 1
233       end
234   
235       /* Now, execute different queries based on xid_only and @parm1 */
236   
237       if @xid_only = 1
238       begin
239           if @parm1 = "indoubt"
240           begin
241               select xactname
242   
243               from master..systransactions
244               where state in ((65536 | 12), (65536 | 13),
245                       4, (65536 | 4))
246           end
247           else if @parm1 != NULL
248           begin
249               select xactname
250   
251               from master..systransactions
252               where state = @transtate or state = @transtat2
253           end
254           else
255           begin
256               select xactname
257   
258               from master..systransactions
259           end
260       end
261       else
262       begin
263           if @parm1 = "indoubt"
264           begin
265   
266               select xactkey,
267                   type = convert(char(11), v3.name),
268                   coordinator = convert(char(10), v4.name),
269                   starttime = convert(char(20), starttime),
270                   state = convert(char(17), v1.name),
271                   connection = convert(char(9), v2.name),
272                   dbid = masterdbid, spid, loid,
273                   failover = convert(char(26), v5.name),
274                   s.srvname, namelen, xactname
275   
276               from master..systransactions s, master..spt_values v1,
277                   master..spt_values v2, master..spt_values v3,
278                   master..spt_values v4, master..spt_values v5
279               where s.state in ((65536 | 12), (65536 | 13),
280                       4, (65536 | 4))
281                   and s.state = v1.number and v1.type = 'T1'
282                   and s.connection = v2.number and v2.type = 'T2'
283                   and s.type = v3.number and v3.type = 'T3'
284                   and s.coordinator = v4.number and v4.type = 'T4'
285                   and s.failover = v5.number and v5.type = 'T5'
286               order by xactkey, s.srvname, s.failover
287           end
288           else if @parm1 != NULL
289           begin
290               select xactkey,
291                   type = convert(char(11), v3.name),
292                   coordinator = convert(char(10), v4.name),
293                   starttime = convert(char(20), starttime),
294                   state = convert(char(17), v1.name),
295                   connection = convert(char(9), v2.name),
296                   dbid = masterdbid, spid, loid,
297                   failover = convert(char(26), v5.name),
298                   s.srvname, namelen, xactname
299   
300               from master..systransactions s, master..spt_values v1,
301                   master..spt_values v2, master..spt_values v3,
302                   master..spt_values v4, master..spt_values v5
303               where
304                   (s.state = @transtate or s.state = @transtat2)
305                   and s.state = v1.number and v1.type = 'T1'
306                   and s.connection = v2.number and v2.type = 'T2'
307                   and s.type = v3.number and v3.type = 'T3'
308                   and s.coordinator = v4.number and v4.type = 'T4'
309                   and s.failover = v5.number and v5.type = 'T5'
310               order by xactkey, s.srvname, s.failover
311           end
312           else
313           begin
314               select xactkey,
315                   type = convert(char(11), v3.name),
316                   coordinator = convert(char(10), v4.name),
317                   starttime = convert(char(20), starttime),
318                   state = convert(char(17), v1.name),
319                   connection = convert(char(9), v2.name),
320                   dbid = masterdbid, spid, loid,
321                   failover = convert(char(26), v5.name),
322                   s.srvname, namelen, xactname
323   
324               from master..systransactions s, master..spt_values v1,
325                   master..spt_values v2, master..spt_values v3,
326                   master..spt_values v4, master..spt_values v5
327               where
328                   s.state = v1.number and v1.type = 'T1'
329                   and s.connection = v2.number and v2.type = 'T2'
330                   and s.type = v3.number and v3.type = 'T3'
331                   and s.coordinator = v4.number and v4.type = 'T4'
332                   and s.failover = v5.number and v5.type = 'T5'
333               order by xactkey, s.srvname, s.failover
334           end
335       end
336   
337       return 0
338   


exec sp_procxmode 'sp_transactions', 'AnyMode'
go

Grant Execute on sp_transactions to public
go
RESULT SETS
sp_transactions_rset_005
sp_transactions_rset_004
sp_transactions_rset_003
sp_transactions_rset_002
sp_transactions_rset_001
sp_transactions_rset_010
sp_transactions_rset_009
sp_transactions_rset_008
sp_transactions_rset_007
sp_transactions_rset_006

DEFECTS
 TNOI 4 Table with no index master..systransactions master..systransactions
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..systransactions  
 MGTP 3 Grant to public sybsystemprocs..sp_transactions  
 MNAC 3 Not using ANSI 'is null' 247
 MNAC 3 Not using ANSI 'is null' 288
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 194
 MUCO 3 Useless Code Useless Brackets 196
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 227
 QCRS 3 Conditional Result Set 63
 QCRS 3 Conditional Result Set 95
 QCRS 3 Conditional Result Set 141
 QCRS 3 Conditional Result Set 178
 QCRS 3 Conditional Result Set 241
 QCRS 3 Conditional Result Set 249
 QCRS 3 Conditional Result Set 256
 QCRS 3 Conditional Result Set 266
 QCRS 3 Conditional Result Set 290
 QCRS 3 Conditional Result Set 314
 QISO 3 Set isolation level 57
 QNAJ 3 Not using ANSI Inner Join 73
 QNAJ 3 Not using ANSI Inner Join 111
 QNAJ 3 Not using ANSI Inner Join 155
 QNAJ 3 Not using ANSI Inner Join 276
 QNAJ 3 Not using ANSI Inner Join 300
 QNAJ 3 Not using ANSI Inner Join 324
 QNUA 3 Should use Alias: Column xactkey should use alias s 63
 QNUA 3 Should use Alias: Column starttime should use alias s 66
 QNUA 3 Should use Alias: Column loid should use alias s 69
 QNUA 3 Should use Alias: Column masterdbid should use alias s 69
 QNUA 3 Should use Alias: Column spid should use alias s 69
 QNUA 3 Should use Alias: Column namelen should use alias s 71
 QNUA 3 Should use Alias: Column xactname should use alias s 71
 QNUA 3 Should use Alias: Column xactkey should use alias s 95
 QNUA 3 Should use Alias: Column starttime should use alias s 98
 QNUA 3 Should use Alias: Column loid should use alias s 101
 QNUA 3 Should use Alias: Column masterdbid should use alias s 101
 QNUA 3 Should use Alias: Column spid should use alias s 101
 QNUA 3 Should use Alias: Column namelen should use alias s 103
 QNUA 3 Should use Alias: Column xactname should use alias s 103
 QNUA 3 Should use Alias: Column xactname should use alias s 105
 QNUA 3 Should use Alias: Column xactname should use alias s 107
 QNUA 3 Should use Alias: Column xactname should use alias s 109
 QNUA 3 Should use Alias: Column xactkey should use alias s 141
 QNUA 3 Should use Alias: Column starttime should use alias s 144
 QNUA 3 Should use Alias: Column loid should use alias s 147
 QNUA 3 Should use Alias: Column masterdbid should use alias s 147
 QNUA 3 Should use Alias: Column spid should use alias s 147
 QNUA 3 Should use Alias: Column namelen should use alias s 149
 QNUA 3 Should use Alias: Column xactname should use alias s 149
 QNUA 3 Should use Alias: Column xactname should use alias s 151
 QNUA 3 Should use Alias: Column xactname should use alias s 153
 QNUA 3 Should use Alias: Column xactkey should use alias s 266
 QNUA 3 Should use Alias: Column starttime should use alias s 269
 QNUA 3 Should use Alias: Column loid should use alias s 272
 QNUA 3 Should use Alias: Column masterdbid should use alias s 272
 QNUA 3 Should use Alias: Column spid should use alias s 272
 QNUA 3 Should use Alias: Column namelen should use alias s 274
 QNUA 3 Should use Alias: Column xactname should use alias s 274
 QNUA 3 Should use Alias: Column xactkey should use alias s 290
 QNUA 3 Should use Alias: Column starttime should use alias s 293
 QNUA 3 Should use Alias: Column loid should use alias s 296
 QNUA 3 Should use Alias: Column masterdbid should use alias s 296
 QNUA 3 Should use Alias: Column spid should use alias s 296
 QNUA 3 Should use Alias: Column namelen should use alias s 298
 QNUA 3 Should use Alias: Column xactname should use alias s 298
 QNUA 3 Should use Alias: Column xactkey should use alias s 314
 QNUA 3 Should use Alias: Column starttime should use alias s 317
 QNUA 3 Should use Alias: Column loid should use alias s 320
 QNUA 3 Should use Alias: Column masterdbid should use alias s 320
 QNUA 3 Should use Alias: Column spid should use alias s 320
 QNUA 3 Should use Alias: Column namelen should use alias s 322
 QNUA 3 Should use Alias: Column xactname should use alias s 322
 QTLO 3 Top-Level OR 252
 VUNU 3 Variable is not used @msg 50
 MRST 2 Result Set Marker 63
 MRST 2 Result Set Marker 95
 MRST 2 Result Set Marker 141
 MRST 2 Result Set Marker 178
 MRST 2 Result Set Marker 241
 MRST 2 Result Set Marker 249
 MRST 2 Result Set Marker 256
 MRST 2 Result Set Marker 266
 MRST 2 Result Set Marker 290
 MRST 2 Result Set Marker 314
 MSUB 2 Subquery Marker 192
 MTR1 2 Metrics: Comments Ratio Comments: 20% 41
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 11 = 19dec - 10exi + 2 41
 MTR3 2 Metrics: Query Complexity Complexity: 191 41
 PRED_QUERY_COLLECTION 2 {sv=master..spt_values, sv2=master..spt_values, sv3=master..spt_values, sv4=master..spt_values, sv5=master..spt_values, t=master..systransactions} 0 63
 PRED_QUERY_COLLECTION 2 {sv=master..spt_values, sv2=master..spt_values, sv3=master..spt_values, sv4=master..spt_values, sv5=master..spt_values, t=master..systransactions} 0 95
 PRED_QUERY_COLLECTION 2 {sv=master..spt_values, sv2=master..spt_values, sv3=master..spt_values, sv4=master..spt_values, sv5=master..spt_values, t=master..systransactions} 0 141
 PRED_QUERY_COLLECTION 2 {sv=master..spt_values, sv2=master..spt_values, sv3=master..spt_values, sv4=master..spt_values, sv5=master..spt_values, t=master..systransactions} 0 266
 PRED_QUERY_COLLECTION 2 {sv=master..spt_values, sv2=master..spt_values, sv3=master..spt_values, sv4=master..spt_values, sv5=master..spt_values, t=master..systransactions} 0 290
 PRED_QUERY_COLLECTION 2 {sv=master..spt_values, sv2=master..spt_values, sv3=master..spt_values, sv4=master..spt_values, sv5=master..spt_values, t=master..systransactions} 0 314

DEPENDENCIES
PROCS AND TABLES USED
reads table master..systransactions (1)  
reads table master..spt_values (1)  
reads table master..sysdatabases (1)