![](https://img-blog.csdnimg.cn/20201014180756754.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
SQL BLOCK & LOCK
文章平均质量分 80
envykok
这个作者很懒,什么都没留下…
展开
-
blocking
-- Script returns blocking information from the sysprocesses table SELECT spid, blocked, status, waittime, waittype, waitresource, db_name(dbid) DatabaseName, cmd, hostname, loginameFR转载 2010-05-30 17:11:00 · 145 阅读 · 0 评论 -
Deadlock Troubleshooting (1)
http://blogs.msdn.com/b/bartd/archive/2006/09/09/747119.aspxA deadlock is a circular blocking chain, where two or more threads are each blocked by the other so that no one can proceed. Whenthe deadlock monitor thread in SQL Server detects a circular blo转载 2010-11-07 15:56:00 · 320 阅读 · 0 评论 -
Deadlock Troubleshooting - Trace 1222
<br />OPEN 1222 TRACE<br /> <br />DBCC TRACEON– Enable trace flags.<br />DBCC TRACEOFF- Disable trace flags.<br />DBCC TRACESTATUS- Display the status of trace flags.<br /> <br />–> Turn flag on for only the session level <br />DBCC TRACEON (1222)原创 2010-11-07 16:17:00 · 385 阅读 · 0 评论 -
Deadlock Troubleshooting (2)
http://blogs.msdn.com/b/bartd/archive/2006/09/13/751343.aspxIn this post I’ll look at an actual deadlock, then troubleshoot it using the steps I described in Deadlock Troubleshooting, Part 1 so you can see them in action. This is a simplified version of转载 2010-11-07 15:58:00 · 177 阅读 · 0 评论 -
Understanding SQL Server 2000 Locking
http://www.databasejournal.com/features/mssql/article.php/3289661/Understanding-SQL-Server-2000-Locking.htmIntroductionIn this article, I want to tell you about SQL Server 2000 lock modes.SQL Server 2000 supports the following lock modes:Shared (S) U转载 2010-11-04 15:58:00 · 244 阅读 · 0 评论 -
How can SELECT cause a deadlock
To summarize: the SELECT used the nonclusteredindex to find a qualifying row. While holding a Shared lockon the nonclustered index, it needs to jump over to the clustered indexand retrieve some columns that aren’t part of the nonclustered index.Wh原创 2010-11-04 15:03:00 · 194 阅读 · 0 评论 -
Reducing Deadlocking Problems
<br />http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/<br />Reducing Deadlocking Problems<br /><br />Many different things can contribute to the cause of a deadlock in SQL Server. Below are some转载 2010-11-03 23:26:00 · 198 阅读 · 0 评论 -
Troubleshooting Locking Facts
1. SP_WHO2sp_who2 returnsSPID --> which is the process under considerationBLK --> if it has a value represents the SPID of the blocking processEx. SPID =10 BLK=51 --> Process 10 is being blocked by process 51DBCC INPUTBUFFER(SPID) - tell us what is t原创 2010-11-04 11:00:00 · 180 阅读 · 0 评论 -
Diagnosing SQL Server performance issues using sp_lock
http://articles.techrepublic.com.com/5100-10878_11-6088055.htmlA common misconception among IT pros is that "locks are bad," and you must do everything possible to ensure database locking doesn't prevent processes from running. In order to ensure a consist转载 2010-10-31 23:17:00 · 161 阅读 · 0 评论 -
Preventing Lock Escalations Caused by UPDATE operations
<br />http://blogs.microsoft.co.il/blogs/srldba/archive/2008/11/10/preventing-lock-escalations-caused-by-update-operations.aspx<br />http://support.microsoft.com/kb/323630Preventing Lock Escalations Caused by UPDATE operations<br /><br />Yoni Okun<br />Loc转载 2010-10-29 00:38:00 · 177 阅读 · 0 评论 -
Block - Issue Resolution
<br /> <br />-- Block by user create temp table<br />If you have to use a temp table, do not create it from within a transaction. If you do, then it will lock some system tables (syscolumns, sysindexes, and syscomments) and prevent others from executing th原创 2010-10-29 00:25:00 · 167 阅读 · 0 评论 -
LOCK Comparizon : NOLOCK vs. READPAST vs. ROWLOCK vs. UPLOCK vs. HOLDLOCK
<br />1. TABLOCK VS. HOLDLOCK<br /> SELECT*FROMtableWITH (HOLDLOCK) 其他事务可以读取表,但不能更新删除 <br /><br />SELECT*FROMtableWITH (TABLOCKX) 其他事务不能读取表,更新和删除<br /><br />2. NOLOCK VS. UPLOCK <br />http://www.mssqltips.com/tip.asp?tip=1257<br />The UPDLOCK hint tells原创 2010-10-24 22:25:00 · 457 阅读 · 0 评论 -
How to Track Down Deadlocks Using SQL Server 2005 Profiler
A Quick Introduction to Deadlocks<br />http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/<br />Deadlockingoccurs when two or more SQL Server processes have locks on separatedatabase objects and e转载 2010-10-20 22:20:00 · 346 阅读 · 0 评论 -
Using the Blocked Process Report in SQL Server 2005/2008
<br />http://www.sqlservercentral.com/articles/Blocking/64474/<br />Locking is a familiar concept to anyone working with SQL Server.However, when a process holds locks for a long period of time, otherprocesses can begin to be blocked by the initial pro转载 2010-10-20 22:07:00 · 226 阅读 · 0 评论 -
Block - waitresource
Waitresourcehttp://support.microsoft.com/kb/224453This field indicates the resource that a SPID is waiting on. The following table lists common waitresource formats and their meaning:Collapse this tableExpand this tableResourceFormatExam转载 2010-10-20 16:54:00 · 293 阅读 · 0 评论