sql中的事务处理机制 事务处理 sp_lock2version: sql server 7.0/2000created by: alexander chigrikhttp://www.mssqlcity.com/ - all about ms sql(sql server articles, faq, scripts, tips and test exams). this stored procedure can be used instead of
sql中的事务处理机制 事务处理 sp_lock2version: sql server 7.0/2000created by: alexander chigrikhttp://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 procedureto 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/2000created by: alexander chigrikhttp://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 procedureto 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 mastergoif object_id('sp_lock2') is not null drop proc sp_lock2gocreate procedure sp_lock2@spid1 int = null, /* server process id to check for locks */@spid2 int = null /* other process id to check for locks */asset 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 nullbegin 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.spidend/*** no parameters, so show all the locks.*/elsebegin 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 spidenddeclare lock_cursor cursorfor select dbid, objid, indid from #locktable where type 'db' and type 'fil'open lock_cursorfetch next from lock_cursor into @dbid, @objid, @indidwhile @@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 endclose lock_cursordeallocate lock_cursorselect * from #locktablereturn (0)-- end sp_lock2go