7

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.

7

First of all I agree completely with Aaron Bertrand when he suggested in the comments you move the SQL into a stored procedure. This is also the clear winner as far as "the proper way to wrap a set of SQL operations in a .NET program" by the way.

Since you're worried about managing code in two places you should check out database projects in Visual Studio. I've been using these for a while now, and it works really well. You even get some handy, albeit limited, refactoring tools when you go this route.

I see several problems with your Visual Basic code:

  • You aren't disposing your SqlCommand object properly.
  • You aren't disposing your SqlDataReader object properly.
  • You aren't actually doing anything with the data returned from the reader so it is superfluous.
  • You are reusing the variable cmd when disposition = True, instead of using another variable. I'm not sure, but I believe, this will prevent the transaction from rolling back the initial update. Regardless this isn't a best practice in my opinion.
  • You should probably move away from using the .AddWithValue() method. As described in this blog post, you can run into problems due to implicit conversions.

Also the reason why closing the SqlDataReader worked as suggested by Mister Magoo in the comments and confirmed in your comment was because the reader is preventing anything else from happening on the connection until it is closed. Here is a quote from the documentation:

While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.

For more/related shenanigans that can be caused by the way you're using/abusing the reader check out this answer on Stack Overflow.


A couple more thoughts based on your comment:

One easy way to populate a database project that is created after a database already exists is to use the "Schema Compare" functionality. In my version of Visual Studio this functionality is found in the menu bar under: Tools --> SQL Server --> New Schema Comparison. You may need to download the latest version of the SSDT(free) for your version of Visual Studio to get this functionality.

I agree that you probably shouldn't dedicate the next few weeks just to moving your inline SQL into stored procedures. As I've mentioned in the past blocking updates while you refactor core elements of your application is rarely the best plan. You can choose to move all new data access into stored procedures, and decide to move SQL into stored procedures when you have to touch the inline SQL. This will be minimally invasive, spread the work out over time, and allow you to systematically improve your code base (VB.NET and SQL).

  • You're right, of course. As per the comments "below the fold" on the original question, it was, indeed, the SQLDataReader that was holding back the transaction from completing. This was my first time in .NET/SQL. Since then, I've gone through all my classes and closed all my readers and disposed my commands. Interesting comment on the .AddWithValue() method. Also, I had no idea about database projects in VS. I'll have to look into that, though it's probably far too late to refactor everything as a stored procedure. – David Krider Oct 8 '15 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.