Repeatable bug with SQLTransaction object

  • Thread starter Thread starter Dano
  • Start date Start date
D

Dano

Hi all,

I am always reluctant to call something a bug in the very fine .Net
framework seeing as how I am relatively new to it. However I have come
across some behaviour dealing with transactions that I don't think by any
stretch of language can NOT be called a bug. If it is my own lack of
experience that is causing this whole situation, then I apologize.

So here is the situation. I am doing some database deletion of records, and
I use the Transaction object. I then purposefully cause an error to check
that the Transaction roll back is working. Strangely enough I get a "The
Rollback Transaction request has no corresponding Begin Transaction" error.
So it sounds like a scoping problem with Try blocks and the Begin
Transaction right? Nope! After spending several hours slamming my head
against a brick wall, I begin commenting out lines one at a time. After much
experimenting I find it has nothing whatsoever to do with Transactions, or
Begin Transactions. Here is the Code, reduced to as few lines as I could
while still generating the error:

<Code>

Public Sub deleteAuditGroup(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnDelete.Click

Dim objConnection As SqlConnection

Dim objTransaction As SqlTransaction

objConnection = New SqlConnection(Application("strConnection"))

Dim objCommand As New SqlCommand()

Try



objConnection.Open()

objTransaction = objConnection.BeginTransaction()

objCommand.Connection = objConnection

objCommand.Transaction = objTransaction

objCommand.CommandType = CommandType.StoredProcedure

objCommand.CommandText = "sp_AuditGroupDriverEntireDelete"

Dim objParam As SqlParameter

objParam = New SqlParameter()

objParam = objCommand.Parameters.Add("@AuditGroupID", SqlDbType.Int)

objParam.Direction = ParameterDirection.Input

objParam.Value = 27 ' Actual int value is irrelevant




' Notice that this is deliberately commented out

'objParam = objCommand.Parameters.Add("@EmployeeID", SqlDbType.Int)

' objParam.Direction = ParameterDirection.Input

' objParam.Value = 5


' This is a bogus parameter not required by the stored procedure to cause
an exception and force a rollback.

objParam = objCommand.Parameters.Add("@blech", SqlDbType.Int)

objParam.Direction = ParameterDirection.Input

objParam.Value = dropAuditGroup.SelectedItem.Value

objCommand.ExecuteNonQuery()



Catch objException As Exception

objTransaction.Rollback()

lblOutput.Text += "**** Error while Deleting Driver From AuditGroup " +
objException.Message() + " <br/>"

objConnection.Close()

Exit Sub

End Try

</Code>

For what it is worth, here is the stored procedure, though I doubt it has
anything to do with this

<Code>

CREATE PROCEDURE sp_AuditGroupDriverEntireDelete

@AuditGroupID int,

@EmployeeID int

AS

DELETE FROM tblAuditGroupDriver

WHERE AuditGroupID=@AuditGroupID

AND EmployeeID=@EmployeeID

GO

</Code>

Now as it stands, the above works fine. It prints out the error stating that
the Blah parameter is not required for the stored procedure, and more
importantly rolls back the transaction. Now all you have to do to call the
pseudo BeginTransaction Error is to uncomment out the EmployeeID parameter
in the above code. Voila! Note, that it has nothing to do with the fact that
EmployeeID is actually required by the stored procedure. ANY additional
bogus parameter will also cause the same behaviour. For some reason in this
case, 2 parameters cause the transaction to be properly rollbacked , but 3
make the server think that there is no BeginTransaction.

I tried this 5 times with two parameters and got the proper message 5 times.
With 3 parameters I got the missing BeginTransaction error all 5 times.

I know for sure something very strange is going on here. Is this truly a
bug, or is it my own ineptitude?

Dano
 
I haven't tried to decipher all of your code. Remember
that the Transaction object must be associated will all
of the Command objects that are being used (Update,
Insert, and Delete).

Also, if your forced error condition causes an error that
is not related to the actual Delete operation, the
Rollback call will generate a second exception of the
kind that you are seeing. Try catching your error
without calling rollback to see what you get.

Many things in this operation tend to look like bugs at
first, but turn out to be everything but.

Javed




-----Original Message-----
Hi all,

I am always reluctant to call something a bug in the very fine .Net
framework seeing as how I am relatively new to it. However I have come
across some behaviour dealing with transactions that I don't think by any
stretch of language can NOT be called a bug. If it is my own lack of
experience that is causing this whole situation, then I apologize.

So here is the situation. I am doing some database deletion of records, and
I use the Transaction object. I then purposefully cause an error to check
that the Transaction roll back is working. Strangely enough I get a "The
Rollback Transaction request has no corresponding Begin Transaction" error.
So it sounds like a scoping problem with Try blocks and the Begin
Transaction right? Nope! After spending several hours slamming my head
against a brick wall, I begin commenting out lines one at a time. After much
experimenting I find it has nothing whatsoever to do with Transactions, or
Begin Transactions. Here is the Code, reduced to as few lines as I could
while still generating the error:

<Code>

Public Sub deleteAuditGroup(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnDelete.Click

Dim objConnection As SqlConnection

Dim objTransaction As SqlTransaction

objConnection = New SqlConnection(Application ("strConnection"))

Dim objCommand As New SqlCommand()

Try



objConnection.Open()

objTransaction = objConnection.BeginTransaction()

objCommand.Connection = objConnection

objCommand.Transaction = objTransaction

objCommand.CommandType = CommandType.StoredProcedure

objCommand.CommandText
Click to expand...
= "sp_AuditGroupDriverEntireDelete"
Dim objParam As SqlParameter

objParam = New SqlParameter()

objParam = objCommand.Parameters.Add
Click to expand...
("@AuditGroupID", SqlDbType.Int)
 
First off, what version of SQLServer are you using. I was unable to
reproduce this with SQL2000 with clr 1.0 or 1.1.
(test program at end).


Second, the error message "The Rollback Transaction request has no
corresponding Begin Transaction" means that the transaction has already been
rolled back. There are some errors in SQLServer which will automatically
roll back your transaction. Perhaps you are seeing such a behavior. In any
case you can be certian that the transaction has been rolled back. Just
wrap your SqlTransaction.Rollback in a try block and eat this error.

David





Imports System.Data.SqlClient

Module Module1

Sub Main()

Diagnostics.Trace.Listeners.Add(New
TextWriterTraceListener(Console.Out))
Dim con As New SqlConnection(". . .")
con.Open()
Dim tran As SqlTransaction = con.BeginTransaction()
Dim cmd As New SqlCommand("sp_AuditGroupDriverEntireDelete", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Transaction = tran

Dim pAuditGroupID As SqlParameter = cmd.Parameters.Add("@AuditGroupID",
SqlDbType.Int)
pAuditGroupID.Direction = ParameterDirection.Input
pAuditGroupID.Value = 27 ' Actual int value is irrelevant

Dim pEmployeeID As SqlParameter = cmd.Parameters.Add("@EmployeeID",
SqlDbType.Int)
pEmployeeID.Direction = ParameterDirection.Input
pEmployeeID.Value = 5

' This is a bogus parameter not required by the stored procedure to
cause an exception and force a rollback.
Dim pBlech As SqlParameter = cmd.Parameters.Add("@blech", SqlDbType.Int)
pBlech.Direction = ParameterDirection.Input
pBlech.Value = 2
Try
cmd.ExecuteNonQuery()
Catch ex As Exception
tran.Rollback()
Trace.WriteLine(ex.ToString)
Finally
con.Close()
End Try


Exit Sub

End Sub

End Module
 
To see if SQL server is rolling back the transaction, SQL profiler needs to
be run while this code is being run, to see exactly what statements are
being executed...
 
Back
Top