长.NET事务导致问题 [英] Long .NET transaction causing problem

问题描述

ASP.Net 4.0网站，SQL Server 2008



我有一些代码在一个网站中，使用.Net代码读取，更新并将记录插入到各种表中，并使用它进行交易。我这样做是因为我们在DAL级别的所有DLL都已经完成了对数据库的调用，并且整个更新可以在循环中多次发生。



所以我开始交易，循环一堆数据，选择，更新和插入记录。一旦循环完成，我要么提交事务要么回滚，如果我发现一路上有问题。



问题是，如果另一个用户是登录到网站，并访问相同的表我从任一用户得到错误，用户将发生异常，可能是：

此SqlTransaction已完成;它已不再可用。

或

交易（进程ID 57）在锁定资源上与另一个进程陷入僵局......



我的猜测似乎是一个用户以某种方式访问​​另一个连接或其他东西。



如果我的开放交易可能需要很长时间，如果设置正确，这一切是否都有效？或者这不是一个好的方式去（不是首选的答案;））。



我知道你还没有看到任何代码，但有相当多的对它来说，我只是想要一个普遍的意见，只要我所说的是有道理的。



谢谢



Julian



我尝试过：



谷歌搜索，同事，不同的环境

Hi,

ASP.Net 4.0 website, SQL Server 2008

I have some code in a website that reads, updates and inserts records into various tables with a transaction around it using .Net code. I am doing it this way as all the DLLs we have at a DAL level are already done for the calls to the database, and the whole updating can happen in a loop many times.

So I begin the transaction, loop through a bunch of data, selecting, updating and inserting records. Once the loop finishes, I either commit the transaction or roll it back if I discover that there was a problem along the way.

The problem is that if another user is logged on to the website, and accesses the same tables I get errors from either user and either user will have an exception occur which might be:
This SqlTransaction has completed; it is no longer usable.
or
Transaction (Process ID 57) was deadlocked on lock resources with another process ...

My guess is that it seems as though one user is somehow accessing another connection or something.

If my open transaction could potentially take a long time, should it all work if set up properly do you think? Or is this not a good way to go anyway (not the preferred answer ;) ).

I know you haven't seen any code, but there's quite a bit to it, and I just wanted a general opinion, as long as what I've said makes sense.

Thanks

Julian

What I have tried:

google searches, colleagues, different environments

推荐答案

大家好，



谢谢大家的建议。输入我发现的任何类型总是很有用，即使它只是规定了事情。



我相信我已经解决了这个问题。



第一个问题是事务期间的表锁定，这已通过在表上应用合适的索引来解决，以确保不会发生表扫描，从而锁定表。在存储过程中也需要FORCESEEK的一些用途。



当这个被解决时，如所怀疑的那样，在一个会话中设置了一个共享参数，然后在其他会话中共享。此参数是发生事务的代码的指示符（布尔值）。其他会话将此布尔值选为True，因为它们应为False。



这完全是我的错。它之前引起了我的注意，我应该早点意识到。但值得注意的是，如果其他人遇到这种情况。



我们使用VB.Net并且在DLL中是一个模块（不是每个人最喜欢的编码方式） ）使用私有布尔值，一次由一个会话设置可能被另一个会话（！）拾取。模块中的其他所有内容都包含在黑盒功能中。除非在IIS中有一个我不知道的设置来覆盖这个共享参数功能。



无论如何，我不想让这个案例打开，因为我相信我解决了这个问题。我目前负载测试似乎都很好。



Julian

Hi all,

Thank you all for your suggestions. It is always useful to have input of any kind I find, even if it just rules things out.

I believe I have solved the issue.

The first issue was table locking during the transaction, this has been resolved by applying suitable indexes on tables to make sure a table scan isn't happening and hence locking the table. Some uses of FORCESEEK were also required in stored procedures.

When this was resolved, as suspected, there was a 'sharing' of a parameter that was set in one session, and then shared across other sessions. This parameter being an indicator to the code that there was a transaction occurring (a Boolean). Other sessions picked up this Boolean as 'True' when they should have been 'False'.

That was completely my fault. It has caught me out before, and I should have realised sooner. But it is worth noting here in case anyone else experiences this.

We are stuck using VB.Net and in the DLL is a Module (not everyone's favorite way of coding) with a Private Boolean, that once set by one session can potentially be picked up by another session (!). Everything else in the Module is 'contained' in black box functions. Unless there is a setting I don't know about in IIS to override this sharing parameter feature.

Anyway, didn't want to leave this case open as I believe I have this resolved. Am currently Load Testing and all seems good.

Julian

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