IsolationLevel 设置为 Serializable 的 TransactionScope 正在锁定所有 SQL SELECT [英] TransactionScope with IsolationLevel set to Serializable is locking all SQL SELECTs

问题描述

我正在使用 PowerShell 事务；它创建了一个 CommittableTransaction，其 IsolationLevel 为 Serializable.问题是，当我在此上下文中执行事务时，除了执行事务的连接之外，在任何连接上受事务影响的表上的所有 SELECT 都被阻塞.我可以从事务中执行获取，但不能在其他任何地方执行.这包括 SSMS 和其他 cmdlet 执行.这是预期的行为吗?好像我错过了什么......

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 脚本:

Start-Transaction
Add-Something -UseTransaction 
Get-Something #hangs here until timeout
Add-Something -UseTransaction
Undo-Transaction

推荐答案

可序列化事务将阻止在此隔离下扫描的范围上的任何更新.序列化隔离级别本身不会阻止读取.如果您发现读取被阻止，则一定有其他原因在起作用，这取决于您在这些脚本中执行的操作.

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.

这篇关于IsolationLevel 设置为 Serializable 的 TransactionScope 正在锁定所有 SQL SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！