VB.NET - 事务回滚 [英] VB.NET - Transaction rollback

问题描述

如果一个事务既没有提交也没有回滚会发生什么.我正在寻找 SQL Server 和/或 Oracle 的答案.请看下面的代码:

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.

我注意到，在我家里的 SQL Server 2005 Express 服务器上，SQL Studio 管理器在直接在控制台中运行查询时会挂起，直到上述程序完成.在我的工作环境中，情况并非如此，即您可以同时运行查询.这是因为隔离级别吗?因此我有两个问题:

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. 如果事务既没有提交也没有回滚会发生什么.我读过这样的文章:如果您不在数据库(例如 SQL Server)中提交事务会发生什么.我可以假设事务在 SQL Server 和 Oracle 中回滚吗?
2. 当事务处于活动状态时，为什么 SQL Server 在一个环境中会挂起，而在另一种环境中则不会?

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?

我正在寻找问题 2 的答案.

I am looking specifically for an anwer to question 2.

推荐答案

如果您不提交事务，则数据库会在您关闭连接时将其回滚，假设您的代码发生了错误.这就是任何严肃数据库的行为方式.

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.

这篇关于VB.NET - 事务回滚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！