Do writes in SNAPSHOT isolation level block writes in another SNAPSHOT transaction in SQL Server 2008 R2

Question

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.

Answer 1

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

Answer 2

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.