Understanding what is a SQL Server Transaction Deadlock:
When a SQL Server task fails with the following error, your transactions have encountered a Deadlock.
RetCode: SQL_ERROR SqlState: 40001 NativeError: 1205 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction (Process ID 966) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
“Transaction was deadlocked” error occurs when two or more sessions are waiting to get a lock on a resource which has already locked by another session in the same blocking chain. As a result, none of the sessions can be completed and SQL Server has to intervene to solve this problem. It gets rid of the deadlock by automatically choosing one of the sessions as a victim and kills it allowing the other session to continue. In such case, the client receives the following error message:
Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
and the killed session is rolled back. As a rule, the victim is the session that requires the least amount of overhead to roll back.
For more information on deadlocks and how to find the related processes, view the link below.
https://sqlbak.com/academy/transaction-process-id-was-deadlocked-on-lock-resources-with-another-proc...
After identified processes are no longer running on the same table and locking it, your task will be able to resume normally.
Environment
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
