Transaction Commit TimeOut but Transaction still completes

G

Guest

I'm faced with a weird problem that happens once a week in a production environment(20 transctions a second): I Attempt to commit a SQlTransaction, it throws a "TimeOut Expired" exception but the sqltransaction still commits. The Transacion Object is null which means that it can't be rolled back in the Catch. It's like since the ADO.Net Transaction Commit failed it does not stop the SQL Server Tranaction. By the way I'm running on .Net FrameWork 1.

Has anyone encountered this before or knows a solution for this
 
M

Miha Markic [MVP C#]

Hi Huan,

Huan said:
I'm faced with a weird problem that happens once a week in a production
environment(20 transctions a second): I Attempt to commit a SQlTransaction,
it throws a "TimeOut Expired" exception but the sqltransaction still
commits. The Transacion Object is null which means that it can't be rolled
back in the Catch.

Hmm, how can it be null?
Can you show a piece of the code?

It's like since the ADO.Net Transaction Commit failed it does not stop the
SQL Server Tranaction. By the way I'm running on .Net FrameWork 1.0
 
G

Guest

Here is the code

Private Function GetSomething(ByVal cmd As SqlCommand)

Dim i As Intege

Dim Trans As SqlTransactio
Dim dr As SqlDataReade

Tr
cmd.Connection.Open(

'Begin Transactio
Trans = cmd.Connection.BeginTransactio
cmd.Transaction = Tran
dr = cmd.ExecuteReader(

Do While dr.Read(
'I Took out some business logic her
Loo
dr.Close(

'Commit Transcatio
Trans.Commit(


Catch e As Exceptio
'Rollback Transcatio
If Not cmd.Transaction Is Nothing The
Trans.Rollback(
EventLogger.WriteEntry("Transaction Rollback Successful!", EventLogEntryType.Error
End I

EventLogger.WriteEntry(e.ToString, EventLogEntryType.Error
Finall

If cmd.Connection.State <> ConnectionState.Closed The
cmd.Connection.Close(
End I
End Tr

----- Miha Markic [MVP C#] wrote: ----

Hi Huan

Huan said:
I'm faced with a weird problem that happens once a week in a productio
environment(20 transctions a second): I Attempt to commit a SQlTransaction
it throws a "TimeOut Expired" exception but the sqltransaction stil
commits. The Transacion Object is null which means that it can't be rolle
back in the Catch

Hmm, how can it be null
Can you show a piece of the code

It's like since the ADO.Net Transaction Commit failed it does not stop th
SQL Server Tranaction. By the way I'm running on .Net FrameWork 1.
 
M

Miha Markic [MVP C#]

Hi Huan,
If Not cmd.Transaction Is Nothing Then
Trans.Rollback()


Why are you using different trans variables (not that important)?
Are you saying that one of them is actually Nothing?
When does exception happens? Within Loop?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Huan Larsen said:
Here is the code:

Private Function GetSomething(ByVal cmd As SqlCommand)

Dim i As Integer

Dim Trans As SqlTransaction
Dim dr As SqlDataReader

Try
cmd.Connection.Open()

'Begin Transaction
Trans = cmd.Connection.BeginTransaction
cmd.Transaction = Trans
dr = cmd.ExecuteReader()

Do While dr.Read()
'I Took out some business logic here
Loop
dr.Close()

'Commit Transcation
Trans.Commit()


Catch e As Exception
'Rollback Transcation
If Not cmd.Transaction Is Nothing Then
Trans.Rollback()
EventLogger.WriteEntry("Transaction Rollback Successful!", EventLogEntryType.Error)
End If

EventLogger.WriteEntry(e.ToString, EventLogEntryType.Error)
Finally

If cmd.Connection.State <> ConnectionState.Closed Then
cmd.Connection.Close()
End If
End Try

----- Miha Markic [MVP C#] wrote: -----

Hi Huan,

Huan said:
I'm faced with a weird problem that happens once a week in a
production
environment(20 transctions a second): I Attempt to commit a SQlTransaction,
it throws a "TimeOut Expired" exception but the sqltransaction still
commits. The Transacion Object is null which means that it can't be rolled
back in the Catch.

Hmm, how can it be null?
Can you show a piece of the code?

It's like since the ADO.Net Transaction Commit failed it does not stop the
SQL Server Tranaction. By the way I'm running on .Net FrameWork 1.0
 
G

Guest

In debug mode I noticed that after The Transcation was either commited or rollbacked, cmd.Transaction = Nothing. And the reason that I check for that is because I Try/catch the "cmd.Connection.Open()" I guess that I could of used the "Trans" object also. I'm assuming that "Trans" is a pointer to "cmd.Transaction"

The exception happens at "Trans.Commit()", but the Rollback doesn't happen because "cmd.Transaction" is Nothing which means that the Transaction did actually commit even though it threw an exception.
If I take the check(If Not cmd.Transaction Is Nothing Then) out then the "Trans.Rollback()" will fail because the transaction did complete

----- Miha Markic [MVP C#] wrote: ----

Hi Huan
If Not cmd.Transaction Is Nothing The
Trans.Rollback(


Why are you using different trans variables (not that important)
Are you saying that one of them is actually Nothing
When does exception happens? Within Loop

--
Miha Markic [MVP C#] - RightHand .NET consulting & developmen
miha at rthand co
www.rthand.co

Huan Larsen said:
Here is the code
Private Function GetSomething(ByVal cmd As SqlCommand
Dim i As Intege
Dim Trans As SqlTransactio Dim dr As SqlDataReade
Tr cmd.Connection.Open(
'Begin Transactio
Trans = cmd.Connection.BeginTransactio
cmd.Transaction = Tran
dr = cmd.ExecuteReader(
Do While dr.Read(
'I Took out some business logic her
Loo
dr.Close(
'Commit Transcatio Trans.Commit(
'Rollback Transcatio
If Not cmd.Transaction Is Nothing The
Trans.Rollback(
EventLogger.WriteEntry("Transaction Rollback Successful!" EventLogEntryType.Error
End I
EventLogger.WriteEntry(e.ToString, EventLogEntryType.Error Finall
If cmd.Connection.State <> ConnectionState.Closed The
cmd.Connection.Close(
End I
End Tr
----- Miha Markic [MVP C#] wrote: ----
Hi Huan
I'm faced with a weird problem that happens once a week in
productio
environment(20 transctions a second): I Attempt to commit SQlTransaction
it throws a "TimeOut Expired" exception but the sqltransaction stil
commits. The Transacion Object is null which means that it can't b rolle
back in the Catch
Hmm, how can it be null
Can you show a piece of the code
It's like since the ADO.Net Transaction Commit failed it does no
stop th
SQL Server Tranaction. By the way I'm running on .Net FrameWork 1.Miha Markic [MVP C#] - RightHand .NET consulting & developmen
miha at rthand co
www.rthand.co
 
A

alan.sims

Yes. We have encountered the same problem. I'm still looking for an answer to this issue right now so I can't offer much help.

The SqlTransaction class does not seem to honor the timeout property of the class (SqlCommand) that it's associated with...nor does it have a timeout property of its own.

Please let me know if you hear of an answer to this issue.

alan

**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
 
A

Angel Saenz-Badillos[MS]

Huan,
The most likely problem is that your transaction commit is taking more than
15 seconds and so it is being timed out _on the client_. The Commit has been
executed on the server, we are just waiting to hear that everything worked
fine.

Little known tidbit, the Transaction timeout is directly linked to the
Connection, so setting Connection Timeout in the connection string should
increase the transaction timeout value on the client so that you can stop
getting this problem.

Hope this helps.
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.


Huan said:
I'm faced with a weird problem that happens once a week in a production
environment(20 transctions a second): I Attempt to commit a SQlTransaction,
it throws a "TimeOut Expired" exception but the sqltransaction still
commits. The Transacion Object is null which means that it can't be rolled
back in the Catch. It's like since the ADO.Net Transaction Commit failed it
does not stop the SQL Server Tranaction. By the way I'm running on .Net
FrameWork 1.0
 
A

Angel Saenz-Badillos[MS]

Alan,
I responded on the other thread, posting here again to makes sure you see
this.

The transaction timeout is linked to the Connection Timeout (not the
command). Setting the value on the connection string will allow you to
modify the 15 seconds default.

Hope this helps,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

alan sims said:
Yes. We have encountered the same problem. I'm still looking for an
answer to this issue right now so I can't offer much help.
The SqlTransaction class does not seem to honor the timeout property of
the class (SqlCommand) that it's associated with...nor does it have a
timeout property of its own.
Please let me know if you hear of an answer to this issue.

alan

**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
 
G

Guest

Thanks for the tidbit. That makes sense. I will try it and see what happens

----- Angel Saenz-Badillos[MS] wrote: ----

Huan
The most likely problem is that your transaction commit is taking more tha
15 seconds and so it is being timed out _on the client_. The Commit has bee
executed on the server, we are just waiting to hear that everything worke
fine

Little known tidbit, the Transaction timeout is directly linked to th
Connection, so setting Connection Timeout in the connection string shoul
increase the transaction timeout value on the client so that you can sto
getting this problem

Hope this helps
--
Angel Saenz-Badillos [MS] Managed Provider
This posting is provided "AS IS", with no warranties, and confers n
rights.Please do not send email directly to this alias
This alias is for newsgroup purposes only


Huan said:
I'm faced with a weird problem that happens once a week in a productio
environment(20 transctions a second): I Attempt to commit a SQlTransaction
it throws a "TimeOut Expired" exception but the sqltransaction stil
commits. The Transacion Object is null which means that it can't be rolle
back in the Catch. It's like since the ADO.Net Transaction Commit failed i
does not stop the SQL Server Tranaction. By the way I'm running on .Ne
FrameWork 1.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top