CODEWITHSUNDEEP
vb.nettransactions
w0051977
w0051977

Reputation: 15817

VB.NET - Transaction rollback

What happens if a transaction is neither committed nor rolled back. I am looking for answers for SQL Server and/or Oracle. Please see the code below:

Public Sub TransactionTest()
        Try
            Dim intCount As Integer
            Dim sql As SqlTransaction
            Dim objCon As New SqlConnection("Data Source=IANSCOMPUTER;Initial Catalog=Test;Integrated Security=True;MultipleActiveResultSets=true")
            objCon.Open()
            Dim trans As SqlTransaction
            trans = objCon.BeginTransaction
            Dim paramValues(0) As SqlParameter
            paramValues(0) = New SqlParameter("@ID", 1)
            Using (objCon)
                intCount = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE person SET URN=1 WHERE ID2=@ID", paramValues)
                paramValues(0) = New SqlParameter("@ID", 2)
                intCount = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE person SET URN=2 WHERE ID2=@ID", paramValues)
                paramValues(0) = New SqlParameter("@ID", 3)
                intCount = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE person SET URN=3 WHERE ID2=@ID", paramValues)
            End Using
        Catch ex As Exception
            'I do not swallow transactions
        End Try
    End Sub

Notice that it is neither committed nor rolled back. In the case above it appears to roll back.

I have noticed that on my SQL Server 2005 Express server at home that SQL Studio Manager hangs when running a query directly in the console until the program above finishes. In my work environment this is not the case i.e. you can run queries simultaneously. Is this because of the isolation level? Therefore I have two questions:

  1. What happens if a transaction is neither committed nor rolled back. I have read articles like this: What happens if you don't commit transaction in a database (say SQL Server). Can I assume that the transaction is rolled back in SQL Server and Oracle?
  2. Why does SQL server hang in one environment when a transaction is active and in another environment it does not?

I am looking specifically for an anwer to question 2.

Upvotes: 0

Views: 4753

Answers (1)

zmbq
zmbq

Reputation: 39039

If you don't commit the transaction, the database will roll it back when you close the connection, assuming something bad happened to your code. This is how any serious database behaves.

As for your second question, I guess it has to do with locking, but it's quite hard to say without knowing more about your environments and who uses them.

Upvotes: 1

Related Questions