deadlocks in sql server

[Hasan]

Hi
I'm having a problem with deadlocks in a table in SQL server when
trying to update it through Biztalk 2004. There is no problem when I
use the same Biztalk solution to update a similar dummy table, but
when I try updating the original table in the production database,
some transactions are updated successfully whereas others become the
victim of the deadlock (Transaction (Process ID 185) was deadlocked on
lock resources with another process and has been chosen as the
deadlock victim. Rerun the transaction). The table that is updated is
also being used by another application that just selects rows from it.

As a workaround, I have used recursion in the code that updates the
table. The function is put through a recursive loop whenever the
deadlock exception(#1205) is caught. It keeps on trying to update the
table until the updation is successful or another exception (not the
deadlock one) is caught. i.e.
Bool Update_IVR(string amount, string customer_id)
{
Try
{
//updation code
}
}
Catch (exception ex)
{
If( ex.message == deadlock message)
{
Bool succ =Update_IVR (amount, customer_id) //recursion
Return succ;
}
Else //error handling code
}

After introducing this code, the problem did not occur for the next
13000 transactions. Then I got the error again four times along with a
timeout error (Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding). However
for the next 17000 transactions (to date) this error has not showed
up.

Thanks

Hasan

[Erland Sommarskog]

Hasan (ha***@mobilink.net) writes:

I'm having a problem with deadlocks in a table in SQL server when
trying to update it through Biztalk 2004. There is no problem when I
use the same Biztalk solution to update a similar dummy table, but
when I try updating the original table in the production database,
some transactions are updated successfully whereas others become the
victim of the deadlock (Transaction (Process ID 185) was deadlocked on
lock resources with another process and has been chosen as the
deadlock victim. Rerun the transaction). The table that is updated is
also being used by another application that just selects rows from it.

As a workaround, I have used recursion in the code that updates the
table. The function is put through a recursive loop whenever the
deadlock exception(#1205) is caught. It keeps on trying to update the
table until the updation is successful or another exception (not the
deadlock one) is caught. i.e.
...
After introducing this code, the problem did not occur for the next
13000 transactions. Then I got the error again four times along with a
timeout error (Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding). However
for the next 17000 transactions (to date) this error has not showed
up.

It seems that you have locking problems in your application, and you
need to perform further analysis to find out where the problem might lie.

Deadlock happens when two processes is trying to access resources already
locked by the other. Here is a simple example:

BEGIN TRANSACTION BEGIN TRANSACTION
UPDATE tbla UPDATE tblb
SET col = 12 SET col = 98
WHERE keycol = 1 WHERE keycol = 8

UPDATE tblb UPDATE tbla
SET col = 88 SET col = 546
WHERE keycol = 8 WHERE keycol = 1
COMMIT TRANSACTION COMMIT TRANSACTION

This will deadlock, when the two processes come to their second UPDATE
statement, they will wait for each other. SQL Server detects this
situation and select one as a deadlock victim.

"Timeout expired" on the other hand can have many causes. This is a timeout
which is set up by the client library, and of which SQL Server has no
knowledge. A timeout could elapse because of long processing time, but
also because the process is blocked by another process. Important to
know is that when you get an timeout expired, you should probably submit
a "IF @@trancount > 0 ROLLBACK TRANSACTION", because there is no automatic
rollback in this case.

I would assume that in your case the Timeout Expired was due to blocking.

In order to understand the cause of deadlocks, you need to get more
information. One way is to set up a Profiler trace to catch deadlocks.
You can also enable trace flags 1204 and 3605 by adding -T1204 and -T3605
to the startup parameters in Enterprise Manger, and restart SQL Server.
In this case, each deadlock will be logged in the SQL Server error log.
In fairly cryptic manner, but neverless.

To detect blocking and what the involved processes are doing, I have a
stored procedure, which is good for this. It's called aba_lockinfo and
you find it on http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Hasan Sheikh]

Thanks for your reply. I have a better understadning of whats going on
now. Can you please explain to me how to set the trace flags you've
mentioned.

Thanks
Hasan

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Hasan Sheikh (ha***@mobilink.net) writes:

Thanks for your reply. I have a better understadning of whats going on
now. Can you please explain to me how to set the trace flags you've
mentioned.

As I said:

You can also enable trace flags 1204 and 3605 by adding -T1204 and -T3605
to the startup parameters in Enterprise Manger, and restart SQL Server

To get there, right-click the server in EM, select Properties. Startup
Parameters is a button at the buttom of the General tab.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp