使用IsolationLevel.Snapshot但DB仍锁定 [英] Using IsolationLevel.Snapshot but DB is still locking
问题描述
我是一个团队构建一个基于ADO.NET网站的一部分。有时候，我们有几个开发商和自动化测试工具，同时工作数据库的开发副本。
I'm part of a team building an ADO.NET based web-site. We sometimes have several developers and an automated testing tool working simultaneously a development copy of the database.
我们使用快照隔离级别，其中，尽我所知，使用乐观并发：而不是锁定，并希望在最好的，如果你试图提交事务，如果受影响的行已经改变了抛出一个异常另一方在交易过程中。
We use snapshot isolation level, which, to the best of my knowledge, uses optimistic concurrency: rather than locking, it hopes for the best and throws an exception if you try to commit a transaction if the affected rows have been altered by another party during the transaction.
要使用快照隔离级别，我们使用：
To use snapshot isolation level we use:
ALTER DATABASE <database name>
SET ALLOW_SNAPSHOT_ISOLATION ON;
和在C＃：</ P>
and in C#:
Transaction = SqlConnection.BeginTransaction(IsolationLevel.Snapshot);
注意的IsolationLevel快照是不一样的READCOMMITTED快照，这是我们以前也尝试过，但目前没有使用。
Note that IsolationLevel Snapshot isn't the same as ReadCommitted Snapshot, which we've also tried, but are not currently using.
当开发商之一进入调试模式并暂停.NET应用程序，他们将与一个活动事务的连接，同时调试。现在，我希望这不会是一个问题 - 毕竟，所有的交易都使用快照隔离级别，因此，当一个交易被暂停，其他事务应该能够正常进行，因为暂停交易不持有任何锁。当然，暂停事务完成时，它很可能检测冲突;但是这是可以接受的，只要其他的开发和自动化测试可以畅通无阻。
When one of the developers enters debug mode and pauses the .NET app, they will hold a connection with an active transaction while debugging. Now, I'd expect this not to be a problem - after all, all transactions are using snapshot isolation level, so while one transaction is paused, other transactions should be able to proceed normally since the paused transaction isn't holding any locks. Of course, when the paused transaction completes, it is likely to detect a conflict; but that's acceptable so long as other developers and the automated tests can proceed unhindered.
然而，在实践中，当一个人停止一个事务的同时调试，所有其他的DB用户试图访问相同的行被阻塞尽管使用快照隔离级别
However, in practice, when one person halts a transaction while debugging, all other DB users attempting to access the same rows are blocked despite using snapshot isolation level.
有谁知道为什么发生这种情况，和/或我如何能实现真正的乐观（非阻塞）并发？
Does anybody know why this occurs, and/or how I can achieve true optimistic (non-blocking) concurrency?
的决议（不幸为我一个人）的：莱姆斯Rusanu 指出，作家总是阻止其他作家;这是后盾 MSDN - 它并没有完全出来，这么说，但只以往提到避免读写锁。总之，我想要的行为在SQL Server中未实现。
The resolution (an unfortunate one for me): Remus Rusanu noted that writers always block other writers; this is backed up by MSDN - it doesn't quite come out and say so, but only ever mentions avoiding reader-writer locks. In short, the behavior I want isn't implemented in SQL Server.
推荐答案
快照隔离级别的影响，像所有的隔离级别，只读取。写操作仍然阻挡对方。如果你认为你所看到的是读取的块，那么你应该进一步调查，并检查了资源类型和资源名称上发生阻塞（wait_type和wait_resource在<一个href="http://msdn.microsoft.com/en-us/library/ms177648%28SQL.90%29.aspx">sys.dm_exec_requests).
SNAPSHOT isolation level affects, like all isolation levels, only reads. Writes are still blocking each other. If you believe that what you see are read blocks, then you should investigate further and check out the resource types and resource names on which blocking occurs (wait_type and wait_resource in sys.dm_exec_requests).
我不会建议在做，以支持包括开发人员在调试盯着就结束分钟的情景code的变化。如果您认为这种情况可能产生重复（即客户端挂起），然后是一个不同的故事。为了达到你想要什么，你必须尽量减少写入，并在交易结束时执行所有写操作，在一个单一的调用返回之前提交。这样，没有客户可以持有X锁很长一段时间（按住X锁不能挂）。实际上，这是pretty的辛苦拉断，并要求对部分开发商有很多的学科，他们怎么写数据访问code。
I wouldn't advise in making code changes in order to support a scenario that involves developers staring at debugger for minutes on end. If you believe that this scenario can repeat in production (ie. client hangs) then is a different story. To achieve what you want you must minimize writes and perform all writes at the end of transaction, in one single call that commits before return. This way no client can hold X locks for a long time (cannot hang while holding X locks). In practice this is pretty hard to pull off and requires a lot of discipline on the part of developers in how they write the data access code.
这篇关于使用IsolationLevel.Snapshot但DB仍锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
