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.
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.
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.
- Avoid cursors.
- Keep transactions as short as possible.
- Reduce lock 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.
