I'm trying to implement transactions in my application. I'm just trying to implement it like the example shown in the BeginTransaction() documentation.
Public Shared Sub Process(wwid As String, trade_id As Integer, disposition As Boolean)
Dim q As String
Dim cmd, cmd_select As SqlCommand
Dim reader As SqlDataReader
Dim trans As SqlTransaction
Dim user_id As Integer = User.CheckAuthentication(wwid)
If user_id > 0 Then
Using conn As New SqlConnection(CNGDB)
conn.Open()
'1. ReadUncommitted
'2. ReadCommitted
'3. RepeatableRead
'4. Serializable
'5. Snapshot
trans = conn.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted)
Try
q = "UPDATE Trades SET Disposition = @disposition, FinalizedAt = @finalized_at" & _
" WHERE TradeID = @trade_id"
cmd = New SqlCommand(q, conn)
cmd.Transaction = trans
cmd.Parameters.AddWithValue("@disposition", disposition)
cmd.Parameters.AddWithValue("@finalized_at", DateTime.Now)
cmd.Parameters.AddWithValue("@trade_id", trade_id)
cmd.ExecuteNonQuery()
If disposition = True Then
q = "SELECT Ownership_OwnershipID, Recipient_UserID FROM Trades" & _
" WHERE TradeID = @trade_id"
cmd_select = New SqlCommand(q, conn)
cmd_select.Transaction = trans
cmd_select.Parameters.AddWithValue("@trade_id", trade_id)
reader = cmd_select.ExecuteReader
reader.Read()
q = "UPDATE Ownerships SET User_UserID = @recipient_id" & _
" WHERE OwnershipID = @ownership_id"
cmd = New SqlCommand(q, conn)
cmd.Transaction = trans
cmd.Parameters.AddWithValue("@recipient_id", reader("Recipient_UserID"))
cmd.Parameters.AddWithValue("@ownership_id", reader("Ownership_OwnershipID"))
cmd.ExecuteNonQuery()
End If
trans.Commit()
Catch ex As Exception
Console.WriteLine("Commit Exception Type: {0}", ex.GetType())
Console.WriteLine(" Message: {0}", ex.Message)
Try
trans.Rollback()
Catch ex2 As Exception
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType())
Console.WriteLine(" Message: {0}", ex2.Message)
End Try
End Try
End Using
End If
End Sub
The problem is that whenever it hits the Commit(), I get the error:
Commit Exception Type: System.Data.SqlClient.SqlException
Message: The transaction operation cannot be performed because there are pending requests working on this transaction.
I expect the problem lies with the fact that I'm trying to read from the SELECT to populate the values in the second UPDATE, but I don't know how else it can be done. I've tried setting the database IsolationLevel to some other values, and it doesn't change anything.
I also found the TransactionScope class, and implemented its example for this code. However, when I tried to apply that technique to a more-complicated set of operations, it complained that it couldn't talk to my local computer's Distributed Transaction Server. I turned it on, and the DTS cancelled this other, more-complicated transaction, for some unspecified reason, and I didn't want to go down that rabbit hole.
Can anyone point out what I'm doing wrong in my code?
Further, what's the proper way to wrap a set of SQL operations in a .NET program? Maybe DTS is the better way to go, but this application will eventually live in an Azure database, and Azure doesn't seem to support DTS anyway, though there is some verbiage about "automatic escalation" of isolation levels, and I'm confused.
I briefly played with stored procedures, for the sake of speed, in this application, but found that it wasn't any faster, and decided I didn't want to try to maintain any code inside of the database. Rather, I wanted to keep my database-accessing library in Visual Studio along with my GUI front end application (in VB), for the sake of working on it in (mostly) one place.
