Speeding up database access - Part 5: Fixing locking issues

This is Part 5 of an 8 Part series of articles about speeding up access to a SQL Server database. This series is based on chapter 8 "Speeding up Database Access" of my book ASP.NET Site Performance Secrets, available at amazon.com and other book sites.

In Part 2 we saw how to pinpoint bottlenecks that are due to locking. In this Part 5, we'll look at fixing those locking issues. You'll see how to determine which queries are involved in excessive locking delays, and how to prevent those delays from happening.

• Part 1 Pinpointing missing indexes and expensive queries
• Part 2 Pinpointing other bottlenecks
• Part 3 Fixing missing indexes
• Part 4 Fixing expensive queries
• Part 5 Fixing locking issues
• Part 6 Fixing execution plan reuse
• Part 7 Fixing fragmentation
• Part 8 Fixing memory, disk, and CPU issues

If you like this article, please vote for it.

Gather Detailed Locking Information

You can find out which queries are involved in excessive locking delays by tracing the event "Blocked process report" in SQL Server Profiler.

This event fires when the lock wait time for a query exceeds the "blocked process threshold". To set this threshold to for example 30 seconds, run the following lines in a query window in SSMS:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'blocked process threshold', 30
RECONFIGURE

Then start the trace in Profiler:

1. Start SQL Profiler. Click Start | Programs | Microsoft SQL Server 2008 | Performance Tools | SQL Server Profiler.
2. In SQL Profiler, click File | New Trace.
3. Click the Events Selection tab.
4. Select Show all events checkbox to see all events. Also select Show all columns to see all the data columns.
5. In the main window, expand Errors and Warnings and select the Blocked process report event. Make sure the checkbox in the TextData column is checked - scroll horizontally if needed to find it.
6. If you need to investigate deadlocks, also expand Locks and select the Deadlock graph event. To get additional information about deadlocks, have SQL Server write information about each deadlock event to its error log, by executing this from a SSMS query window:
SQL

DBCC TRACEON(1222,-1)

7. Uncheck all the other events, unless you are interested in them.
8. Click Run to start the trace.
9. Save the template so you don't have to recreate it next time. Click File | Save As | Trace Template. Fill in a descriptive name and click OK. Next time you create a new trace by clicking File | New Trace, you can retrieve the template from the Use the template dropdown.
10. Once you have captured a representative sample, click File | Save to save the trace to a trace file for later analysis. You can load a trace file by clicking File | Open.

When you click a Blocked process report event in Profiler, you'll find in the lower pane information about the event, including the blocking query and the blocked query. You can get details about Deadlock graph events the same way.

To check the SQL Server error log for deadlock events:

1. In SSMS, expand the database server, expand Management, expand SQL Server Logs. Then double click a log.



2. In the Log File Viewer, click Search near the top of the window and search for "deadlock-list". In the lines that chronologically come after the deadlock-list event, you'll find much more information about the queries involved in the deadlock.

Reduce Blocking

Now that you have identified the queries involved in locking delays, it's time to reduce those delays. The most effective way to do this is to reduce the length of time locks are held:

• Optimize queries. The less time your queries take, the less time they hold locks. See Part 1: "Pinpointing missing indexes and expensive queries".
• Use Stored Procedures rather than ad hoc queries. This reduces time spent compiling execution plans and time spent sending individual queries over the network. Part 6 "Fixing execution plan reuse" shows how to introduce Stored Procedures.
• If you really have to use cursors, commit updates frequently. Cursor processing is much slower than set based processing.
• Do not process lengthy operations while locks are held, such as sending emails. Do not wait for user input while keeping a transaction open. Instead, use optimistic locking, as described in:
• Optimistic Locking in SQL Server using the ROWVERSION Data Type

A second way to reduce lock wait times is to reduce the number of resources being locked:

• Do not put a clustered index on frequently updated columns. This requires a lock on both the clustered index and all non-clustered indexes, because their row locator contains the value you are updating.
• Consider including a column in a non-clustered index. This would prevent a query from having to read the table record, so it won't block another query that needs to update an unrelated column in the same record.
• Consider row versioning. This SQL Server feature prevents queries that read a table row from blocking queries that update the same row and vice versa. Queries that need to update the same row still block each other.

Read versioning works by storing rows in a temporary area (in tempdb) before they are updated, so reading queries can access the stored version while the update is taking place. This does create overhead in maintaining the row versions - test this solution before taking it live. Also, in case you set the isolation level of transactions, row versioning only works with the Read Committed isolation mode - which is the default isolation mode.

To implement row versioning, set the READ_COMMITTED_SNAPSHOT option as shown in the code below. When doing this, you can have only one connection open - the one used to set the option. You can make that happen by switching the database to single user mode. Warn your users first. Be careful when applying this to a production database, because your web site won't be able to connect to the database while you are carrying out this operation.

SQL

ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE mydatabase SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE mydatabase SET MULTI_USER;

To check whether row versioning is in use for a database, run:

SQL

select is_read_committed_snapshot_on
from sys.databases
where name=' mydatabase '

Finally, you can set a lock timeout. For example, to abort statements that have been waiting for over 5 seconds (or 5000 milliseconds), issue the command:

SQL

SET LOCK_TIMEOUT 5000

Use -1 to wait indefinitely. Use 0 to not wait at all.

Reducing Deadlocks

Deadlock is a situation where two transactions are waiting for each other to release a lock. In a typical case, transaction 1 has a lock on resource A and is trying to get a lock on resource B, while transaction 2 has a lock on resource B and is trying to get a lock A. Neither transaction can now move forward, as shown below:

One way to reduce deadlocks is to reduce lock delays in general, as shown in the last section. That reduces the time window in which deadlocks can occur.

A second way is suggested by the diagram - always lock resources in the same order. If in the diagram you get transaction 2 to lock the resources in the same order as transaction 1 (first A, then B), then transaction 2 won't lock resource B before it starts waiting for resource A, and so doesn't block transaction 1.

Finally, watch out for deadlocks caused by the use of HOLDLOCK or Repeatable Read or Serializable Read isolation levels. Take for example this code:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
    SELECT Title FROM dbo.Book
    UPDATE dbo.Book SET Author='Charles Dickens'
    WHERE Title='Oliver Twist'
COMMIT

Imagine two transactions running this code at the same time. Both acquire a Select lock on the rows in the Book table when they execute the SELECT. They hold onto the lock because of the Repeatable Read isolation level. Now both try to acquire an Update lock on a row in the Book table to execute the UPDATE. Each transaction is now blocked by the Select lock the other transaction is still holding.

To prevent this from happening, use the UPDLOCK hint on the SELECT statement. This causes the SELECT to acquire an Update lock, so only one transaction can execute the SELECT. The transaction that did get the lock can then execute its UPDATE and free the locks, after which the other transaction comes through.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
    SELECT Title FROM dbo.Book WITH(UPDLOCK)
    UPDATE dbo.Book SET Author='Charles Dickens' WHERE Title='Oliver Twist'
COMMIT

Conclusion

In this part, we saw how to reduce locking delays, by reducing the time locks are held and by reducing the number of resources being locked. We also looked at deadlocks.

In the next part, we'll see how to optimize execution plan reuse.