Troubleshooting locking conflicts in SQL Server

Troubleshooting locking problems is one of those activities that DBAs would much rather avoid, but most have to it do at one point or another. In fact, understanding database transactions and locking is what differentiates many beginners from the real pros.

So what is locking? As a fine Database Management System (DBMS), SQL Server needs to have a way to protect data integrity. In read-only databases, things are very simple: you populate your tables through regularly scheduled (perhaps weekly or nightly) loads and the users run SELECT statements against those tables. Since users are not allowed to make changes to the data -- no locks are acquired and everyone is happy. However, you've got to collect data prior to making it available for reading. The majority of your systems will be used for reading and modifying data at the same time.

When someone tries to modify a row of data in your table, SQL Server locks that record, so that no one else can modify the same record until the first data modification is complete. What if two users want to concurrently read the same record? Can one user change the record while another one is modifying the same record? The answer depends on how you configure your database settings.

By default, SQL Server runs under the TRANSACTION ISOLATION LEVEL of "READ COMMITED." This setting disallows reading of the record that is being modified. So if you have a batch job that modifies all records in a table, the rest of the database users will have to wait until your transaction is complete prior to being able to read any records in that table. This is accomplished by placing UPDATE locks on the rows being modified. In SQL Server 2000 there are several different types of locks that SQL Server can acquire, as summarized in the following table:

If two lock types are incompatible with one another, one of the unlucky users will have to wait until the other transaction has completed its work. This event is referred to as blocking. Keep in mind that some blocking (that does not last very long) is normal and to be expected in transactional applications.

You can change the default TRANSACTION ISOLATION level with the one that most fits your needs. The less restrictive isolation levels allow reading of the data while it is being modified, which incidentally reduces the integrity of your data. Some of your users might see the values that have been changed. Some other users might see the changed values that haven't been committed to the disk yet. Therefore, be aware of the consequences of changing the isolation level and design your application accordingly.

If you have a multi-user transactional application blocking locks can get you in trouble quickly. While your app might perform fine with 10 users, when the number of concurrent users goes up to 50 your phone will be ringing off the hook. "I hit the CREATE RECORD button and my screen has been frozen ever since" will be the most frequently mentioned complaint. Some other users will complain that they tried connecting to the application several times to no avail. So what's the deal? How can we find out?

Fortunately, you can run a couple of system procedures to troubleshoot locking problems. The sp_who2 procedure shows you all current connections to your SQL Server as well as any problems with conflicting locks. This system procedure also provides the connection id (SPID), as well as the computer that initiated the connection. Usually the blocking locks have a chain effect -- if connection 3 is blocked by connection 2 and connection 4 requests the same resource, connection 4 will be blocked by 3. Unfortunately sp_who2 doesn't tell you the complete SQL statement executed by the offending connection, but it does tell you the type of operation performed. If you have system administrator privileges you can execute DBCC INPUTBUFFER (SPID) statement to get the last 150 characters of the SQL statement executed by the provided connection. The combination of sp_who2 and DBCC INPUTBUFFER statements will usually paint a very plain picture.

Occasionally the two ideas mentioned in the previous paragraph won't suffice. The problem is that if a SQL statement was executed as a remote procedure call DBCC INPUTBUFFER won't give you many details. It will simply show "RPC" in the output. In such case you have to use SQL Server Profiler to spy on the offending connection.

The Profiler has come long ways from its predecessor SQL Trace. It really is much easier to use and manage. However, be careful with this tool. As most other diagnostic tools, the Profiler has a tendency to track a lot of information by default -- much more than you'd want to decipher. And don't try to examine a day's worth of Profiler's output -- it'll be way to huge for most applications. Usually a trace file of an hour or two (during the peak hours) will do the job. If you have a locking problem while creating a trace file you can quickly find the SQL statement executed by the offending connection.

So far I've shown you how to identify the root and the cause of the locking problems. What happens next? The locking problems are often caused by the absence of appropriate indexes. If a transaction has to scan through a table with millions of rows it will be long and therefore will have to keep the locks on that table for a while. On the other hand, the transactions seeking particular values by taking advantage of indexes will be short and quick. The key to a successful application is keeping your transactions as short as possible. Sometimes this might mean rewriting the stored procedures and queries hitting your tables, but more often the key is placing appropriate indexes on each table.

In some systems the number of transactions might be too high to handle with a single server. In such case you should consider moving read operations (the reporting functionality) to a separate server. Keep in mind though that having two servers in place will require synchronizing data on two servers in some fashion. There are numerous ways of transferring data from one server to another, DTS and replication being the most effective and efficient. Be sure to consider pros and cons of each method of transferring data prior to making a decision.

So in this article I gave you a brief overview of potential locking problems, what causes them, how to detect and resolve them. Each database has its intricacies that cannot be predicted. But with the appropriate planning and design you can avoid most of the blocking issues. Using the powerful system procedures and diagnostic tools can help you troubleshoot the rest.

Reader Feedback

Heinz-Wilhelm F. writes: Why not use Oracle instead (any version from Oracle version 6 -- released in 1989 -- to the current version Oracle9i database) and stop worrying about locking issues any longer?

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free SQL Server tips and scripts.
• Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
• Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.