Database | Proc | Application | Created | Links |
sybsystemprocs | sp_transactions ![]() | ![]() | 31 Aug 14 | Defects 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", 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-- 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 */
exec sp_procxmode 'sp_transactions', 'AnyMode' go Grant Execute on sp_transactions to public go
DEFECTS | |
![]() | master..systransactions |
![]() | master..spt_values |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | 247 |
![]() | 288 |
![]() | 83 |
![]() | 91 |
![]() | 137 |
![]() | 194 |
![]() | 196 |
![]() | 203 |
![]() | 227 |
![]() | 63 |
![]() | 95 |
![]() | 141 |
![]() | 178 |
![]() | 241 |
![]() | 249 |
![]() | 256 |
![]() | 266 |
![]() | 290 |
![]() | 314 |
![]() | 57 |
![]() | 73 |
![]() | 111 |
![]() | 155 |
![]() | 276 |
![]() | 300 |
![]() | 324 |
![]() | 63 |
![]() | 66 |
![]() | 69 |
![]() | 69 |
![]() | 69 |
![]() | 71 |
![]() | 71 |
![]() | 95 |
![]() | 98 |
![]() | 101 |
![]() | 101 |
![]() | 101 |
![]() | 103 |
![]() | 103 |
![]() | 105 |
![]() | 107 |
![]() | 109 |
![]() | 141 |
![]() | 144 |
![]() | 147 |
![]() | 147 |
![]() | 147 |
![]() | 149 |
![]() | 149 |
![]() | 151 |
![]() | 153 |
![]() | 266 |
![]() | 269 |
![]() | 272 |
![]() | 272 |
![]() | 272 |
![]() | 274 |
![]() | 274 |
![]() | 290 |
![]() | 293 |
![]() | 296 |
![]() | 296 |
![]() | 296 |
![]() | 298 |
![]() | 298 |
![]() | 314 |
![]() | 317 |
![]() | 320 |
![]() | 320 |
![]() | 320 |
![]() | 322 |
![]() | 322 |
![]() | 252 |
![]() | 50 |
![]() | 63 |
![]() | 95 |
![]() | 141 |
![]() | 178 |
![]() | 241 |
![]() | 249 |
![]() | 256 |
![]() | 266 |
![]() | 290 |
![]() | 314 |
![]() | 192 |
![]() | 41 |
![]() | 41 |
![]() | 41 |
![]() | 63 |
![]() | 95 |
![]() | 141 |
![]() | 266 |
![]() | 290 |
![]() | 314 |
DEPENDENCIES |
PROCS AND TABLES USED reads table master..systransactions (1) ![]() reads table master..spt_values (1) ![]() reads table master..sysdatabases (1) ![]() |