SQL SERVER – Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other’s object. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the 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.

Solarwinds

Fix/Workaround/Solution:
Deadlock priority can be set by user. In other words, user can choose which process should stop to allow other process to continue. SQL Server automatically chooses the process to terminate which is running completes the circular chain of locks. Sometime, it chooses the process which is running the for shorter period then other process.

To reduce the chance of a deadlock:

  • Minimize the size of transaction and transaction times.
  • Always access server objects in the same order each time in application.
  • Avoid cursors, while loops, or process which requires user input while it is running.
  • Reduce lock time in application.
  • Use query hints to prevent locking if possible (NoLock, RowLock)
  • Select deadlock victim by using SET DEADLOCK_PRIORITY.

SQL SERVER 2005 has new priority HIGH as well as numeric-priority.

SQL SERVER 2005 Syntax
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }
<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }
Example:
The following example sets the deadlock priority to NORMAL.
SET DEADLOCK_PRIORITY NORMAL;
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Fix: Error 130: Cannot perform an aggregate function on an expression containing an aggregate or a subquery
Next Post
SQLAuthority News – Limited Edition T-Shirts Arrived

Related Posts

94 Comments. Leave new

  • Thank you for the clear explanation, it really saved me a couple hours and helped to quickly resolved the issue I was having with dead locks queries in my application.

    Reply
  • Below was the error failed on schedulind job.
    Executed as user: ANSWERS\administrator. … DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnError: DTSStep_DTSExecuteSQLTask_3, Error = -2147467259 (80004005) Error string: Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1205 (4B5) Error string: Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 … Process Exit Code 1. The step failed.

    Reply
  • Miguel Ramos Alarcón
    September 12, 2013 5:56 am

    I am facing the same problem in my UAT environment, the error:

    “Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”

    doesn’t show up in my DEV environment with the same FrontEnd application, just changing the connection string.

    Do you know what can be causing this behaviour?
    Could be a difference between two database servers?

    Reply
    • Hi Miguel Ramos Alarcón,

      I’m also facing the same issue. Do you have any updates on this issue?

      Thanks for helping me in this regard.

      Reply
      • Miguel Ramos Alarcón
        September 25, 2013 5:25 pm

        Thanks for your reply Ashok. we have a lack of design in our database. In dev environment we don’t have as much information as we have in PROD server, besides bad coding implemented in our stored procedures, so we had a lot of blocking issues. Let me know if you need anything else.

        MRA

  • Abhishek Dhanotia
    February 13, 2014 5:53 pm

    Hi Pinal,

    I am using two stored procedures

    1. Delete a row from table.
    2. Create a new row from existing row.

    When two users called this two SPs concurrently, then i am getting this error message:
    “Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction”

    Sometime its coming for 1st SP and Sometime its coming for 2nd SP.

    Please suggest.

    Reply
  • Knowledge student
    May 15, 2014 5:32 pm

    Hi Pinal,
    Could you please help me in this issue, I have the following error. and I need to know where the deadlock is occurred.

    Below is the error:
    Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    Below is the procedure:
    ALTER PROCEDURE [dbo].[DEGLOCK_PROC]
    (
    @P_ID int out,
    @P_A int,
    @P_B varbinary(max),
    @P_C varchar(50),
    @P_D varchar(50),
    @P_E varchar(50),
    @P_F varchar(50),
    @P_G varbinary(max),
    @P_H int,
    @P_I int,
    @P_J varchar(10),
    @P_K bit,
    @P_L int,
    @P_M int,
    @P_N int,
    @P_O int,
    @P_P VARCHAR(50),
    @P_Q int,
    @P_R xml,
    @P_S xml,
    @P_T nvarchar(max),
    @P_U nvarchar(max),
    @P_V nvarchar(200)

    )
    AS
    BEGIN
    SET XFT_CORT ON;

    DECLARE @X varchar(10), @Y int

    SELECT
    @X = X,
    @Y = Y
    FROM dbo.FOO
    WHERE
    A = @P_A
    AND L = @P_L
    AND H = @P_H
    AND I = @P_I
    AND N = @P_N
    AND O = @P_O
    AND (@P_D IS NOT NULL AND D = @P_D)

    BEGIN TRANSFTION

    IF @Y IS NULL
    BEGIN
    INSERT INTO dbo.FOO
    (
    A,
    B,
    C,
    D,
    E,
    F,
    G,
    H,
    I,
    J,
    K,
    L,
    M,
    N,
    O,
    P
    )
    SELECT
    @P_A ,
    @P_B ,
    @P_C ,
    @P_D ,
    @P_E ,
    @P_F,
    @P_G ,
    @P_H ,
    @P_I ,
    @P_J ,
    @P_K,
    @P_L,
    @P_M,
    @P_N,
    @P_O,
    @P_P

    SET @P_ID = SCOPE_IDENTITY();
    END
    ELSE
    BEGIN
    IF CAST(@P_J AS DECIMAL)> CAST(@X AS DECIMAL)
    BEGIN
    UPDATE dbo.FOO
    SET
    X = @P_J,
    K = @P_K,
    B = @P_B,
    C = @P_C,
    E = @P_E,
    G = @P_G,
    M = @P_M
    FROM dbo.FOO
    WHERE ID = @ID
    END
    SET @P_ID = @ID

    END

    IF EXISTS(SELECT 1 FROM dbo.Bar WHERE ID = @P_ID)
    BEGIN
    UPDATE dbo.Bar
    SET
    AA = @P_R,
    AB = @P_S
    WHERE ID = @P_ID
    END
    ELSE
    BEGIN
    INSERT INTO dbo.Bar
    (
    ID,
    AC,
    AA,
    AB
    )
    SELECT
    @P_ID,
    @P_Q,
    @P_R,
    @P_S
    END

    INSERT INTO dbo.BAZ
    (
    ID,
    AD,
    AE,
    AF
    )
    SELECT
    @P_ID,
    @P_T,
    @P_U,
    @P_V
    WHERE NOT EXISTS
    (
    SELECT 1 FROM dbo.BAZ WHERE ID = @P_ID
    )

    COMMIT TRANSFTION
    END

    Reply
  • Hello,

    I am stuck with an issue. I have written a stored procedure, which has few insert into statements, updates and deletes.
    Even If one of the tables used in the sp does not exists in the Database, the stored procedure gets created successfully, but at the time of execution it generates Deadlock.
    How can i handle this scenario ?

    Thanx in advance !

    Reply
  • I’m having this problem with my procedure
    Could you help me?

    USE [rvsJITDB]
    GO
    /****** Object: StoredProcedure [dbo].[INSERT_CNTDELIVERY] Script Date: 10/24/2014 09:54:57 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[INSERT_CNTDELIVERY] AS

    — Insert into CNTDelivery la trascodificación definida.

    INSERT INTO CNTDELIVERY (
    –CNTMANAGER & GOODSCNT
    CNTID, BAY_NO, BOX_NO,BOX_QTY,
    –CNTDELIVERY_TEMP
    ORDID, BUILDER, PLANT, LINE, LINEPT, JITORDER, SEQUENCE, MODEL, FEATUREORDER, CHASSIS, RECEPT_DT, PROCESS_DT,
    DELIVERY_DT, PART_QTY, TYPEREG, INTERCHANGE_ID, CALLOFF_ID, ADDCALLOFF, SUPPLIER_ID, SYNCHRO_ID, SPECIALDATA,
    PILOTRUN_ID, CUSTOMPN, PARTGROUP_ID, PARTTYPE_ID,PROVIDER_ID, SUPPLYPT, QUANTITY,
    — GOODSATTRIBUTE
    LOCALPN,DESCRIPTION,
    MNG_CTL,LBL_CTL,CNT_CTL,DATESEQ,STATUS,BAY_SIDE,NCOPY )

    SELECT DISTINCT
    –RTRIM(B.CNTID), — JIT
    (CASE
    WHEN B.CNTID IS NULL THEN ‘NC’ –SECUENCIA NORMAL
    ELSE RTRIM(B.CNTID)
    END) AS CNTID,
    0, — BAY_NO
    0, — BOX_NO
    1, — BOX_QTY
    A.ORDID, — ORDID
    A.BUILDER, — BUILDER
    A.PLANT, — PLANT
    A.LINE, — LINE
    A.LINEPT, — POINT
    A.JITORDER, — ORDER
    A.SEQUENCE, — SEQUENCE
    A.MODEL,
    A.FEATUREORDER,
    A.CHASSIS,
    A.RECEPT_DT, — RECEPTION DATE
    A.PROCESS_DT, — PROCESS DATE
    A.DELIVERY_DT, — DELIVERY DATE
    A.PART_QTY, — Nº PARTS NUMBER BY JITORDER
    A.TYPEREG,
    A.INTERCHANGE_ID,
    A.CALLOFF_ID,
    — G.PRN_ORD,
    A.ADDCALLOFF,
    A.SUPPLIER_ID, — SUPPLIER (STATIC)
    A.SYNCHRO_ID,
    A.SPECIALDATA,
    A.PILOTRUN_ID,
    A.CUSTOMPN, — VW REFERENCE
    A.DESCRIPTION, — DESCRIÇÃO
    A.PARTGROUP_ID,
    — A.PARTTYPE_ID,
    A.PROVIDER_ID,
    A.SUPPLYPT, — SUPPLY POINT (STATIC)
    A.QUANTITY, — QUANTITY (1)
    CASE WHEN G.LOCALPN IS NULL THEN A.CUSTOMPN
    ELSE G.LOCALPN
    END AS LOCALPN,
    — G.DESC1_DESC AS DESCRIPTION,
    CASE WHEN G.DESC1_DESC IS NULL THEN ‘SEM DESCRIÇÃO’
    ELSE G.DESC1_DESC
    END AS DESCRIPTION,

    0, — MNG_CTL
    1 AS LBL_CTL, — LBL_CTL
    0, — CNT_CTL
    CONVERT(VARCHAR,LEFT(‘000’,3-LEN(DATEPART(DAYOFYEAR,A.RECEPT_DT))))
    +CONVERT(VARCHAR,DATEPART(DAYOFYEAR,A.RECEPT_DT)) + CONVERT(VARCHAR,LEFT(‘0000’,4-LEN(CONVERT(INT,A.SEQUENCE))))
    + CONVERT(VARCHAR,A.SEQUENCE),
    G.PRN_ORD, –STATUS
    ‘A’,
    1
    –AÑADE FILTROS A GOODSCNT
    FROM CNTDELIVERY_TEMP A
    LEFT OUTER JOIN GOODSCNT B
    ON A.CUSTOMPN LIKE RTRIM(B.LOCALPN) –AND CONVERT(VARCHAR,A.LINE) LIKE B.LINE
    INNER JOIN CNTMANAGER C ON
    ((B.CNTID IS NULL AND ‘NC’=C.CNTID)
    OR
    (B.CNTID IS NOT NULL AND B.CNTID=C.CNTID))
    AND A.LINE=C.LINE
    LEFT OUTER JOIN GOODSATTRIBUTE G ON A.CUSTOMPN = G.CUSTOMPN
    INNER JOIN ADMPRINTER P ON A.CUSTOMPN LIKE P.CUSTOMPN
    /*WHERE
    (SELECT COUNT(*) FROM GOODSCNT K WHERE A.CUSTOMPN LIKE RTRIM(K.LOCALPN) AND CONVERT(VARCHAR,A.LINE) LIKE K.LINE AND K.FILTERED=1)=0
    */
    –COMENTAR CUENDO FUNCIONE MARKCNT PARA DOBLE BOLSA
    –EXEC INSERT_CNTDELIVERY_TRATA_DOBLE_BOLSA

    Reply
  • Hi, Could any one help me to resolve the issue. We have around 5 SP’s which are inserting data into Table A,and these will run in parallel.From the temp tables in the SP,data will be loaded to Table A. We are getting deadlock here.

    What could be done to avoid deadlock.

    Reply
  • Hello pinal ,

    I am following your site from 3 years and have learned so much things
    i have read this whole page from top to bottom but not cleared with the solution to handle this.
    If my transaction table rows are always have concurrent DML happening by users because it is an ERP system , All the day users are getting 1205 error . i am thinking to implement tracking of error 1205 and if this happens it must tried for some no of time using maintaining flags like below

    Begin catch

    IF (ERROR_NUMBER() = 1205)
    SET @retry = @retry – 1;
    ELSE
    SET @retry = -1;

    IF XACT_STATE() 0
    ROLLBACK TRANSACTION;

    end catch

    Please help if i can improve the solution to the best…

    Reply
  • Jamshoo Lakhani
    May 2, 2016 8:37 pm

    I have used this one OPTION (RECOMPILE)

    Reply
  • this worked for me, just connected to my database instance and issued the following command:

    ALTER DATABASE MyDataBaseName
    SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
    GO

    All my deadlocks went away.

    Reply
  • Thanks!!!! solve my problem!!!!

    Reply
  • Great sir!

    Reply

Leave a Reply

Menu