SQL中的事务处理机制--sp_lock2
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL中的事务处理机制--sp_lock2,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5357字,纯文字阅读大概需要8分钟。
内容图文
![SQL中的事务处理机制--sp_lock2](/upload/InfoBanner/zyjiaocheng/567/7d514cb84be54f8cb9d4642c12ce7e44.jpg)
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_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
内容总结
以上是互联网集市为您收集整理的SQL中的事务处理机制--sp_lock2全部内容,希望文章能够帮你解决SQL中的事务处理机制--sp_lock2所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。