INF: Understanding and Resolving SQL Server Blocking Problems

• Microsoft SQL Server, versions 6.0 and 6.5

SUMMARY

Microsoft SQL Server is a transaction-oriented relational database management system (RDBMS) that is often used in highly concurrent environments with many simultaneous users. It maintains transactional integrity and database consistency by using locks at the table and page levels (and at the row level with SQL Server version 6.5) for INSERTs.

An unavoidable characteristic of any lock-based concurrent system is that blocking may occur under some conditions. Blocking happens when one connection holds a lock and a second connection wants a conflicting lock type. This forces the second connection to either wait or block on the first. This article discusses why this happens, and techniques for understanding, resolving, and preventing blocking problems.

In this discussion the term "connection" refers to a single logged-on session of the database. Each connection appear as a Server Process ID (SPID). Each of these SPIDs is often referred to as a "process," although it is not a separate process context in the normal sense. Rather, each SPID consists of the server-side resources and data structures necessary to service the requests of a single connection from a given client. A single client application may have one or more connections. From the perspective of SQL Server, there is no difference between multiple connections from a single client application on a single client computer and multiple connections from multiple client applications or multiple client computers. One connection can block another connection, regardless of whether they emanate from the same application or separate applications on two different client computers.

MORE INFORMATION

OVERVIEW

To achieve consistent results during concurrent update activity, any database must impose isolation between transactions. Without isolation, inconsistent results may occur under concurrent use. Most database products use locks to impose isolation. There are isolation techniques other than locks, such as versioning (also called time domain addressing), but each technique has its own cost and overhead. For details, see "Transaction Processing: Concepts and Techniques," by Jim Gray and Andreas Reuter, ISBN 1-55860-190-2. There is no "free lunch" in implementing isolation. Locking is well understood and highly refined in current products, so this article focuses on locking. All parts of the transaction must be protected with locks, or else a ROLLBACK would not be reliable. Some of the actions needing locking protection may not be obvious. These include locks on system catalog tables, indexes, and allocation structures such as extents.

Locks are automatically acquired by the database server in response to certain types of queries. Locks are not typically acquired under manual programmatic control (although the current product allows this through optimizer hints).

Locks are not generally intended for use under manual control to implement pessimistic concurrency. For example, one connection should not manually acquire a lock in order to indicate to other connections that the page or row is "in use." Rather, the application should use an "in use" column as a flag to other connections. Alternatively, the application could use a cursor under optimistic concurrency control, which would then signal the application if another connection changed the data during the interval the first connection was browsing the data. For more details on implementing cursors, see the SQL Server 6.5 documentation and the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q156489
   TITLE     : Overview of SQL Server, ODBC, and DB-Library Cursors

Different RDBMSs may handle locking and concurrency differently. If the application is being ported from one RDBMS to another, you cannot expect the two databases to behave identically. The application may require adjustments, especially if the application takes advantage of a feature or characteristic unique to one RDBMS. This is so even if the application uses only standard ANSI SQL, because locking and concurrency control issues are implementation-specific.

The RDBMS designers must balance a tradeoff between locking granularity and overhead. Fine-grain locks at the row or column level can allow more concurrency, but they also entail greater overhead. This relationship between locking granularity and overhead has been well understood for many years in the academic community. See "Effects of Locking Granularity in a Database Management System", by Daniel Ries and Michael Stonebraker from ACM Transactions on Database Systems, September 1977, and "Locking Granularity Revisited", same authors and publication, June 1979. Microsoft SQL Server locks at the table, page, or (with version 6.5) at the row level for INSERTs. Future versions of SQL Server will have more extensive row level locking, but this will not prevent blocking problems.

No matter how fine-grained the locks taken, if the application does not exercise good discipline regarding transaction path length and query management, blocking problems may occur. This is similar to a slow moving automobile causing a backup by driving in the left lane of a freeway. The driver may think that sufficient lanes should be made available for him or her to drive any way he or she wants. However, no feasible number of highway lanes will allow drivers to disregard good lane discipline without causing a traffic slowdown. Likewise, no RDBMS isolation technique can allow applications to disregard the impact they have on concurrency and scalability.

INVESTIGATING A BLOCKING PROBLEM

1. Identify the SPID at the head of the blocking chain.

   Most blocking problems happen because a single process holds locks for an extended period of time. This usually causes a chain of blocked processes, similar to a slow-moving automobile that causes a backup on the freeway. Identify the head of the blocking chain by using the SQL Enterprise Manager command Server/CurrentActivity and observing the "Object Locks" tab.

   Alternatively, you can use the following query, which should return one row for each SPID at the head of a blocking chain, plus the query the blocking spid is running, and the types of blocking locks it holds. We suggest you do not run multiple concurrent instances of this query unless you are on SQL Server versions later than version 6.5 Service Pack 3. This query is only an example; you may want to modify it or use your own query. Also see the following article in the Microsoft Knowledge Base for more information:

         ARTICLE-ID: Q122485
         TITLE     : Identifying SPID Responsible for Lock Chain
   
   
   /* Query to find spids at head of a blocking chain, their input buffers, */
   /* and the type of blocking locks they hold */
   
   

