Reputation: 15817
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:
I am looking specifically for an anwer to question 2.
Upvotes: 0
Views: 4753
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