7

For SNAPSHOt isolation level in SQL Server 2008 R2, the following is mentioned in MSDN ADO.Net documentation:

Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server.

There is no mention of whether writes will block writes, when both transactions are in SNAPSHOT isolation mode. So my question is as follows: Will writes in a SNAPSHOT transaction1 block writes to same tables in another SNAPHOT transaction2?

LATEST UPDATE

After doing a lot of thinking on my question, I am coming to a conclusion as mentioned in paragraph below. Hope others can throw more light on this.

There is no relational database in which writes do NOT block writes. In other words, writes will always block writes. Writes would include statements like INSERT or UPDATE or DELETE. This is true no matter which isolation level you use, since all relational databases need to implement data consistency, when multiple writes are happening in database. Of course, the simultaneous writes need to be conflicting ( as in inserting into the same table or updating the same row/s) for this blocking to occur.

Sunil
  • 20,653
  • 28
  • 112
  • 197
  • See Also : http://stackoverflow.com/questions/1044164/using-isolationlevel-snapshot-but-db-is-still-locking – StuartLC Jun 20 '13 at 12:28

2 Answers2

5

Ligos is actually incorrect - if two separate transactions are trying to update the same record with Snapshot on, transaction 2 WILL be blocked until transaction 1 releases the lock. Then, and ONLY then, will you get error 3960. I realize this thread is over 2 years old, but I wanted to avoid miss-information being out there.

Even the link Ligos references says the exact same thing I am mentioning above (check out the last non-red paragraph)

Write vs. Write will only not be blocked if the two records (ie. rows) trying to be updated are different

Scott
  • 159
  • 2
  • 5
3

No. They will not block. Instead, the UPDATE command in trans2 will fail with error number 3960.

Because of how SNAPSHOT isolation level works, any UPDATE command may fail. The only way you can tell is to catch and handle error 3960 (it is called optimistic concurrency because you don't expect this situation to happen very often).

I ended up testing this empirically, because it's not entirely obvious from the documentation. This blog post illustrates it nicely though.

Assumption: both trans1 and trans2 are UPDATEing the same row in the same table. Updating two different rows should work just fine.

ligos
  • 4,256
  • 2
  • 25
  • 34
  • igos - Thanks for your reply. How about two SNAPSHOT transactions with INSERTS? Both are trying to insert new rows. They will still block each other? – Sunil Oct 02 '12 at 14:18
  • @Sunil `INSERT`s should not block each other (assuming you're not trying to insert the same primary key) – ligos Oct 02 '12 at 23:43
  • @Sunil after reading your update, `INSERT`s do not deadlock each other (assuming different primary keys). They may block momentarily as a new identity key is created and data actually written, but they don't deadlock or block waiting for a table lock. `UPDATE`s do. I didn't try to test `DELETE`s (because I rarely use them), but I suspect they will be similar to `UPDATE`. – ligos Oct 02 '12 at 23:47
  • ligos - Thanks again for your reply. I think blocking is insignificant when inserting one or a few rows. But when inserting thousands, like say 5,000 rows into a table, it will block other Inserts/Updates when in SNAPSHOT isolation level. That's what we observed on our end. – Sunil Oct 03 '12 at 02:35
  • Blog post link is now dead sadly. – MgSam May 07 '19 at 21:42