Database | Proc | Application | Created | Links |
sybsystemprocs | sp_familylock ![]() | ![]() | 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 ** The output of this stored proc is almost identical to the output of 6 ** sp_lock. It differs in that the rows displayed here are a subset of 7 ** the output rows of sp_lock having the parent spid represented by the 8 ** parameters. If sp_familylock is invoked with no parameter it will 9 ** report information on all processes (belonging to families) that 10 ** currently hold locks. 11 ** 12 ** Messages for "sp_familylock" 13 ** 14 ** 18052, "The class column will display the cursor name for locks associated 15 ** with a cursor for the current user and the cursor id for other 16 ** users." 17 */ 18 19 20 create procedure sp_familylock 21 @fpid1 int = NULL, /* server family id to check for locks */ 22 @fpid2 int = NULL /* other family id to check for locks */ 23 as 24 25 declare @length int 26 declare @msg varchar(1024) 27 28 29 if @@trancount = 0 30 begin 31 set chained off 32 end 33 34 set transaction isolation level 1 35 36 /* Print warning message about cursor lock info: 37 ** 18052, "The class column will display the cursor name for locks associated 38 ** with a cursor for the current user and the cursor id for other 39 ** users." 40 */ 41 exec sp_getmessage 18052, @msg out 42 print @msg 43 44 /* RESOLVE: ensure that fpid1 and fpid2 are not NULL. */ 45 /* 46 ** Show the locks for both parameters. 47 */ 48 if @fpid1 is not NULL 49 begin 50 select @length = max(datalength(db_name(dbid))) 51 from master..syslocks 52 where fid in (@fpid1, @fpid2) 53 54 /* Print the familyid and context type of the lock and order the output 55 ** by fid, spid. 56 */ 57 if (@length > 15) 58 begin 59 60 select fid, spid, loid, locktype = v1.name, table_id = l.id, page, 61 dbname = db_name(dbid), class, context = v2.name 62 from master..syslocks l, master..spt_values v1, master..spt_values v2 63 where l.type = v1.number 64 and v1.type = "L" 65 and (l.context + 2048) = v2.number 66 and v2.type = "L2" 67 and fid in (@fpid1, @fpid2) 68 and fid in (select fid from master..sysprocesses 69 where fid != 0) 70 order by fid, spid, loid, dbname, table_id, locktype, page 71 end 72 else 73 select fid, spid, loid, locktype = v1.name, table_id = l.id, page, 74 dbname = convert(char(15), db_name(dbid)), class, context = v2.name 75 from master..syslocks l, master..spt_values v1, master..spt_values v2 76 where l.type = v1.number 77 and v1.type = "L" 78 and (l.context + 2048) = v2.number 79 and v2.type = "L2" 80 and fid in (@fpid1, @fpid2) 81 and fid in (select fid from master..sysprocesses 82 where fid != 0) 83 order by fid, spid, loid, dbname, table_id, locktype, page 84 end 85 86 /* 87 ** No parameters, so show all the locks belonging to family tasks. 88 */ 89 else 90 begin 91 select @length = max(datalength(db_name(dbid))) 92 from master..syslocks 93 94 if (@length > 15) 95 select fid, l.spid, loid, locktype = v1.name, table_id = l.id, page, 96 dbname = db_name(l.dbid), class, context = v2.name 97 from master..syslocks l, master..spt_values v1, master..spt_values v2 98 where l.type = v1.number 99 and v1.type = "L" 100 and (l.context + 2048) = v2.number 101 and v2.type = "L2" 102 and fid in (select distinct fid from master..sysprocesses 103 where fid != 0) 104 order by fid, spid, loid, dbname, table_id, locktype, page 105 else 106 select fid, l.spid, loid, locktype = v1.name, table_id = l.id, page, 107 dbname = convert(char(15), db_name(l.dbid)), class, context = v2.name 108 from master..syslocks l, master..spt_values v1, master..spt_values v2 109 where l.type = v1.number 110 and v1.type = "L" 111 and (l.context + 2048) = v2.number 112 and v2.type = "L2" 113 and fid in (select distinct fid from master..sysprocesses 114 where fid != 0) 115 order by fid, spid, loid, dbname, table_id, locktype, page 116 end 117 118 return (0) 119
exec sp_procxmode 'sp_familylock', 'AnyMode' go Grant Execute on sp_familylock to public go
RESULT SETS | |
sp_familylock_rset_004 | |
sp_familylock_rset_003 | |
sp_familylock_rset_002 | |
sp_familylock_rset_001 |
DEPENDENCIES |
PROCS AND TABLES USED reads table master..spt_values (1) ![]() calls proc sybsystemprocs..sp_getmessage ![]() reads table sybsystemprocs..sysusermessages ![]() reads table master..sysmessages (1) ![]() reads table master..syslanguages (1) ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table master..syslocks (1) ![]() reads table master..sysprocesses (1) ![]() |