TSQL:是什么导致孤立的表锁 [英] TSQL: What is causing orphaned table locks

问题描述

在过去的三天内，我不得不终止释放表锁的进程，这些进程导致我的Web应用抛出超时错误.除了尝试重新索引表之外，系统已经有好几周没有任何更改了.我确定在此之前表已锁定，但我无法确认.

它始终是相同的表(包和event_log)
事务ID事务名称状态事务类型开始时间隔离级别#锁#页面锁#行锁#其他锁会话ID登录名
#Error hidden_​​transaction有效的完整交易记录09/10/2012 22:40:32快照22 13 7 2 59 carma
数据库名称事务状态第一次更新时间#锁#页锁#行锁#其他锁
notxdb活动时间09/10/2012 22:40:32 22 13 7 2
资源名称
carma.event_log
锁类型请求模式#授予锁#等待锁
对象意图独占1 0
PAGE Intent Exclusive 10 0
KEY Exclusive 5 0
carma.package
锁类型请求模式#授予锁#等待锁
对象意图独占1 0
PAGE Intent Exclusive 3 0
KEY Exclusive 2 0
当前正在执行的SQL语句
-

抱歉，我不记得确切的查询了.所有查询都不是复杂的，但是直到再次出现时，我才能通过事件发现它的类型，如果可能的话，我想避免使用> _<< br mode ="hold"/> ;
理想情况下，我希望完全避免任何停机时间，但是如果我确定该解决方案会有所帮助，那么我可以在几个小时内安排一些停机时间


有谁知道是什么原因造成的，或者我怎么知道是什么原因造成的?

谢谢
^ _ ^

Hi,

For the last three days I have been having to kill of processes to released table locks that cause my web app to throw time-out errors. There have been no changes to the system for many weeks apart from when I attempted to re-index the tables. I am sure that the tables were locked before that but I cannot confirm.

It is always the same tables (packages and event_log)
Transaction ID Transaction Name State Transaction Type Start Time Isolation Level # Locks # Page Locks # Row Locks # Other Locks Session ID Login Name
#Error implicit_transaction Active Full Transaction 09/10/2012 22:40:32 Snapshot 22 13 7 2 59 carma
Database Name Transaction State First Update Time # Locks # Page Locks # Row Locks # Other Locks
notxdb Active 09/10/2012 22:40:32 22 13 7 2
Resource Name
carma.event_log
Lock Type Request Mode # Locks Granted # Locks Waiting
OBJECT Intent Exclusive 1 0
PAGE Intent Exclusive 10 0
KEY Exclusive 5 0
carma.package
Lock Type Request Mode # Locks Granted # Locks Waiting
OBJECT Intent Exclusive 1 0
PAGE Intent Exclusive 3 0
KEY Exclusive 2 0
Currently Executing SQL Statement
--

Sorry but I can''t remember the exact query. None of the queries are complex but I can''t event find out what type of query it was until it reoccurs, and I''d like to avoid that if possible >_<<br mode="hold" />
Ideally I''d like to avoid any downtime at all but if I''m pretty sure the solution will help than I can schedule some downtime within a couple of hours


Does anyone have any idea what is causing this or how I can find out what causes it?

Thanks
^_^

推荐答案

嗨！我建议您运行SQL Server Profiler来收集有关正在发生的事情的完整信息
数据库和有关锁的详细信息(请参考此链接以获取详细信息:

Hi! I suggest you to run SQL Server Profiler to gather full informaion about what''s going
on with the database and a detailed information about locks (Please refer to this link for details: http://msdn.microsoft.com/en-us/library/ms173789(v=sql.105).aspx[^]).
Then try to use Database Engine Tuning Advisor to solve these issues.

upd.

In sys.sysprocesses we can see processes information, but we need a process or processes which are getting blocked

select * from sys.sysprocesses where  spid >= 50 and blocked <> 0

您可能还需要使用以下查询:

also you may need to use this query:

SELECT loginame, cpu, memusage, physical_io, spa.*
  FROM master..sysprocesses spa
 WHERE EXISTS( SELECT spb.*
                 FROM master..sysprocesses spb
                WHERE spb.blocked > 0 and
                      spb.blocked = spa.spid )
       AND NOT EXISTS ( SELECT spc.*
                          FROM master..sysprocesses spc
                         WHERE spc.blocked > 0 AND
                               spc.spid = spa.spid ) 
 ORDER BY spa.spid

该查询将为您提供有关您拥有多少个共享锁的信息

This query will give you information about how many shared locks you have

SELECT l.resource_type, l.request_mode, l.request_status, l.request_session_id
  FROM sys.dm_tran_locks AS l
 WHERE resource_database_id = DB_ID();

如果无法解决问题，请尝试使用本文提供的解决方案搜索锁: ^ ]

If in won''t help, try to search locks with solution from this article: http://www.techrepublic.com/blog/datacenter/find-blocking-processes-using-recursion-in-sql-server-2005/275[^]

这篇关于TSQL:是什么导致孤立的表锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！