Sql Server and transaction solation level snapshot
I’ve worked with some project that has a rather busy database. In order to avoid locking conflicts it was decided to use SNAPSHOT isolation level. Now I wasn’t very confident about this since SNAPSHOT is touted to be a silver-bullet for all kinds of problems. Now I’ve changed my mind and I’m happy to tell you why.
1. Whenever read is made to the database inside a transaction it is done against snapshot.
2. Because snapshot is stored in tempdb-database reading does not interfere with other operations like INSERTs and UPDATEs
There is some things to consider though.
1. The size of tempdb is going to grow, you’ll need storage space and a lot
2. You should consider placing tempdb to separate disk from the actual database and make sure that disk is FAST
3. You must explicitly start a transaction with isolation level set to snapshot whenever you make SELECT operation. If you don’t start transaction or you start it with wrong isolation level you’ll end up reading the actual data tables and keep causing share-locks. This negates the positive effects of SNAPSHOT
Currently I’m doing little bit performance testing with this snapshot schema. If I come up with some weird results I’ll write another blog entry about them. Otherwise you can assume that I’m happy about this feature