Home / server / Questions / 319373
In Process
nat
Asked: 2011-10-08 00:32:57 +0800 CST

sql deadlocking and timing out almost constantly

  • 772

Looks like today is going to be another rubbish one. We have recently updated our sql box with a complete monster, with loads of cores and ram, however we are stuck with our old DB schema which is crapola.

Our old sql box had problems but nothing like what we are experiencing with the new one, although on the day of rolling out it was running super fast, within a week it's a complete mess...

Our .net app used by a couple of hundred people or so is generating a huge amount of deadlocks and timeouts on the SQL box and we are struggling to work out why. We have checked all the indexes and they are as good as they can be right now. Some of the major tables are way too wide and have a stupid amount of triggers on, but there is nothing we can do about this now.

A lot of the pids seem to be the same for the same users who are trying multiple times.

So for instance...

User: user1
Time: 09:21
Error Message: Transaction (Process ID 76) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

User: user1
Time: 09:22
Error Message: Transaction (Process ID 76) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

etc....

When we moved the db to the new box it was backed up from the old and restored to the new.

If anyone has any suggestions as to something we can do I will buy them multiple pints.

sql-server timeout deadlock

4 Answers

  1. This is really more of a development issue. You may want to consult with your developers to determine what transaction isolation level that are in use.

    The Microsoft SQL Server default isolation level is Read Committed. The developer should know and set the appropriate level for the transaction. It is generally advisable to use the least-restrictive isolation level possible, and avoid using the Repeatable Read and Serializable isolation levels if possible.

    If they are using a more restrictive isolation level than the default, such as Repeatable Read or Serializable, then the application will be more predisposed to locking issues. If they are using a more restrictive isolation level than the default, and are not aware that they are doing so, it is even worse.

    Microsoft's premier data access technology, Entity Framework, uses the Serializable isolation level by default. This is not very well documented or disclosed. If the application uses Entity Framework and the developer is unaware of this fact, the developer may want to review the database design to determine if they can set the transaction isolation level to Read Committed.

    More information:

    SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms173763.aspx

    Transactions and Connections in Entity Framework 4.0
    http://blogs.u2u.be/diederik/post/2010/06/29/Transactions-and-Connections-in-Entity-Framework-40.aspx

    • 2

  2. I am not into beer, but you should seriously look at the SQL Server profiler - it is a valuable tool for analyzing your workload, including some tools to help work out the reasons for deadlock conditions.

    There are numerous articles written on this topic, this one is sufficiently comprehensive. The official documentation at MSDN also contains some information, although not written up as nicely.

    • 1

  3. If you have the proper indices there's no good way to resolve this without fixing the database schema and/or queries: in particular, testing with SNAPSHOT ISOLATION and READ COMMITTED SNAPSHOT. They are not quick fixes.

    If you don't mind turning the new beast back into a slow porker, you can disable parallelism. It's uncertain how much it will help.

    Ultimately frequent deadlocks are a result of inadequate database design, and there's no way around that.

    • 0

  4. Have you at least been able to record the queries causing the problems? My experience has been very few queries are responsible for the vast majority of issues.

    From the error messages it looks like you are running SQL Server. If you are running 2005 or better turn on Trace Flag 1222 DBCC TRACEON (1222, -1) it should give you some info about the queries. A crappy schema may cause problems, but I have never seen a crappy schema directly cause deadlocks. Usually there is a workaround. A slow query is way better than a query causing constant deadlocks.

    Get some of the queries that are interfering pulled out, and we might be able to suggest some changes to them.

    • 0