SQL中的事务处理机制--sp_lock2

2015/11/19 43088

清华大佬耗费三个月吐血整理的几百G的资源，免费分享！....>>>

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
sp_lock2
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).
 
This stored procedure can be used instead of sp_lock system stored procedure
to return more detailed locking view (it can return user name, host name,
database name, object name, index name and object owner).
This is the example to use sp_lock2:
 
EXEC sp_lock2
 
/*
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).
 
This stored procedure can be used instead of sp_lock stored procedure
to return more detailed locking view (it can return user name, host name,
database name, object name, index name and object owner).
This is the example to use sp_lock2:
 
EXEC sp_lock2
*/
 
USE MASTER
GO
IF OBJECT_ID('sp_lock2') IS NOT NULL DROP PROC sp_lock2
GO
CREATE PROCEDURE sp_lock2
@spid1 int = NULL,      /* server process id to check for locks */
@spid2 int = NULL       /* other process id to check for locks */
as
 
set nocount on
/*
** Show the locks for both parameters.
*/
declare @objid int,
   @indid int,
   @dbid int,
   @string Nvarchar(255)
 
CREATE TABLE #locktable
   (
   spid       smallint
   ,loginname nvarchar(20)
   ,hostname  nvarchar(30)
   ,dbid      int
   ,dbname    nvarchar(20)
   ,ObjOwner  nvarchar(128)
   ,objId     int
   ,ObjName   nvarchar(128)
   ,IndId     int
   ,IndName   nvarchar(128)
   ,Type      nvarchar(4)
   ,Resource  nvarchar(16)
   ,Mode      nvarchar(8)
   ,Status    nvarchar(5)
   )
 
if @spid1 is not NULL
begin
   INSERT #locktable
      (
      spid
      ,loginname
      ,hostname
      ,dbid
      ,dbname
      ,ObjOwner
      ,objId
      ,ObjName
      ,IndId
      ,IndName
      ,Type
      ,Resource
      ,Mode
      ,Status
      )
   select convert (smallint, l.req_spid)
      ,coalesce(substring (s.loginame, 1, 20),'')
      ,coalesce(substring (s.hostname, 1, 30),'')
      ,l.rsc_dbid
      ,substring (db_name(l.rsc_dbid), 1, 20)
      ,''
      ,l.rsc_objid
      ,''
      ,l.rsc_indid
      ,''
      ,substring (v.name, 1, 4)
      ,substring (l.rsc_text, 1, 16)
      ,substring (u.name, 1, 8)
      ,substring (x.name, 1, 5)
   from master.dbo.syslockinfo l,
      master.dbo.spt_values v,
      master.dbo.spt_values x,
      master.dbo.spt_values u,
      master.dbo.sysprocesses s
   where l.rsc_type = v.number
   and   v.type = 'LR'
   and   l.req_status = x.number
   and   x.type = 'LS'
   and   l.req_mode + 1 = u.number
   and   u.type = 'L'
   and   req_spid in (@spid1, @spid2)
   and   req_spid = s.spid
end
/*
** No parameters, so show all the locks.
*/
else
begin
   INSERT #locktable
      (
      spid
      ,loginname
      ,hostname
      ,dbid
      ,dbname
      ,ObjOwner
      ,objId
      ,ObjName
      ,IndId
      ,IndName
      ,Type
      ,Resource
      ,Mode
      ,Status
      )
   select convert (smallint, l.req_spid)
      ,coalesce(substring (s.loginame, 1, 20),'')
      ,coalesce(substring (s.hostname, 1, 30),'')
      ,l.rsc_dbid
      ,substring (db_name(l.rsc_dbid), 1, 20)
      ,''
      ,l.rsc_objid
      ,''
      ,l.rsc_indid
      ,''
      ,substring (v.name, 1, 4)
      ,substring (l.rsc_text, 1, 16)
      ,substring (u.name, 1, 8)
      ,substring (x.name, 1, 5)
   from master.dbo.syslockinfo l,
      master.dbo.spt_values v,
      master.dbo.spt_values x,
      master.dbo.spt_values u,
      master.dbo.sysprocesses s
   where l.rsc_type = v.number
   and   v.type = 'LR'
   and   l.req_status = x.number
   and   x.type = 'LS'
   and   l.req_mode + 1 = u.number
   and   u.type = 'L'
   and   req_spid = s.spid
   order by spID
END
DECLARE lock_cursor CURSOR
FOR SELECT dbid, ObjId, IndId FROM #locktable
  WHERE Type <>'DB' and Type <> 'FIL'
 
OPEN lock_cursor
FETCH NEXT FROM lock_cursor INTO @dbid, @ObjId, @IndId
WHILE @@FETCH_STATUS = 0
   BEGIN
 
   SELECT @string =
      'USE ' + db_name(@dbid) + char(13)
      + 'update #locktable set ObjName = name, ObjOwner = USER_NAME(uid)'
      + ' from sysobjects where id = ' + convert(varchar(32),@objid)
      + ' and ObjId = ' + convert(varchar(32),@objid)
      + ' and dbid = ' + convert(varchar(32),@dbId)
 
   EXECUTE (@string)
 
   SELECT @string =
      'USE ' + db_name(@dbid) + char(13)
      + 'update #locktable set IndName = i.name from sysindexes i '
      + ' where i.id = ' + convert(varchar(32),@objid)
      + ' and i.indid = ' + convert(varchar(32),@indid)
      + ' and ObjId = ' + convert(varchar(32),@objid)
      + ' and dbid = ' + convert(varchar(32),@dbId)
      + ' and #locktable.indid = ' + convert(varchar(32),@indid)
 
   EXECUTE (@string)
 
   FETCH NEXT FROM lock_cursor INTO @dbid, @ObjId, @IndId
   END
CLOSE lock_cursor
DEALLOCATE lock_cursor
 
SELECT * FROM #locktable
return (0)
-- END sp_lock2
GO