Re: timeout exception during SqlTransaction.Commit() any workaround please help?
- From: "Mary Chipman [MSFT]" <mchip@xxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 22 Jan 2008 09:01:35 -0500
A couple of questions:
Why aren't you using SqlClient if you're going against a SQL Server?
Why don't you wrap //my long running procedures inside of a stored
procedure BEGIN TRAN/COMMIT TRAN? That way you can handle all of the
errors on the server in addition to getting better performance. Each
separate procedure call from the client is another round trip over the
network.
--Mary
On Mon, 21 Jan 2008 10:53:27 +0300, "serg" <serg_NOSPAM_AT_alef.ru>
wrote:
Hi, Mary.
It is an accounting procedure that is closin finacial year.
There are not only simple insertion/deletetion but table triggers firing and
stored procedure execution.
I am widly using IDbCommand.CommandTimeout property for longrunning
procedures and it work fine.
But at the very and when i am closing transaction i get an exception.
This is my code snippet
public class MyConnMgmtClass : IDisposable
{
.
private IDbTransaction _transaction;
.
public void Commit ()
{
if (_transaction == null)
throw new AlefException (00013, null);
_transaction.Commit(); // at this string occurs timeout
exception
}
.
}
Timeout exception occurs at the client level in about 30sec.
Ant this is only client exception not server because of at the server level
i see that transaction is successfuly commited.
I have profiled SQL several times and I see that duration for commiting
transaction is between 60sec to 90sec. (this is mssql2005)
Now i have found the only workaround.
Do not use IDbTransaction object.
Instead I am using excplicit transaction opening/closing with IDbCommand
objects.
Code like this:
IDbCommand command = conn.CreateCommand(false);
command.CommandText = "BEGIN TRAN";
command.ExceuteNonQuery();
...
// my long running procedures
...
command.CommandTimeout = 180; //seconds
command.CommandText = "COMMIT TRAN";
command.ExceuteNonQuery();
This is working.
3 minutes is long enough to commit my transaction.
I do not understand why IDbTransaction object does not have public Timeout
property like IDbCommand object.
Thanks a lot
Serg.
"Mary Chipman [MSFT]" <mchip@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:rrqno3pfa964cbrq0c7rf2vnl6dga5br8v@xxxxxxxxxx
I think you're going to need to provide more information to
troubleshoot this. Have you looked at a profiler trace? What are you
doing inside of that transaction that it takes 10 minutes? 20,000
records is not a lot of data for SQL Server.
-Mary
On Fri, 11 Jan 2008 18:03:02 +0300, "serg" <serg_NOSPAM_AT_alef.ru>
wrote:
I have a long running sql transaction ~10 minutes, ~20000 records
deleted/inserted.
Everything is fine but at the very end when SqlTransaction.Commit() is
perfoming I get a timeout exception.
SqlTransaction does not have a Timeout parameter :(
And I can`t find a way to get an internal SqlCommand to set a
CommandTimeout.
What can I do? Is there any workaround?
Please help
Divide to many small transactions is not a solution. This should be one
transaction.
Thansk in adv.
Serg.
- Follow-Ups:
- References:
- Prev by Date: Re: how to remove databinding
- Next by Date: Re: When will ADO.NET Entity Framework?
- Previous by thread: Re: timeout exception during SqlTransaction.Commit() any workaround please help?
- Next by thread: Re: timeout exception during SqlTransaction.Commit() any workaround please help?
- Index(es):
Relevant Pages
|
