Prev: Primary Key - string or integer
Next: SQL Server Drops connection after long timeout with replication partner
From: Erland Sommarskog on 13 Mar 2010 13:29
John Hardin (jhardin(a)epicor.com) writes:
> Here is a new data point that just came to light: When this occurs, the
> SPID that's running the UPDATE that has all the others blocked appears
> in Activity Monitor as "AWAITING COMMAND". I would expect it to report
> "UPDATE", or some other "I'm active and actually doing something"
> status.
>
> It does have an open transaction. It appears that the client is opening a
> transaction and not closing it properly...

Yes, and it may not be this particular UPDATE, but some statement before
that.

A trap that is very easy to walk into is that when you start a batch
that starts a transaction, and the client then gets tired of waiting
("Timeout expired") and cancels the batch, the transaction is *not*
rolled back. Therefore, you should always issue

IF @@trancount > 0 ROLLBACK TRANSACTION

when you get a timeout.

If you use SET XACT_ABORT ON, cancellation of a batch will also cause a
rolback, but this setting may have other effects you don't like.

A closer analysis of what locks the process is holding when it blocks
everyone else, may give futher clues.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: John Hardin on 29 Mar 2010 12:25
"Erland Sommarskog" wrote:
> John Hardin (jhardin(a)epicor.com) writes:
>> Here is a new data point that just came to light: When this occurs, the
>> SPID that's running the UPDATE that has all the others blocked appears
>> in Activity Monitor as "AWAITING COMMAND". I would expect it to report
>> "UPDATE", or some other "I'm active and actually doing something"
>> status.
>>
>> It does have an open transaction. It appears that the client is opening a
>> transaction and not closing it properly...
>
> A trap that is very easy to walk into is that when you start a batch
> that starts a transaction, and the client then gets tired of waiting
> ("Timeout expired") and cancels the batch, the transaction is *not*
> rolled back. Therefore, you should always issue
>
> IF @@trancount > 0 ROLLBACK TRANSACTION
>
> when you get a timeout.

The code in the try/catch around the database call does the equivalent.

Apparently somehow there was some interaction between .NET remoting, the
database and the webservice such that the call to the database would be
interrupted without the webservice seeing an error and rolling back the
transaction.

We're still troubleshooting that.

--
John Hardin KA7OHZ
Senior Applications Developer, BI Specialist
EPICOR Retail
web: http://www.epicor.com
voice: (425) 245-1800
fax: (425) 670-1810
email: <jhardin(a)epicor.com>
20818 44th Ave. W., Suite 270
Lynnwood, WA 98036 USA
Worldwide Headquarters 18200 Von Karman, Suite 1000, Irvine CA 92612 USA
------------------------------------------------------------------------
The first time I saw a bagpipe, I thought the player was torturing an
octopus. I was amazed they could scream so loudly.
------------------------------------------------------------------------

First  |  Prev  | 
Pages: 1 2
Prev: Primary Key - string or integer
Next: SQL Server Drops connection after long timeout with replication partner