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

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

SQL SERVER – Trace Flags – DBCC TRACEON 

Trace flags are valuable tools as they allow DBA to enable or disable a database function temporarily. Once a trace flag is turned on, it remains on until either manually turned off or SQL Server restarted. Only users in the sysadmin fixed server role can turn on trace flags.

If you want to enable/disable Detailed Deadlock Information (1205), use Query Analyzer and DBCC TRACEON to turn it on.

1205 trace flag sends detailed information about the deadlock to the error log.

Enable Trace at current connection level:

DBCC TRACEON(1205)

Disable Trace:

DBCC TRACEOFF(1205)

Enable Multiple Trace at same time separating each trace with a comma.

DBCC TRACEON(1205,2528)

Disable Multiple Trace at same time separating each trace with a comma.

DBCC TRACEOFF(1205,2528)

To set the trace using the DBCC TRACEON command at a server level, Pass second argument to the function as -1.

DBCC TRACEON (1205, -1)

To enumerate a complete list of traces that are on run following command in query analyzer.

DBCC TRACESTATUS(-1)
 

Avoid deadlocking on your SQL Server

Deadlock:

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. 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.


Some tips for reducing the deadlock:

• 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. Collect it before the transaction begins.
• Avoid cursors.
• Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch.
• Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If you do need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there.
• Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest 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. Consider using bound connections.

Example:
You are a database developer for a clothing retailer. The company has a database named Sales. This
database contains a table named Inventory. The Inventory table contains the list of items for sale and the
quantity available for each of those items. When sales information is inserted into the database, this table
is updated. The stored procedure that updates the Inventory table is shown in the exhibit.
CREATE PROCEDURE UpdateInventory @IntID int
AS
BEGIN
DECLARE @Count int
BEGIN TRAN
SELECT @Count = Available
FROM Inventory WITH (HOLDLOCK)
WHERE InventoryID = @IntID
IF (@Count > 0)
UPDATE Inventory SET Available = @Count – 1
WHERE InventoryID = @IntID
COMMIT TRAN
END
When this procedure executes, the database server occasionally returns the following error message:
Transaction (Process ID 53) was deadlocked on {lock} resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
You need to prevent the error message from occurring while maintaining data integrity.
Change the table hint to UPDLOCK.
Explanation: This is a deadlock problem. We must resolve this problem. The SQL batch of this scenario
basically consists of an SELECT statement and an UPDATE statement. The SELECT statement includes a table
hint: WITH (HOLDLOCK).
This table hint is very restrictive. The rows are completely locked. We need to remove this restrictive table hint
and replace it with the table hint UPDLOCK, which is less restrictive than HOLDLOCK. It allows reads of the
rows, but no updates.
Note: table hint
A table hint specifies that a table scan, or one or more indexes, must be used by the query optimizer, or a
locking method must be used by the query optimizer with this table and for this SELECT. The query optimizer
can usually pick the best optimization method without hints being specified, therefore it is recommended that
hints only be used as a last resort by experienced developers and database administrators.

Reference Links:

http://www.sql-server-performance.com/tips/deadlocks_p1.aspx