views:

274

answers:

2

Working with SQL server with isolation level "read committed snapshot", we routinely write data to the database and read it further on in the context of the same transaction. Usually when reading from the database we see our own uncommitted changes but only committed changes made by others. We assumed that this is the expected behavior .

We now found that in some relatively rare cases we don't see the values we've written - only previously committed values.

Any ideas what might be causing the inconsistency?

A: 

Ther are some good articles on what could go wrong. Perhaps they might help

Craig Freedman, Serializable vs. Snapshot Isolation Level The black/white marble analogy

Hugo Kornelis, 4 articles "Snapshot isolation: A threat for integrity": DRI/FKs silently changing to different isolation levels

gbn
Nice articles, but they don't discuss the question of reading data modified in the same transaction.
Youval Bronicki
The 1st article mentions black/white marbles. Are you rolling back? The 2nd four mention FKs changing isolation level. In the same TXN, if you are reading your own committed pre-change data then has it been rolled back? Or your isolation level has changed... which may confuse things so you read your own pre-change committed data
gbn
My problems are not related to roll back (see separate answer)
Youval Bronicki
A: 

It turns out that this is a known inconsistency in SQL Server: when you read from the database you usually see your own transaction's uncommitted changes (in all isolation levels), but this is not always guaranteed. The work around in my case was to use with (readuncommitted), trusting an application-level locking mechanism that prevents concurrent modifications of the same data.

I received the same answer from two independent database experts. Unfortunately, I didn't find any written reference to the issue.

Youval Bronicki