Database | Proc | Application | Created | Links |
sybsystemprocs | sp_lock ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 /* 4.8 1.1 06/14/90 sproc/src/lock */ 4 /* 5 ** Messages for "sp_lock" 6 ** 7 ** 18052, "The class column will display the cursor name for locks associated 8 ** with a cursor for the current user and the cursor id for other 9 ** users." 10 */ 11 12 13 create procedure sp_lock 14 @spid1 int = NULL, /* server process id to check for locks */ 15 @spid2 int = NULL, /* other process id to check for locks */ 16 @verbose int = NULL, /* if set, print object name of locked objects */ 17 @instanceid int = NULL /* instance id for the locks output */ 18 as 19 20 declare @length int 21 declare @msg varchar(1024) 22 23 24 if @@trancount = 0 25 begin 26 set chained off 27 end 28 29 set transaction isolation level 1 30 31 /* Print warning message about cursor lock info: 32 ** 18052, "The class column will display the cursor name for locks associated 33 ** with a cursor for the current user and the cursor id for other 34 ** users." 35 */ 36 exec sp_getmessage 18052, @msg out 37 print @msg 38 39 40 /* 41 ** Show the locks for both parameters. 42 */ 43 if (@verbose IS NULL) AND (@spid1 is not NULL) 44 begin 45 select @length = max(datalength(db_name(dbid))) 46 from master..syslocks 47 where spid in (@spid1, @spid2) 48 49 if (@length > 15) 50 begin 51 52 select fid, spid, loid, locktype = v1.name, table_id = id, 53 page, row, dbname = db_name(dbid), class, 54 context = v2.name 55 from master..syslocks l, 56 master..spt_values v1, 57 master..spt_values v2 58 where l.type = v1.number 59 and v1.type = "L" 60 and (l.context + 2049) = v2.number 61 and v2.type = "L2" 62 and spid in (@spid1, @spid2) 63 order by fid, spid, loid, dbname, table_id, page, 64 row, locktype 65 end 66 else 67 begin 68 69 select fid, spid, loid, locktype = v1.name, table_id = id, 70 page, row, dbname = convert(char(15), db_name(dbid)), 71 class, context = v2.name 72 from master..syslocks l, 73 master..spt_values v1, 74 master..spt_values v2 75 where l.type = v1.number 76 and v1.type = "L" 77 and (l.context + 2049) = v2.number 78 and v2.type = "L2" 79 and spid in (@spid1, @spid2) 80 order by fid, spid, loid, dbname, table_id, page, row, locktype 81 end 82 end 83 84 /* 85 ** No parameters, so show all the locks. 86 */ 87 else if (@verbose IS NULL) 88 begin 89 select @length = max(datalength(db_name(dbid))) 90 from master..syslocks 91 92 if (@length > 15) 93 begin 94 95 select fid, spid, loid, locktype = v1.name, table_id = id, 96 page, row, dbname = db_name(dbid), class, 97 context = v2.name 98 from master..syslocks l, 99 master..spt_values v1, 100 master..spt_values v2 101 where l.type = v1.number 102 and v1.type = "L" 103 and (l.context + 2049) = v2.number 104 and v2.type = "L2" 105 order by fid, spid, loid, dbname, table_id, page, row, locktype 106 end 107 else 108 begin 109 110 select fid, spid, loid, locktype = v1.name, table_id = id, 111 page, row, dbname = convert(char(15), db_name(dbid)), 112 class, context = v2.name 113 from master..syslocks l, 114 master..spt_values v1, 115 master..spt_values v2 116 where l.type = v1.number 117 and v1.type = "L" 118 and (l.context + 2049) = v2.number 119 and v2.type = "L2" 120 order by fid, spid, loid, dbname, table_id, page, row, locktype 121 end 122 end 123 124 /* 125 ** Show the locks for both parameters. 126 ** Best viewed in wide format. After all we will have long 127 ** identifiers soon. 128 */ 129 else if (@verbose is not NULL) AND (@spid1 is not NULL) 130 begin 131 132 select fid, spid, loid, locktype = v1.name, 133 page, row, 134 objectName = rtrim(db_name(dbid)) + '..' + 135 rtrim(object_name(id, dbid)), 136 id, 137 class, context = v2.name 138 from master..syslocks l, 139 master..spt_values v1, 140 master..spt_values v2 141 where l.type = v1.number 142 and v1.type = "L" 143 and (l.context + 2049) = v2.number 144 and v2.type = "L2" 145 and spid in (@spid1, @spid2) 146 order by fid, spid, loid, objectName, page, row, locktype 147 end 148 149 /* 150 ** No parameters, so show all the locks. 151 */ 152 else if (@verbose is not NULL) 153 begin 154 155 select fid, spid, loid, locktype = v1.name, 156 page, row, 157 objectName = rtrim(db_name(dbid)) + '..' + 158 rtrim(object_name(id, dbid)), 159 id, 160 class, context = v2.name 161 from master..syslocks l, 162 master..spt_values v1, 163 master..spt_values v2 164 where l.type = v1.number 165 and v1.type = "L" 166 and (l.context + 2049) = v2.number 167 and v2.type = "L2" 168 order by fid, spid, loid, objectName, page, 169 row, locktype 170 end 171 172 return (0) 173
exec sp_procxmode 'sp_lock', 'AnyMode' go Grant Execute on sp_lock to public go
RESULT SETS | |
sp_lock_rset_004 | |
sp_lock_rset_003 | |
sp_lock_rset_002 | |
sp_lock_rset_001 | |
sp_lock_rset_006 | |
sp_lock_rset_005 |
DEFECTS | |
![]() | 58 |
![]() | 75 |
![]() | 101 |
![]() | 116 |
![]() | 141 |
![]() | 164 |
![]() | master..syslocks |
![]() | master..spt_values |
![]() | |
![]() | |
![]() | |
![]() | 36 |
![]() | 49 |
![]() | 87 |
![]() | 92 |
![]() | 152 |
![]() | 172 |
![]() | 52 |
![]() | 69 |
![]() | 95 |
![]() | 110 |
![]() | 132 |
![]() | 155 |
![]() | 29 |
![]() | 55 |
![]() | 72 |
![]() | 98 |
![]() | 113 |
![]() | 138 |
![]() | 161 |
![]() | 52 |
![]() | 52 |
![]() | 52 |
![]() | 52 |
![]() | 53 |
![]() | 53 |
![]() | 53 |
![]() | 53 |
![]() | 62 |
![]() | 69 |
![]() | 69 |
![]() | 69 |
![]() | 69 |
![]() | 70 |
![]() | 70 |
![]() | 70 |
![]() | 71 |
![]() | 79 |
![]() | 95 |
![]() | 95 |
![]() | 95 |
![]() | 95 |
![]() | 96 |
![]() | 96 |
![]() | 96 |
![]() | 96 |
![]() | 110 |
![]() | 110 |
![]() | 110 |
![]() | 110 |
![]() | 111 |
![]() | 111 |
![]() | 111 |
![]() | 112 |
![]() | 132 |
![]() | 132 |
![]() | 132 |
![]() | 133 |
![]() | 133 |
![]() | 134 |
![]() | 135 |
![]() | 135 |
![]() | 136 |
![]() | 137 |
![]() | 145 |
![]() | 155 |
![]() | 155 |
![]() | 155 |
![]() | 156 |
![]() | 156 |
![]() | 157 |
![]() | 158 |
![]() | 158 |
![]() | 159 |
![]() | 160 |
![]() | 17 |
![]() | 52 |
![]() | 69 |
![]() | 95 |
![]() | 110 |
![]() | 132 |
![]() | 155 |
![]() | 13 |
![]() | 13 |
![]() | 13 |
![]() | 52 |
![]() | 69 |
![]() | 95 |
![]() | 110 |
![]() | 132 |
![]() | 155 |
DEPENDENCIES |
PROCS AND TABLES USED reads table master..spt_values (1) ![]() calls proc sybsystemprocs..sp_getmessage ![]() reads table sybsystemprocs..sysusermessages ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table master..sysmessages (1) ![]() reads table master..syslanguages (1) ![]() reads table master..syslocks (1) ![]() |