Reducing SQL Server Deadlocks

Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. When this happens, SQL Server identifies the problem and ends the deadlock by automatically choosing one process and aborting the other process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.

As you might imagine, deadlocks can use up SQL Server’s resources, especially CPU power, wasting it unnecessarily.

Most well-designed applications, after receiving a deadlock message, will resubmit the aborted transaction, which most likely can now run successfully. This process, if it happens often on your server, can drag down performance. If the application has not been written to trap deadlock errors and to automatically resubmit the aborted transaction, users may very well become confused as to what is happening when they receive deadlock error messages on their computer.

Here are some tips on how to avoid deadlocking on your SQL Server:

  • Ensure the database design is properly normalized.
  • Have the application access server objects in the same order each time.
  • During transactions, don’t allow any user input. Collect it before the transaction begins.
  • Avoid cursors.
  • Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch. Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If your application does need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there, not from SQL Server.
  • Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
  • If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
  • Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
  • If appropriate, use as low of an isolation level as possible for the user connection running the transaction.  
  • Consider using bound connections.

[6.5, 7.0, 2000, 2005] Updated 6-6-2005

*****

When a deadlock occurs, by default, SQL Server choose a deadlock “victim” by identifying which of the two processes will use the least amount of resources to rollback, and then returns error message 1205.

But what if you don’t like default behavior? Can you change it? Yes, you can, by using the following command:

SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }

WHERE:

Low tells SQL Server that the current session should be the preferred deadlock victim, not the session that incurs the least amount of rollback resources. The standard deadlock error message 1205 is returned.

Normal tells SQL Server to use the default deadlock method.

@deadlock_var is a character variable specifying which deadlock method you want to use. Specify “3″ for low, or “6″ for normal.

This command is set a runtime for a specified user connection. [2000] Updated 9-1-2005

*****

To help identify deadlock problems, use the SQL Server Profiler’s Create Trace Wizard to run the “Identify The Cause of a Deadlock” trace. This will provide you with the raw data you need to help isolate the causes of deadlocks in your databases.  [7.0]

*****

To help identify which tables or stored procedures are causing deadlock problems, turn on trace flag 1204 (outputs basic trace data) or trace flag 1205 (outputs more detailed trace data).

DBCC TRACEON (3605,1204,-1)

Be sure to turn off this trace flag when you are done, as this trace can eat up SQL Server’s resources unnecessarily, hurting performance. [6.5, 7.0, 2000] Updated 11-6-2006

*****

Ideally, deadlocks should be eliminated from your applications. But if you are unable to eliminate all deadlocks in your application, be sure to include program logic in your application to deal with killed deadlock transactions in a user-friendly way.

For example, let’s say that two transactions are deadlocked and that SQL Server kills one of the transactions. In this case, SQL Server will raise an error message that your application needs to respond to. In most cases, you will want your application to wait a random amount of time after the deadlock in order to resubmit the killed transaction to SQL Server.

It is important that there is a random waiting period because it is possible that another contending transaction could also be waiting, and you don’t want both contending transactions to wait the same amount of time and then both try to execute at the same time, causing another deadlock.




Related Articles :

  • No Related Articles Found

