Click here to Skip to main content
15,868,141 members
Articles / Database Development / SQL Server

Speeding up database access - Part 5: Fixing locking issues

Rate me:
Please Sign up or sign in to vote.
4.77/5 (9 votes)
18 Dec 2011CPOL7 min read 29.2K   32   1
Looks at fixing any locking issues you found in Part 2. You'll see how to determine which queries are involved in excessive locking delays, and how to prevent those delays from happening.

Image 1

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.

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:

SQL
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:
  7. SQL
    DBCC TRACEON(1222,-1)
  8. Uncheck all the other events, unless you are interested in them.
  9. Click Run to start the trace.
  10. 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.
  11. 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.

Image 2

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. Image 3

  3. 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:

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:

Image 4

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:

SQL
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.

SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Australia Australia
Twitter: @MattPerdeck
LinkedIn: au.linkedin.com/in/mattperdeck
Current project: JSNLog JavaScript Logging Package

Matt has over 9 years .NET and SQL Server development experience. Before getting into .Net, he worked on a number of systems, ranging from the largest ATM network in The Netherlands to embedded software in advanced Wide Area Networks and the largest ticketing web site in Australia. He has lived and worked in Australia, The Netherlands, Slovakia and Thailand.

He is the author of the book ASP.NET Performance Secrets (www.amazon.com/ASP-NET-Site-Performance-Secrets-Perdeck/dp/1849690685) in which he shows in clear and practical terms how to quickly find the biggest bottlenecks holding back the performance of your web site, and how to then remove those bottlenecks. The book deals with all environments affecting a web site - the web server, the database server and the browser.

Matt currently lives in Sydney, Australia. He recently worked at Readify and the global professional services company PwC. He now works at SP Health, a global provider of weight loss web sites such at CSIRO's TotalWellBeingDiet.com and BiggestLoserClub.com.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Pablo Aliskevicius6-Dec-11 3:39
Pablo Aliskevicius6-Dec-11 3:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.