timeout exception during SqlTransaction.Commit() any workaround please help?

serg

unread,

Jan 11, 2008, 10:03:02 AM1/11/08

to

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.

Mary Chipman [MSFT]

unread,

Jan 14, 2008, 6:11:01 PM1/14/08

to

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:

serg

unread,

Jan 21, 2008, 2:53:27 AM1/21/08

to

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]" <mc...@online.microsoft.com> wrote in message
news:rrqno3pfa964cbrq0...@4ax.com...

Mary Chipman [MSFT]

unread,

Jan 22, 2008, 9:01:35 AM1/22/08

to

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:

serg

unread,

Jan 22, 2008, 11:02:42 AM1/22/08

to

Actually under interfaces i am using native SqlClient. Initialy it was OLEDB
but with migrating to net 2.0 it became SqlClient.

As to single stored procedure.
There are complicated math calculations transact-sql is not well for them.

Serg.

"Mary Chipman [MSFT]" <mc...@online.microsoft.com> wrote in message

news:citbp3hso3av1c8tt...@4ax.com...

Mary Chipman [MSFT]

unread,

Jan 23, 2008, 12:21:42 PM1/23/08

to

T-SQL isn't good at math, but the CLR is. You can write your sprocs in
managed code in SQLS 2005, which would give you the best of both
worlds. For more info, see
http://msdn2.microsoft.com/en-us/library/ms345136.aspx.

--Mary

On Tue, 22 Jan 2008 19:02:42 +0300, "serg" <serg_NOSPAM_AT_alef.ru>
wrote:

serg

unread,

Jan 24, 2008, 3:49:33 AM1/24/08

to

Yes, i know about clr in sql2005
But now this is not my willing or unwilling to rewrite it.
It is money question. No one want to give money to rewrite app that is
working quite well.
There should be much more weighty reasons to do it.
I should wait for users maturing. :)

Thanks.
Serg.

"Mary Chipman [MSFT]" <mc...@online.microsoft.com> wrote in message

news:nptep39tjcqu5hbss...@4ax.com...

haseebkhan

unread,

Apr 27, 2010, 2:09:24 PM4/27/10

to

You can set the ConnetionTime out value to 30 instead of 15 (default). This you can pass it in the connection string. The Commit is taking more than 15 seconds and hence the issue.

serg wrote:

Yes, i know about clr in sql2005But now this is not my willing or unwilling to
24-Jan-08

Yes, i know about clr in sql2005
But now this is not my willing or unwilling to rewrite it.
It is money question. No one want to give money to rewrite app that is
working quite well.
There should be much more weighty reasons to do it.
I should wait for users maturing. :)

Thanks.
Serg.

"Mary Chipman [MSFT]" <mc...@online.microsoft.com> wrote in message
news:nptep39tjcqu5hbss...@4ax.com...