What is Deadlock?

Question Posted / bhaskar

4 Answers
7243 Views
Satyam, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

What is Deadlock?..

Answer / hari

Deadlocks occur when two or more processes place a lock on
the same resources and each process waits for the others to
release the lock.

Is This Answer Correct ?

4 Yes

0 No

What is Deadlock?..

Answer / sivakumar

A deadlock occurs when there is a cyclic dependency between
two or more threads for some set of resources.

Is This Answer Correct ?

2 Yes

0 No

What is Deadlock?..

Answer / samba shiva reddy . m

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

As you might imagine, deadlocks can use up SQL Server's resources, especially CPU power, wasting it unnecessarily.

Most well-designed applications, after receiving a deadlock message, will resubmit the aborted transaction, which most likely can now run successfully. This process, if it happens often on your server, can drag down performance. If the application has not been written to trap deadlock errors and to automatically resubmit the aborted transaction, users may very well become confused as to what is happening when they receive deadlock error messages on their computer.

Here are some tips on how to 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. 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 your application does 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, not from SQL Server.
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.
[6.5, 7.0, 2000, 2005] Updated 6-6-2005

*****
When a deadlock occurs, by default, SQL Server choose a deadlock "victim" by identifying which of the two processes will use the least amount of resources to rollback, and then returns error message 1205.

But what if you don't like default behavior? Can you change it? Yes, you can, by using the following command:

SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }

WHERE:

Low tells SQL Server that the current session should be the preferred deadlock victim, not the session that incurs the least amount of rollback resources. The standard deadlock error message 1205 is returned.

Normal tells SQL Server to use the default deadlock method.

@deadlock_var is a character variable specifying which deadlock method you want to use. Specify "3" for low, or "6" for normal.

This command is set a runtime for a specified user connection. [2000] Updated 9-1-2005

*****
To help identify deadlock problems, use the SQL Server Profiler's Create Trace Wizard to run the "Identify The Cause of a Deadlock" trace. This will provide you with the raw data you need to help isolate the causes of deadlocks in your databases. [7.0]

*****
To help identify which tables or stored procedures are causing deadlock problems, turn on trace flag 1204 (outputs basic trace data) or trace flag 1205 (outputs more detailed trace data).

DBCC TRACEON (3605,1204,-1)

Be sure to turn off this trace flag when you are done, as this trace can eat up SQL Server's resources unnecessarily, hurting performance. [6.5, 7.0, 2000] Updated 11-6-2006

*****
Ideally, deadlocks should be eliminated from your applications. But if you are unable to eliminate all deadlocks in your application, be sure to include program logic in your application to deal with killed deadlock transactions in a user-friendly way.

For example, let's say that two transactions are deadlocked and that SQL Server kills one of the transactions. In this case, SQL Server will raise an error message that your application needs to respond to. In most cases, you will want your application to wait a random amount of time after the deadlock in order to resubmit the killed transaction to SQL Server.

It is important that there is a random waiting period because it is possible that another contending transaction could also be waiting, and you don't want both contending transactions to wait the same amount of time and then both try to execute at the same time, causing another deadlock.

Is This Answer Correct ?

2 Yes

0 No

What is Deadlock?..

Answer / guest

When a table is accessed by more than one user than that
table may get locked for some particular user and holding
others untill the lock get released, in such scenario the
deadlocks get generated and the query for others executing
that table get stuck.This may lead to the hanging of the
application.
The deadlocks can be overcome by,
Not using order by and group by clause or by index
rebuilding and proper indexing.
The deadlock cannot be permanently avoided

Is This Answer Correct ?

2 Yes

2 No

Post New Answer