奇怪的 SQL2005 问题.“SqlConnection 不支持并行事务" [英] Strange SQL2005 problem. "SqlConnection does not support parallel transactions"

问题描述

我有一个问题，似乎是由于死锁情况造成的.我们现在正在寻找问题的根源，但同时我们想重新启动服务器并让客户继续前进.

I have a problem that seems like its a result of a deadlock-situation. Whe are now searching for the root of the problem but meantime we wanted to restart the server and get the customer going.

现在每次我们启动程序时它只会说SqlConnection 不支持并行事务".我们没有更改程序中的任何内容，它已编译并在客户服务器上，但在可能死锁"的情况下，它想再次上线.

And now everytime we start the program it just says "SqlConnection does not support parallel transactions". We have not changed anything in the program, its compiled and on the customers server, but after the "possible deadlock"-situation it want go online again.

我们有 7 个客户端(计算机)运行该程序，每个客户端都在与本地服务器上的 webservice 通信，而 webservice 正在与 sql-server(与 webserver 相同的机器)通信.

We have 7 clients (computers) running the program, each client is talking to a webservice on a local server, and the webservice is talking to the sql-server (same machine as webserver).

我们已经重新启动了 sql-server 和 iis-server，但没有重新启动服务器，因为服务器上正在运行其他重要的服务，所以这是我们做的最后一件事.我们不能在管理选项卡中设置锁或任何东西.

We have restarted both the sql-server and the iis-server, but not rebooted the server because of other important services running on the server so its the last thing we do. We can se no locks or anything in the management tab.

所以我的问题是，为什么SqlConnection 不支持并行事务"错误一次又一次出现而没有更改程序中的任何内容，并且它仍然存在于 sql-restart 之间.

So my question is, why does the "SqlConnection does not support parallel transactions" error comming from one time to another without changing anything in the program and it still lives between sql-restart.

它似乎发生在程序启动时的第一个数据库请求中.

It seems like it happens at the first db-request the program does when it start.

如果您需要更多信息，请询问.我很困惑...

If you need more information just ask. Im puzzled...

更多信息:我不认为我有长时间"运行的交易.这种情况通常是我有一个包含 20-100 行 (ContractRows) 的数据集，我将在 tableAdapter 上执行 .Update.我还循环遍历那 20-100 行，对于其中一些我会创建 ad-hook-sql-querys(例如，如果租用的产品被标记为已返回，我会创建一个 sql-query 以将产品标记为在数据库中返回)

More information: I dont think I have "long" running transactions. The scenario is often that I have a dataset with 20-100 rows (ContractRows) in that Ill do a .Update on the tableAdapter. I also loop throug those 20-100 rows and for some of them Ill create ad-hook-sql-querys (for example if a rented product is marked as returned I create a sql-query to mark the product as returned in the database)

所以我这样做非常简单:

So I do this very simplified:

Create objTransactionObject
Create objtableadapter (objTransactionObject)
for each row in contractDS.contractrows
  if row.isreturned then
    strSQL &= "update product set instock=1 where prodid=" & row.productid & vbcrlf
 End if
next
objtableadapter.update(contractDS)
objData.ExecuteQuery(strSQL, objTransactionObject)    
if succsesfull 
  objtransactionobject.commit
else
  objtransactionobject.rollback
end if
objTran.Dispose()

然后我根据它是否顺利进行提交或回滚.

And then Im doing commit or rollback depending on if It went well or not.

没有一个答案可以解决问题，但我会感谢您提供的故障排除指示.

None of the answers have solved the problem, but I'll thank you for the good trouble shooting pointers.

SqlConnection 不支持并行事务"突然消失了，现在 sql-server 每天只宕机"4-5 次，我猜这是一个死锁，但我没有正确的知识来找出答案并且目前缺少可以为我监控的 sql 专家.我只是重新启动了 sql-server，一切又恢复了.10 次中有 1 次我也必须重新启动计算机.这真的困扰着我(当然还有我的客户).

The "SqlConnection does not support parallel transactions" dissapeared suddenly and now the sql-server just "goes down" 4-5 times a day, I guess its a deadlock that does that but I have not the right knowledge to find out and are short on sql-experts who can monitor this for me at the moment. I just restart the sql-server and everything works again. 1 of 10 times I also have to restart the computer. Its really bugging me (and my customers of course).

任何认识在瑞典(或世界各地，讲英语)分析死锁故障或其他 sql 问题的良好知识的人都可以随时与我联系.我知道这不是一个联系网站，但我想提出这个问题，因为我已经没有选择了，我花了 3 天三夜优化客户，以确保我们关闭联系并且不要在那里做太多愚蠢的事情.没有运气.

Anyone knowing a person with good knowledge in analyzing troubles with deadlocks or other sql problems in sweden (or everywhere in the world,english speaking) are free to contact me. I know this is'nt a contact site but I take my chanse to ask the question because I have run out of options, I have spent 3 days and nights optimizing the clients to be sure we close connections and dont do too much stupid things there. Without luck.

推荐答案

您似乎正在共享连接并在同一个打开的连接上创建新事务(这是您看到的异常的并行部分).

It seems to be that you are sharing connections and creating new transactions on the same open connection (this is the parallel part of the exception you are seeing).

你的例子似乎支持这一点，因为你没有提到你是如何获得连接的.

Your example seems to support this as you have no mention of how you acquire the connection in it.

您应该检查您的代码并确保您只打开一个连接，然后在完成后处理它(并且无论如何，使用 using 语句确保您关闭连接)，好像你要在某处打开一个.

You should do a review of your code and make sure that you are only opening a connection and then disposing of it when you are done (and by all means, use the using statement to make sure that you close the connection), as it seems like you are leaving one open somewhere.

这篇关于奇怪的 SQL2005 问题.“SqlConnection 不支持并行事务"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！