/* Get all blocked spids */

 status, physical_io, waittype

/* delete all blocking spids except the 1st in each blocking chain */

/* get each spid from sysprocesses which is referenced in */
/* the "blocked" column of #blk. This should be the head */
/* of each blocking chain */

 hostname=substring (hostname, 1, 10), progname=substring(program_name, 1,

 cmd=substring(cmd, 1, 10), status, physical_io, waittype

/* For each spid at the head of a blocking chain */
/* print its input buffer to show what query it's running */

  begin
  select @i_buff_string = ("dbcc inputbuffer (" +

  select "Below is input buffer for this blocking spid: ", @blocker_spid
  select ""
  exec (@i_buff_string)
  fetch next from blk_cursor into @blocker_spid
  end

/* For each spid at the head of a blocking chain */
/* print the type of blocking locks it holds */

drop table #blk

1. Find the query the blocking SPID is running.

   You can do this by running the above query, or by doing DBCC INPUTBUFFER (spid), where spid is the blocking SPID. or by using the Server/CurrentActivity function in SQL Enterprise Manager and double-clicking the SPID to show the input buffer. Save this information for future reference.

2. Find the type of locks the blocking SPID is holding.

   You can do this by running the above query, or by either running sp_lock or querying master..syslocks. Save this information for future reference.

   Alternatively, you can use the Server/CurrentActivity function in SQL Enterprise Manager. However, it is sometimes necessary to use queries instead of Enterprise Manager, because some types of tempdb blocking problems may prevent you from running queries that use temp table operations. Using direct queries gives you the control necessary to avoid this problem. An example of a query that results in temp table operations (and therefore may not be usable in certain blocking situations) is sp_lock, which does an ORDER BY.

3. Find the transaction nesting level and process status of the blocking SPID.

   This is essentially the same number as @@TRANCOUNT, but it can be determined from outside the SPID by using the DBCC PSS command. Save this information for future reference. The following shows an example of the syntax:

         dbcc traceon(3604) /* return subsequent DBCC output to client rather
            than errorlog */
         go
         SELECT SUID FROM SYSPROCESSES WHERE SPID=<blocking SPID number>
         go
         DBCC PSS (suid, spid, 0) /* where suid is from above, and spid is the
         blocking SPID number */
         go
   
      In the returned information, note pxcb->xcb_xactcnt=n, where n is the
      @@TRANCOUNT value for the SPID. This shows the transaction nesting level
      for the blocking SPID, which in turn can explain why it is holding
      locks. For example, if the value is greater than zero, the SPID is in
      the midst of a transaction (in which case it is normal that it retain
      any exclusive locks it acquired). Note also pstat=n, where n indicates
      the internal status of the SPID. This can indicate why the blocking SPID
      may itself be waiting for certain events.
   
      You can also check to see if any long-term open transaction exists in
      the database by using DBCC OPENTRAN(database_name).
   
   

   ARTICLE-ID: Q125770
   TITLE     : Locking Behavior of Updates and Deletes in SQL Server

IDENTIFYING AND RESOLVING COMMON BLOCKING SCENARIOS

I. Blocking Caused by a Normally Running Query with a Long Execution Time

A long-running query can block other queries. For example, a bulk DELETE or UPDATE can acquire many locks that (whether they escalate to a table lock or not) block other queries. For this reason, you generally do not want to intermix long-running decision support queries and OLTP queries on the same database. You can identify this situation by observing the blocking SPID. The INPUTBUFFER may point to a query known to have a long execution time. This will often cause a steady I/O consumption, visible by running the following query several times in succession:

   SELECT SPID, PHYSICAL_IO FROM MASTER..SYSPROCESSES

This class of blocking problem may just be a performance problem, and may require you to pursue it as such. For more information see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q110352
   TITLE     : Optimizing Microsoft SQL Server Performance

II. Blocking Caused by a Sleeping SPID That Has Lost Track of the

    Transaction Nesting Level

   ARTICLE-ID: Q117143
   TITLE     : When and How to Use dbcancel() or sqlcancel()

III. Blocking Caused by a SPID Whose Corresponding Client Application

     Did Not Fetch All Result Rows to Completion

You can often identify this problem by the following behavior:

• The blocking SPID continuously has a waittype of 0x800.
• The status may indicate runnable.
• The cmd may be SELECT.
• Transaction nesting level is zero. (If you cannot examine @@TRANCOUNT within the application, you can examine it externally by using the DBCC PSS command.

Unlike a conventional deadlock, a distributed deadlock is not detectable using the RDBMS lock manager. The blocking SPID will often appear sleeping, with a waittype of 0x800 (waiting on a network I/0). A SPID in this state cannot be KILLed, as it is waiting on a return from a Windows NT Server API call. A distributed client/server deadlock may occur if the application opens more than one connection to the RDBMS and submits a query asynchronously. The following are two examples of how this can happen, and possible ways the application can avoid it.

Example A: Client/Server Distributed Deadlock with a Single Client Thread

If the client has multiple open connections (dbprocs in DB-Library terms), and a single thread of execution, the following distributed deadlock may occur. For brevity, the term dbproc refers to the client connection structure. In ODBC API terms, the closest analogy is an hdbc.

NOTE: Used a fixed font for the information below to display correctly.

 SPID1------blocked on lock------->SPID2
  /\                         (waiting on net write)        Server side
  |                          (sysprocesses.waittype==0x800)
  |                                 |
  |                                 |
  | ================================|==================================
  |     <-- single thread -->       |                      Client side
  |                                 \/
 dbproc1   <-------------------   dbproc2
 (waiting on dbnextrow            (effectively blocked on dbproc1, awaiting
  or SQLFetch)                     single thread of execution to run)

Example B: Client/Server Distributed Deadlock with a Thread per Connection

Even if a separate thread exists for each connection on the client, a variation of this distributed deadlock may still occur, as shown by the following:

NOTE: Use a fixed font for the information below to display correctly.

SPID1------blocked on lock-------->SPID2

  /\                         (waiting on net write)        Server side
  |                          (sysprocesses.waittype==0x800)
  |                                 |
  | INSERT                          |SELECT
  | ================================|==================================
  |     <-- thread per dbproc -->   |                      Client side
  |                                 \/
 dbproc1   <-----data row-------   dbproc2
 (waiting on dbnextrow           (blocked on dbproc1, waiting for it
  or SQLFetch)                    to read the row from its buffer)

Both examples A and B are fundamental issues that application developers must be aware of. They must code applications to handle these cases appropriately. Two reliable solutions are to use either a query timeout or bound connections.

Query Timeout: When a query timeout has been provided, if the distributed deadlock occurs, it will be broken when then timeout happens. See the DB- Library or ODBC documentation for more information on using a query timeout.

Bound Connections: This is a feature new to SQL Server 6.5. It allows a client having multiple connections to bind them into a single transaction space, so the connections don't block each other. See the SQL Server 6.5 documentation under "bound connections" for more information.

V. Blocking Caused by a SPID That Is in a "Golden," or Rollback, State

A data modification query that is KILLed, or canceled outside of a user- defined transaction will be rolled back. This can also occur as a side effect of the client computer restarting and its network session disconnecting. Likewise, a query selected as the deadlock victim will be rolled back. A data modification query often cannot be rolled back any faster than the changes were initially applied. In the case of a DELETE, INSERT, or UPDATE that had been running for an hour, it could take at least an hour to roll back. If the server is shut down in the midst of this operation, the database will be in recovery mode upon restarting, and it will be inaccessible until all open transactions are processed. Startup recovery takes essentially the same amount of time per transaction as run- time recovery, and the database is inaccessible during this period. Thus, forcing the server down to fix a SPID in a rollback state will often be counterproductive. This is expected behavior, because the changes made must be completely rolled back, or transactional and physical integrity in the database would be compromised. Because this must happen, SQL Server marks the SPID in a "golden" or rollback state (which means it cannot be KILLed or selected as a deadlock victim). This can often be identified by observing the sp_who output, which may indicate the ROLLBACK command. On version 6.5 Service Pack 2 or later, a ROLLBACK status has been added to sysprocesses.status, which will also appear in sp_who output or the SQL Enterprise Manager "current activity" screen. However, the most reliable way to get this information is to inspect the DBCC PSS of the blocking SPID in question, and observing the pstat value. For example, it may be something like the following:

   pstat=0x4000, 0x800, 0x100, 0x1

0x4000 -- Delay KILL and ATTENTION signals if inside a critical section 0x2000 -- Process is being killed 0x800 -- Process is in backout, thus cannot be chosen as deadlock victim 0x400 -- Process has received an ATTENTION signal, and has responded by

          raising an internal exception

The pstat value above would be a typical situation if a long-running data modification was canceled (for example, by clicking the Cancel Query button on a GUI application), and then the SPID was found to block users and yet be unkillable. This situation is normal; the transaction must be backed out. It can be identified by the bits, as noted above.

In addition to the pstat field of the PSS, the sysprocesses.waittype field can also give information about why the SPID may be waiting. The following are some common values:

0x800 -- Waiting on network I/O completion 0x8011 -- Waiting on buffer resource lock (shared) request 0x81 -- Waiting on writelog 0x0020 -- Waiting on buffer in I/O 0x0005 -- Waiting on exclusive page lock 0x13 -- Waiting on buffer resource lock (exclusive) request 0x8001 -- Waiting on exclusive table lock 0x8007 -- Waiting on update page lock 0x8005 -- Waiting on exclusive page lock 0x8003 -- Waiting on exclusive intent lock 0x6 -- Waiting on shared page lock 0x8006 -- Waiting on shared page lock 0x23 -- Waiting on buffer being dumped 0x5 -- Waiting on exclusive page lock 0x0013 -- Waiting on buffer resource lock (exclusive) request 0x0022 -- Waiting on buffer being dirtied

VI. Blocking Caused by SQL Server 6.5 Atomic SELECT INTO Behavior

By definition, SQL Server treats each statement as a separate transaction. Beginning with SQL Server version 6.5, SELECT INTO was made consistent with this standard by including the table creation and data insert phases in a single atomic operation. A side effect of this is that locks on system catalog tables are maintained for the duration of a SELECT INTO statement. This is more frequently seen in tempdb, because applications often do SELECT INTO temporary tables. Blocking caused by this action can be identified by examining the locks held by the blocking SPID. The atomic SELECT INTO behavior can be disabled with trace flag 5302. For more information, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q153441
   TITLE     : SELECT INTO Locking Behavior

If the client application traps or the client workstation is restarted, the network session to the server may not be immediately canceled under some conditions. From the server's perspective, the client still appears to be present, and any locks acquired may still be retained. For more information, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q137983
   TITLE     : How to Troubleshoot Orphaned Connections in SQL Server

APPLICATION INVOLVEMENT IN BLOCKING PROBLEMS

SQL Server is essentially a puppet of the client application. The client application has almost total control over (and responsibility for) the locks acquired on the server. While the SQL Server lock manager automatically uses locks to protect transactions, this is directly instigated by the query type sent from the client application, and the way the results are processed. Therefore, resolution of most blocking problems necessitates inspecting the client application.

Often, turnkey client applications are used against SQL Server, in addition to higher-level application development tools. These may encapsulate the DB- Library or ODBC API calls to the database in a higher abstraction level. However, from the perspective of SQL Server, there is essentially no difference between one of these higher level applications, a call-level DB- Library application, and a call-level ODBC application. SQL Server only perceives a stream of Transact-SQL queries and certain control tokens sent by each client API call. The same basic issues will cause blocking problems whether the client application is a call-level application written in C or a higher level application that encapsulates the database calls. Likewise, the solution to these problems is generally the same.

This means that no matter what level of visibility the application exposes regarding the database calls being made, a blocking problem nonetheless frequently requires both the inspection of the exact SQL statements submitted by the application and the application's exact behavior regarding query cancellation, connection management, fetching all result rows, and so on. If the development tool does not allow explicit control over connection management, query cancellation, query timeout, result fetching, and so on, blocking problems may not be resolvable. This potential should be closely examined before selecting an application development tool for SQL Server, especially for business-critical OLTP environments.

There may be a tendency to focus on server-side tuning and platform issues when facing a blocking problem. However, this does not usually lead to a resolution, and can absorb time and energy better directed at examining the client application and the queries it submits.

Several tracing utilities can be used to examine exactly what the client application is sending to the server. Each client application may have its own tracing feature, so examine the documentation for the application. In addition, server-side tracing can be done using the -T4032 trace flag and the SQL Trace utility. You can usually resolve a blocking problem without using these utilities, but they are available if needed.

APPLICATION DESIGN TECHNIQUES TO AVOID A BLOCKING PROBLEM

DO NOT:

• Fail to immediately fetch all result rows to completion.
• Design a large-scale OLTP system using an application development tool that does not allow explicit control over connections, transactions, and the SQL syntax sent to the server.
• Use or design a client application that allows users to fill in edit boxes that generate a long-running query. For example, do not use or design an application that prompts the user for inputs, but allows leaving certain fields blank or entering a wildcard. This may cause the application to submit a query with an excessive running time, thereby causing a blocking problem.
• Use or design an application that allows user input within a transaction.

• Allow for query cancellation by means of dbcancel(), sqlcancel(), or an equivalent command.
• Use a query timeout by means of dbsetltime() or SQLSetStmtOption(), to prevent a runaway query and avoid distributed deadlocks.
• Immediately fetch all result rows to completion.
• Keep transactions as short as possible.
• Explicitly control connection management.
• Stress test the application at the full projected concurrent user load.

With proper application and query design, Microsoft SQL Server is capable of supporting many thousands of simultaneous users on a single server, with little blocking. The successful sites that reach this level typically use the techniques described in this article.

Keywords          : SSrvGen kbprg kbusage
Version           : Windows:6.0,6.5
Platform          : WINDOWS
Issue type        : kbhowto