October 2008 Archives

(SQL) Blocking Giving You the Blues?

October 5, 2008

Problem: You have a long-running query in SQL Server that is causing failures all over the place. Activity Monitor shows you the cause is blocking.

Causes: Blocking essentially means "you’ve locked a table, and now someone else is stuck waiting for you to unlock it." We all know (or should know) that transactions cause table locks, and thus blocking. That’s one reason that transactions need to be short and sweet. But there is another source of blocking, one that can be a bit more subtle: long-running queries that are trying to read committed data only.

READ COMMITTED is the default isolation level for SQL Server 2005 (and most other versions, I presume). Ben Gan describes: "In this isolation level, processes request a shared lock to read data and release it as soon as the data has been read – not when the transaction terminates." Thus if you have a poorly-performing SELECT query, it might take a while before the shared lock is released. In the meantime, everyone else is blocked.

About this Archive

This page is an archive of entries from October 2008 listed from newest to oldest.

September 2008 is the previous archive.

November 2008 is the next archive.

Find recent content on the main index or look in the archives to find all content.

Categories

Pages

OpenID accepted here Learn more about OpenID
Powered by Movable Type 5.12