One Response to “Reducing SQL Server Deadlocks”

  1. As mentioned in the main part, one of the improvements would be is to reduce lock time.
    In our shop one of the stored procedures has a complicated query that uses many views and constructs a long XML. The query runs for about 5 minutes and occasionally causes deadlocks elsewhere. We decided to place the data, constructed by views, into temporary tables and to use temporary table instead of views in the complicated query itself. As the result, permanent tables became locked only for about 1-2 minutes, although the whole SP was still running 5 minutes or longer.
    Identifying the length of time of tables being locked is not very straight forward, so we created an SP that runs in a separate Query Analyzer session and monitors another process for tables being locked. Here is the code of this monitoring SP:

    IF OBJECT_ID(N’DBO.MonLocks’, ‘P’) > 0
    BEGIN
    DROP PROCEDURE DBO.MonLocks
    END
    GO

    CREATE PROCEDURE DBO.MonLocks
    (@Process_id as int = NULL,
    @DBName VARCHAR (63) = ‘DailyDB’,
    @IncludeTempDB CHAR (1) = ‘N’)
    AS

    /************************************************************************************
    PRINT @@SPID

    EXEC MonLocks
    @Process_id = 187,
    @DBName = ‘WeeklyDB’

    select * from ##TTot
    order by TimeLocked DESC

    ************************************************************************************/
    BEGIN
    SET NOCOUNT ON

    DECLARE @CpuUsed BIGINT,
    @TickStart DATETIME,
    @TickEnd DATETIME,
    @Msec FLOAT,
    @SQL NVARCHAR(MAX),
    @WaitFor_Delay DATETIME = ’00:00:01′, – 1 second
    – need to count how many time in a row ‘CPU idle’ happenes
    – because in tests ‘CPU idle’ was for mor than 3 seconds,
    – but the monitored process was still running
    @WaitsTotIdleCPU INT = 15,
    @WaitsCntIdleCPU INT

    IF OBJECT_ID(‘TEMPDB..##TCurr’, ‘U’) IS NOT NULL
    DROP TABLE ##TCurr
    IF OBJECT_ID(‘TEMPDB..##TTot’, ‘U’) IS NOT NULL
    DROP TABLE ##TTot

    – create temp tabs
    CREATE TABLE ##TCurr (TabName VARCHAR(511) NOT NULL)

    CREATE TABLE ##TTot (
    TabName VARCHAR(511) NOT NULL,
    TimeLocked FLOAT NULL DEFAULT 0.0,
    LockStart DATETIME NULL,
    LastLockEnded DATETIME NULL)

    – wait for process to start
    WHILE 1=1
    BEGIN
    – save @CpuUsed
    SELECT @CpuUsed = cpu
    FROM master..sysprocesses
    WHERE spid = @Process_id

    – wait for 1 second
    WAITFOR DELAY @WaitFor_Delay

    – if @CpuUsed changed, the processing started
    IF @CpuUsed
    (
    SELECT TOP 1 cpu
    FROM master..sysprocesses
    WHERE spid = @Process_id
    )
    BREAK
    END

    – processing started

    – save CPU start time
    INSERT ##TTot
    SELECT TabName=’CPU’, TimeLocked=0, LockStart = GETDATE(), LastLockEnded = GETDATE()

    – ‘SEED’ the @TickStart
    SET @TickStart = GETDATE()

    – accumulate tables lock times
    WHILE 1=1
    BEGIN
    TRUNCATE TABLE ##TCurr

    – get all currently active perm tables
    SET @SQL = ‘INSERT ##TCurr
    SELECT DISTINCT SO.Name
    FROM sys.dm_tran_locks TL
    JOIN ‘ + @DBName + ‘..SYSOBJECTS SO ON SO.ID =TL.resource_associated_entity_id
    WHERE request_session_id = ‘ + CAST(@Process_id AS VARCHAR)
    EXEC sp_executesql @SQL

    – get all currently active temp tables
    IF @IncludeTempDB = ‘Y’
    BEGIN
    – TEMPDB is causing deadlocks and early termination
    INSERT ##TCurr
    SELECT DISTINCT SO.Name
    FROM sys.dm_tran_locks TL
    JOIN TEMPDB..SYSOBJECTS SO ON SO.ID =TL.resource_associated_entity_id
    WHERE request_session_id = @Process_id
    END

    – insert missing active permanent tables into ##TTot
    INSERT ##TTot (TabName, LockStart)
    SELECT DISTINCT TabName, LockStart = @TickStart
    FROM ##TCurr
    WHERE NOT EXISTS
    (
    SELECT 1 FROM ##TTot WHERE TabName = ##TCurr.TabName
    )

    – save @CpuUsed
    SELECT @CpuUsed = cpu
    FROM master..sysprocesses
    WHERE spid = @Process_id

    – wait for 1 second
    WAITFOR DELAY @WaitFor_Delay

    – if @CpuUsed did not change, the processing stopped
    IF @CpuUsed =
    (
    SELECT TOP 1 cpu
    FROM master..sysprocesses
    WHERE spid = @Process_id
    )
    BEGIN
    – comes here if CPU did not change for the process
    – need to count how many time in a row ‘CPU idle’ happenes
    – because in tests ‘CPU idle’ was for mor than 3 seconds,
    – but the monitored process was still running
    SET @WaitsCntIdleCPU = @WaitsCntIdleCPU + 1
    IF @WaitsCntIdleCPU < @WaitsTotIdleCPU
    BEGIN
    – just keep counting 'CPU idle' occurances
    CONTINUE
    END
    ELSE
    BEGIN
    – process did not get CPU for long enough time,
    – so assume it stopped
    BREAK
    END
    END

    SET @WaitsCntIdleCPU = 0

    SET @TickEnd = GETDATE()
    SET @Msec = DATEDIFF(ms, @TickStart, @TickEnd) / 1000.0
    – keep @TickStart close to @TickEnd, so
    – SUM will match the real difference between end time and start time
    SET @TickStart = GETDATE()

    – update CPU time
    UPDATE ##TTot
    SET TimeLocked = TimeLocked + @Msec,
    LastLockEnded = GETDATE()
    WHERE TabName = 'CPU'

    – update ##TTot for tables that are currently active
    UPDATE ##TTot
    SET TimeLocked = TimeLocked + @Msec,
    LastLockEnded = GETDATE()
    WHERE EXISTS (SELECT 1 FROM ##TCurr WHERE TabName = ##TTot.TabName)

    END

    – update CPU time
    UPDATE ##TTot
    SET LastLockEnded = GETDATE()
    WHERE TabName = 'CPU'

    select * from ##TTot
    order by TimeLocked DESC

    select * from ##TTot
    order by LastLockEnded DESC
    END

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |