IsolationLevel 设置为 Serialized 的 TransactionScope 正在锁定所有 SQL SELECT
我正在使用 PowerShell 事务；它创建一个 IsolationLevel 为 Serialized 的 CommittableTransaction。问题是，当我在此上下文中执行事务时，除了执行事务的连接之外，任何连接上受该事务影响的表上的所有 SELECT 都会被阻止。我可以从事务中执行获取操作，但不能从其他地方执行获取操作。这包括 SSMS 和其他 cmdlet 执行。这是预期的行为吗？好像我错过了一些东西......
PS 脚本：
Start-Transaction
Add-Something -UseTransaction
Get-Something #hangs here until timeout
Add-Something -UseTransaction
Undo-Transaction
I'm using PowerShell transactions; which create a CommittableTransaction with an IsolationLevel of Serializable. The problem is that when I am executing a Transaction in this context all SELECTs are blocked on the tables affected by the transaction on any connection besides the one executing the transaction. I can perform gets from within the transaction but not anywhere else. This includes SSMS and other cmdlets executions. Is this expected behavior? Seems like I'm missing something...
PS Script:
Start-Transaction
Add-Something -UseTransaction
Get-Something #hangs here until timeout
Add-Something -UseTransaction
Undo-Transaction
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
可序列化事务将阻止在此隔离下扫描的范围上的任何更新。序列化隔离级别本身不会阻止读取。如果您发现读取被阻止，则一定有其他原因在起作用，这取决于您在这些脚本中执行的操作。
Serializable transactions will block any updates on the ranges scanned under this isolation. By itself the serialization isolation level does not block reads. If you find that reads are blocked, something else must be at play and it depends on what you do in those scripts.
听起来好像您的数据库已设置 ALLOW_SNAPSHOT_ISOLATION=OFF。此设置控制数据库使用的并发机制：
ALLOW_SNAPSHOT_ISOLATION=OFF：这是 SQL Server 的传统模式，具有基于锁的并发。这种模式可能会导致锁定问题。
ALLOW_SNAPSHOT_ISOLATION=ON：此功能自 SQL Server 2005 起可用，并使用 MVCC，非常漂亮类似于 Oracle 或 Postgresql 的做法。这对于并发性更好，因为读取器不会阻止写入器，写入器也不会阻止读取器。
请注意，这两种模式的行为方式不同，因此您必须对事务进行编码以采用一种模式或另一种模式。
Sounds as if your database has ALLOW_SNAPSHOT_ISOLATION=OFF. This setting controls the concurrency mechanism used by the database:
ALLOW_SNAPSHOT_ISOLATION=OFF: This is the traditional mode of SQL Server, with lock based concurrency. This mode may lead to locking problems.
ALLOW_SNAPSHOT_ISOLATION=ON: This is avaliable since SQL Server 2005, and uses MVCC, pretty similar to what Oracle or Postgresql do. This is better for concurrency as readers do not block writers and writers do not block readers.
Note that this two modes do not behave in the same way, so you must code your transactions for assuming one mode or the other.