T-SQL vs ADO.NET Transaction?

[Guest]

Hello,

I have an application that is currently using a T-SQL stored procedure that
uses a transaction to perform an update to a single record and then INSERT,
what could be HUNDREDS OF THOUSANDS of records, into a related table. My
T-SQL looks like this:

BEGIN TRANS

UPDATE MyTable SET statusCode = 2 WHERE MyTable.myTableId = @myTableId
IF @@ERROR <> 0
BEGIN
/*code to rollback*/
END

/*could insert hundreds of thousands of records (i.e. 300,000)*/
INSERT INTO MyRelatedHistoryTable (Col1, Col2) SELECT ColOne, ColeTwo FROM
MyRelatedTable (NOLOCK)

IF @@ERROR <> 0
BEGIN
/*ROLLBACK code here*/
END

COMMIT TRANS


The problem is that my ADO.NET command is given a timeout period of 2
minutes and it will occasionally timeout. This causes an OPEN TRANSACTION in
the database (i.e. using DBCC OPENTRAN shows an open transaction) for several
minutes, which locks my table and causes other timeouts.

I am thinking of just removing the transaction handling from the stored
procedure and just wrapping the stored procedure call into an ADO.NET
transaction. I believe this will help resolve my OPEN TRANSACTION issue in
the case of a timeout, but would moving the transaction to an ADO.NET
transaction reduce performance of my stored procedure call?

TIA!!!

 

[Dave Sexton]

Hi,

The problem is that my ADO.NET command is given a timeout period of 2
minutes and it will occasionally timeout.

Why can't you just increase the SqlCommand's timeout?

This causes an OPEN TRANSACTION in
the database (i.e. using DBCC OPENTRAN shows an open transaction) for
several
minutes, which locks my table and causes other timeouts.

Are you closing the connection after the SqlCommand throws its exception?

I am thinking of just removing the transaction handling from the stored
procedure and just wrapping the stored procedure call into an ADO.NET
transaction. I believe this will help resolve my OPEN TRANSACTION issue in
the case of a timeout, but would moving the transaction to an ADO.NET
transaction reduce performance of my stored procedure call?

SqlTransaction just sends BEGIN TRANS like you were doing in your procedure.

SqlConnection.BeginTransaction method on MSDN:
http://msdn.microsoft.com/library/d...ionClassBeginTransactionTopic1.asp?frame=true

If your SqlCommand times out you should close the connection and all pending
transactions will be rolled back.

SqlConnection.Close method on MSDN:
http://msdn.microsoft.com/library/d...ntSqlConnectionClassCloseTopic.asp?frame=true

 

[Guest]

Why can't you just increase the SqlCommand's timeout?

Brian: I already have increased it to 3 minutes and it occasionally takes a
little longer. It is part of a much larger process that takes a total of 6
minutes or so. I just want to make sure that as data starts aggregating that
I am covered if a timeout occurs.

 

[Frans Bouma [C# MVP]]

Hello,

I have an application that is currently using a T-SQL stored
procedure that uses a transaction to perform an update to a single
record and then INSERT, what could be HUNDREDS OF THOUSANDS of
records, into a related table. My T-SQL looks like this:

BEGIN TRANS

UPDATE MyTable SET statusCode = 2 WHERE MyTable.myTableId = @myTableId
IF @@ERROR <> 0
BEGIN
/*code to rollback*/
END

/*could insert hundreds of thousands of records (i.e. 300,000)*/
INSERT INTO MyRelatedHistoryTable (Col1, Col2) SELECT ColOne, ColeTwo
FROM MyRelatedTable (NOLOCK)

IF @@ERROR <> 0
BEGIN
/*ROLLBACK code here*/
END

COMMIT TRANS


The problem is that my ADO.NET command is given a timeout period of 2
minutes and it will occasionally timeout. This causes an OPEN
TRANSACTION in the database (i.e. using DBCC OPENTRAN shows an open
transaction) for several minutes, which locks my table and causes
other timeouts.

I am thinking of just removing the transaction handling from the
stored procedure and just wrapping the stored procedure call into an
ADO.NET transaction. I believe this will help resolve my OPEN
TRANSACTION issue in the case of a timeout, but would moving the
transaction to an ADO.NET transaction reduce performance of my stored
procedure call?

That won't make a difference. An ADO.NET transaction will first
execute BEGIN TRANS name and then you'll run your code, which means the
same thing.

What could cause the timeout is a transaction log which is way too
small. As you're inserting 300,000 rows in one transaction, each insert
is logged. If your transaction log is too small, it will have to resize
several times during the insert process which can be very slow. I also
find 2 minutes for 300,000 inserts rather conservative, I'd set it to
10 minutes minimum.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------