Deadlock problem

[ketankshah]

I am sometimes getting deadlock error on my customer's e-commerce website.

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 73)
was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
/secure/shopcart.asp, line 405

How can I sort out this error?

Thanks

Ketan

 

[tlbroadbent]

Check the following links from SQL BOL.

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_3hdf.asp

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_9m43.asp

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_3xrf.asp

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[kutty007]

Dead lock, as you may be knowing happens when a process is trying to access an object locked by another process. One way to avoid this by using uncommited read when ever snap Shot data is sufficient.

For ex. you can say

SELECT * FROM tblProduct WITH (NOLOCK) WHERE ....

This will fetch data from Product table which have been updated in the table but may not have been commited as yet. This is called dirty read.

Typically this may not be acceptable in case of critical data(like financial transactions). But in most other scenario this should be OK.

 

[fluteplr]

Kutty, actually deadlock is a bit more complicated.
In your scenario the lock problem will disapear once the first process (the one that has a lock) has finished this is not a deadlock although the 2nd process may timeout waiting for the first to finish.

A deadlock is when there are two or more processes in the following setup.

Process 1 has table A locked and needs table B to finish.

Process 2 has table B locked and needs table A to finish.

Since neither process can finish you have a deadlock. The server then picks a process to kill which is the only way out of a deadlock.

Usually deadlock are caused by creating transactions that use a large number of tables and run for a long time. By carefully monitoring what processes are running when a deadlock occurs you can generally identify them and modify the code to avoid the problem.

 

[tlbroadbent]

Fluteplr is correct regarding deadlocks. That is the reason I directed Ketan to SQL BOL. BOL contains a more detailed explanation as well as strategies for avoiding deadlocks. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm