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.
我是构建基于ADO.NET的网站的团队的一员。我们有时会有几个开发人员和一个自动化测试工具同时处理数据库的开发副本。
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;
and in C#:
在C＃中：
Transaction = SqlConnection.BeginTransaction(IsolationLevel.Snapshot);
Note that IsolationLevel Snapshot isn't the same as ReadCommitted Snapshot, which we've also tried, but are not currently using.
请注意，IsolationLevel Snapshot与ReadCommitted Snapshot不同，我们也尝试过，但目前还没有使用。
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.
当其中一个开发人员进入调试模式并暂停.NET应用程序时，他们将在调试时与活动事务保持连接。现在，我希望这不是一个问题 - 毕竟，所有事务都使用快照隔离级别，因此当一个事务暂停时，其他事务应该能够正常进行，因为暂停的事务没有持有任何锁。当然，当暂停的事务完成时，它很可能检测到冲突;但只要其他开发人员和自动化测试可以不受阻碍地进行，这是可以接受的。
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?
有谁知道为什么会这样，和/或我如何才能实现真正的乐观（非阻塞）并发？
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.
决议（对我来说不幸）：Remus Rusanu指出作家总是阻止其他作家;这是由MSDN支持的 - 它并没有完全说出来，但只是提到避免读写器锁。简而言之，我想要的行为并没有在SQL Server中实现。
2 个解决方案
#1
8
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).
与所有隔离级别一样，SNAPSHOT隔离级别仅影响读取。写作仍然相互阻碍。如果您认为所看到的是读取块，那么您应该进一步调查并检查发生阻塞的资源类型和资源名称（sys.dm_exec_requests中的wait_type和wait_resource）。
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.
我不建议进行代码更改，以支持涉及开发人员在几分钟内盯着调试器的场景。如果您认为这种情况可以在生产中重复（即客户端挂起），那么这是一个不同的故事。要实现您想要的目标，您必须在返回之前提交的单个调用中最小化写入并在事务结束时执行所有写入。这样，没有客户端可以长时间持有X锁（在持有X锁时无法挂起）。在实践中，这很难实现，并且开发人员在编写数据访问代码方面需要很多纪律。
#2
2
Have you looked at the locks when one developer pauses the transaction? Also, just turning on snapshot isolation level does not have much effect. Have you set ALLOW_SNAPSHOT_ISOLATION ON?
当一个开发人员暂停交易时你有没看过锁？此外，仅打开快照隔离级别没有太大影响。你有ALLOW_SNAPSHOT_ISOLATION吗？
Here are the steps:
以下是步骤：
ALTER DATABASE <databasename>
SET READ_COMMITTED_SNAPSHOT ON;
GO
ALTER DATABASE <database name>
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
After the database has been enabled for snapshot isolation, developers and users must then request that their transactions be run in this snapshot mode. This must be done before starting a transaction, either by a client-side directive on the ADO.NET transaction object or within their Transact-SQL query by using the following statement:
在为快照隔离启用数据库之后，开发人员和用户必须请求以此快照模式运行其事务。这必须在启动事务之前完成，可以通过ADO.NET事务对象上的客户端指令或使用以下语句在其Transact-SQL查询中完成：
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
Raj
拉吉
#1
8
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).
与所有隔离级别一样，SNAPSHOT隔离级别仅影响读取。写作仍然相互阻碍。如果您认为所看到的是读取块，那么您应该进一步调查并检查发生阻塞的资源类型和资源名称（sys.dm_exec_requests中的wait_type和wait_resource）。
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.
我不建议进行代码更改，以支持涉及开发人员在几分钟内盯着调试器的场景。如果您认为这种情况可以在生产中重复（即客户端挂起），那么这是一个不同的故事。要实现您想要的目标，您必须在返回之前提交的单个调用中最小化写入并在事务结束时执行所有写入。这样，没有客户端可以长时间持有X锁（在持有X锁时无法挂起）。在实践中，这很难实现，并且开发人员在编写数据访问代码方面需要很多纪律。
#2
2
Have you looked at the locks when one developer pauses the transaction? Also, just turning on snapshot isolation level does not have much effect. Have you set ALLOW_SNAPSHOT_ISOLATION ON?
当一个开发人员暂停交易时你有没看过锁？此外，仅打开快照隔离级别没有太大影响。你有ALLOW_SNAPSHOT_ISOLATION吗？
Here are the steps:
以下是步骤：
ALTER DATABASE <databasename>
SET READ_COMMITTED_SNAPSHOT ON;
GO
ALTER DATABASE <database name>
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
After the database has been enabled for snapshot isolation, developers and users must then request that their transactions be run in this snapshot mode. This must be done before starting a transaction, either by a client-side directive on the ADO.NET transaction object or within their Transact-SQL query by using the following statement:
在为快照隔离启用数据库之后，开发人员和用户必须请求以此快照模式运行其事务。这必须在启动事务之前完成，可以通过ADO.NET事务对象上的客户端指令或使用以下语句在其Transact-SQL查询中完成：
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
Raj
拉吉