这个sqltransaction已经完成;它不再可用。连接仍然打开 [英] This sqltransaction has completed; it is no longer usable. Connection still open

问题描述

大家好，



我正在尝试测试两个sqlconnections。我故意在第二个sql命令文本上添加一些错误以强制它回滚所有内容，但是当它遇到第二个catch块中的TransHQ.Rollback（）时出现错误。我知道僵尸交易的事情，但我的两个连接仍然是开放的。也许我错过了什么。

这是我的代码：

Hi everyone,

I'm having two sqlconnections that I'm trying to test. I purposely put some error on the second sql commandtext to force it to rollback everything, but I'm having an error when it hits the TransHQ.Rollback() in the second catch block. I knew about the "zombie transaction" thing, but all of my two connections are still open. Maybe I missed something.
This is my code:

Try
    sqloCnHQ.Open()
    TransHQ = sqloCnHQ.BeginTransaction()
    cmdHQ.Transaction = TransHQ
    cmdHQ.Connection = sqloCnHQ
    cmdHQ.CommandText = SqlHQ.ToString()
    cmdHQ.CommandTimeout = 0
    cmdHQ.ExecuteNonQuery()
    TransHQ.Commit()
Catch ex As Exception
    Application.DoEvents()
    TransHQ.Rollback()
    ErrorHandling("Sync Transactions " & cmdHQ.ToString)
    SyncMessageOPT += "Sync Transactions " + cmdHQ.ToString + Environment.NewLine
    sqloCnHQ.Dispose()
    Err.Clear()
    Exit Sub
End Try


Try
    sqloCnLocal.Open()
    TransLocal = sqloCnLocal.BeginTransaction()
    cmdLocal.Transaction = TransLocal
    cmdLocal.Connection = sqloCnLocal
    cmdLocal.CommandText = SqlLocal.ToString() + "xxx"
    cmdLocal.CommandTimeout = 0
    cmdLocal.ExecuteNonQuery()
    TransLocal.Commit()
Catch ex As Exception
    Application.DoEvents()
    TransHQ.Rollback()
    TransLocal.Rollback()
    ErrorHandling("Sync Transactions " & cmdLocal.ToString)
    SyncMessageOPT += "Sync Transactions " + cmdLocal.ToString + Environment.NewLine
    sqloCnHQ.Dispose()
    sqloCnLocal.Dispose()
    Err.Clear()
    Exit Sub
End Try

sqloCnHQ.Dispose()
sqloCnLocal.Dispose()

提前致谢！

Thanks in advance!

推荐答案

感谢您的想法。我设法做对了，但我不确定我做的是否有缺点。我只是在第二个ExecuteNonQuery之后放两个提交语句。

Hi,

Thanks for the idea. I manage to get it right, but I'm not sure if what I did will have a drawback.I simply put the two commit statements after the second ExecuteNonQuery.

sqloCnLocal.Open()
TransLocal = sqloCnLocal.BeginTransaction()
cmdLocal.Transaction = TransLocal
cmdLocal.Connection = sqloCnLocal
cmdLocal.CommandText = SqlLocal.ToString() + "xxx"
cmdLocal.CommandTimeout = 0
cmdLocal.ExecuteNonQuery()

TransHQ.Commit()
TransLocal.Commit()

因此，在整个执行完成之前，不要先提交。



感谢您的帮助！

So the idea is not to commit first until the whole execution finished.

Thanks for the great help!

这篇关于这个sqltransaction已经完成;它不再可用。连接仍然打开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！