CODEWITHSUNDEEP
sql-servertransactionssnapshotserializable
mos
mos

Reputation: 157

Implications of using Serializable and Snapshot isolation concurrently

Are there any issues using SNAPSHOT isolation to read data consistently for viewing without locking, blocking or dirty/phantom reads, while a separate process is processing continuous incoming data in serializable transactions?

We need readers (guaranteed read-only: web data sync, real-time monitoring views, etc) to be able to read consistent data, without being blocked, or blocking the updates. We were using SNAPSHOT for everything, but had too many consistency failures so switched the updating process to SERIALIZABLE.

I've read about but am not totally clear as to the impacts of using different isolation levels concurrently. I've seen the lock compatibility matrix, and read various info. It seems ok, but I'd really appreciate some wise guidance from people with practical experience about any major pitfalls.

Are there any issues using Snapshot isolation for the readers while SERIALIZABLE transactions are writing? Are there circumstances it will block a SERIALIZABLE transaction? Is there a benefit to using SNAPSHOT vs READ COMMITTED (with READ_COMMITTED_SNAPSHOT ON)?

Thanks, any assistance greatly appreciated :-)

Upvotes: 4

Views: 1795

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294397

Reads performed under SNAPSHOT isolation level read any modified data from the version store. As such they are affected only by writes. Writes behave identically under all isolation levels. Therefore SNAPSHOT reads behave the same way no matter the isolation level of the concurent transactions.

READ_COMMITTED_SNAPSHOT ON makes READ COMMITTED act as SNAPSHOT. In effect, it is SNAPSHOT: the READ_COMMITTED_SNAPSHOT was provided as a quick way to port applications to SNAPSHOT w/o code changes. So everything said on the first paragraph applies.

Upvotes: 4

Related Questions