SQL Error 1205 Transaction was deadlocked

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.

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 WITH (NOLOCK)
• Select deadlock victim by using SET DEADLOCK_PRIORITY.

Sources:

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