DatabaseProcApplicationCreatedLinks
sybsystemprocssp_lock  31 Aug 14Defects 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
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 58
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 75
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 101
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 116
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 141
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 164
 TNOI 4 Table with no index master..syslocks master..syslocks
 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..syslocks  
 MGTP 3 Grant to public sybsystemprocs..sp_lock  
 MNER 3 No Error Check should check return value of exec 36
 MUCO 3 Useless Code Useless Brackets 49
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 172
 QCRS 3 Conditional Result Set 52
 QCRS 3 Conditional Result Set 69
 QCRS 3 Conditional Result Set 95
 QCRS 3 Conditional Result Set 110
 QCRS 3 Conditional Result Set 132
 QCRS 3 Conditional Result Set 155
 QISO 3 Set isolation level 29
 QNAJ 3 Not using ANSI Inner Join 55
 QNAJ 3 Not using ANSI Inner Join 72
 QNAJ 3 Not using ANSI Inner Join 98
 QNAJ 3 Not using ANSI Inner Join 113
 QNAJ 3 Not using ANSI Inner Join 138
 QNAJ 3 Not using ANSI Inner Join 161
 QNUA 3 Should use Alias: Column fid should use alias l 52
 QNUA 3 Should use Alias: Column id should use alias l 52
 QNUA 3 Should use Alias: Column loid should use alias l 52
 QNUA 3 Should use Alias: Column spid should use alias l 52
 QNUA 3 Should use Alias: Column class should use alias l 53
 QNUA 3 Should use Alias: Column dbid should use alias l 53
 QNUA 3 Should use Alias: Column page should use alias l 53
 QNUA 3 Should use Alias: Column row should use alias l 53
 QNUA 3 Should use Alias: Column spid should use alias l 62
 QNUA 3 Should use Alias: Column fid should use alias l 69
 QNUA 3 Should use Alias: Column id should use alias l 69
 QNUA 3 Should use Alias: Column loid should use alias l 69
 QNUA 3 Should use Alias: Column spid should use alias l 69
 QNUA 3 Should use Alias: Column dbid should use alias l 70
 QNUA 3 Should use Alias: Column page should use alias l 70
 QNUA 3 Should use Alias: Column row should use alias l 70
 QNUA 3 Should use Alias: Column class should use alias l 71
 QNUA 3 Should use Alias: Column spid should use alias l 79
 QNUA 3 Should use Alias: Column fid should use alias l 95
 QNUA 3 Should use Alias: Column id should use alias l 95
 QNUA 3 Should use Alias: Column loid should use alias l 95
 QNUA 3 Should use Alias: Column spid should use alias l 95
 QNUA 3 Should use Alias: Column class should use alias l 96
 QNUA 3 Should use Alias: Column dbid should use alias l 96
 QNUA 3 Should use Alias: Column page should use alias l 96
 QNUA 3 Should use Alias: Column row should use alias l 96
 QNUA 3 Should use Alias: Column fid should use alias l 110
 QNUA 3 Should use Alias: Column id should use alias l 110
 QNUA 3 Should use Alias: Column loid should use alias l 110
 QNUA 3 Should use Alias: Column spid should use alias l 110
 QNUA 3 Should use Alias: Column dbid should use alias l 111
 QNUA 3 Should use Alias: Column page should use alias l 111
 QNUA 3 Should use Alias: Column row should use alias l 111
 QNUA 3 Should use Alias: Column class should use alias l 112
 QNUA 3 Should use Alias: Column fid should use alias l 132
 QNUA 3 Should use Alias: Column loid should use alias l 132
 QNUA 3 Should use Alias: Column spid should use alias l 132
 QNUA 3 Should use Alias: Column page should use alias l 133
 QNUA 3 Should use Alias: Column row should use alias l 133
 QNUA 3 Should use Alias: Column dbid should use alias l 134
 QNUA 3 Should use Alias: Column dbid should use alias l 135
 QNUA 3 Should use Alias: Column id should use alias l 135
 QNUA 3 Should use Alias: Column id should use alias l 136
 QNUA 3 Should use Alias: Column class should use alias l 137
 QNUA 3 Should use Alias: Column spid should use alias l 145
 QNUA 3 Should use Alias: Column fid should use alias l 155
 QNUA 3 Should use Alias: Column loid should use alias l 155
 QNUA 3 Should use Alias: Column spid should use alias l 155
 QNUA 3 Should use Alias: Column page should use alias l 156
 QNUA 3 Should use Alias: Column row should use alias l 156
 QNUA 3 Should use Alias: Column dbid should use alias l 157
 QNUA 3 Should use Alias: Column dbid should use alias l 158
 QNUA 3 Should use Alias: Column id should use alias l 158
 QNUA 3 Should use Alias: Column id should use alias l 159
 QNUA 3 Should use Alias: Column class should use alias l 160
 VUNU 3 Variable is not used @instanceid 17
 MRST 2 Result Set Marker 52
 MRST 2 Result Set Marker 69
 MRST 2 Result Set Marker 95
 MRST 2 Result Set Marker 110
 MRST 2 Result Set Marker 132
 MRST 2 Result Set Marker 155
 MTR1 2 Metrics: Comments Ratio Comments: 20% 13
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 9dec - 1exi + 2 13
 MTR3 2 Metrics: Query Complexity Complexity: 80 13
 PRED_QUERY_COLLECTION 2 {l=master..syslocks, sv=master..spt_values, sv2=master..spt_values} 0 52
 PRED_QUERY_COLLECTION 2 {l=master..syslocks, sv=master..spt_values, sv2=master..spt_values} 0 69
 PRED_QUERY_COLLECTION 2 {l=master..syslocks, sv=master..spt_values, sv2=master..spt_values} 0 95
 PRED_QUERY_COLLECTION 2 {l=master..syslocks, sv=master..spt_values, sv2=master..spt_values} 0 110
 PRED_QUERY_COLLECTION 2 {l=master..syslocks, sv=master..spt_values, sv2=master..spt_values} 0 132
 PRED_QUERY_COLLECTION 2 {l=master..syslocks, sv=master..spt_values, sv2=master..spt_values} 0 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)