0

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
Adam Driscoll
  • 9,395
  • 9
  • 61
  • 104

2 Answers2

6

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Setting READ_COMMITTED_SNAPSHOT seems to fix the problem. I'm not sure if that would be a valid solution.... – Adam Driscoll Sep 02 '10 at 16:07
  • That means that you were doing updates in your PS script. Those updates lock the data, but now your SSMS and other queries run under snapshot when they request read committed (ie. =by default) so they can see the old version of the data (the image from before the PS update). – Remus Rusanu Sep 02 '10 at 16:09
  • Ok. So if it was possible to change the IsolationLevel to say, ReadCommited, would this change behavior? – Adam Driscoll Sep 02 '10 at 16:14
  • No. The behavior occurs because of updates in your PS script and updates are behaving the same under all isolation levels. – Remus Rusanu Sep 02 '10 at 17:07
2

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.

gpeche
  • 21,974
  • 5
  • 38
  • 51