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.
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)
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.
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.
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?
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.
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
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.
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
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 !
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
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.
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…
I have used this one OPTION (RECOMPILE)
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.
Thanks!!!! solve my problem!!!!
Great sir!