What is Snapshot Isolation?

The isolation levels in DBMS are used to maintain concurrent execution of transactions without facing interruption through problems like dirty read, phantom read, and non-repeatable read. Snapshot isolation is one such isolation level that achieves the maximum level of concurrency. 

In this article we will understand two concepts:

• Snapshot isolation
• Row versioning technique

Snapshot Isolation level:

In lock-based isolation levels, the transaction reading data blocks the transaction trying to write the data and vice-versa, thus reducing the concurrency level and degrading the system performance. In order to overcome this problem snapshot isolation was introduced. Instead of acquiring key-range locks on the transactions, snapshot isolation makes use of row-versioning.

Row-versioning technique:

In SQL server row version can be defined as a data type that helps in version-stamping rows of a table with unique values. In row versioning, the SQL server stores all the older copies of a row that was modified by a transaction in the tempDB. If a particular row is modified multiple times then multiple older copies of the row would be stored in the tempdb for as long as they are required by any ongoing or forthcoming transaction. 

The name “snapshot” focuses on the fact that a similar copy of the database will be available to all the transaction queries, as it was at the beginning of the transaction. Row-versioning technique in snapshot isolation helps the SQL server to avoid locks thus allowing, the transaction reading data to not block the transaction modifying data and vice-versa. But a transaction writing data might block other transactions trying to write data at the same time, this is an update conflict that occurs in snapshot isolation. As a result of this update conflict, any one of the two transactions needs to be rollback or it needs to be killed.

Properties of Snapshot Isolation:

• No Conflict: Snapshot isolation follows optimistic concurrency control which states that transactions will not conflict with each other very likely.
• Prefix: Each node can view the transaction in a similar order.
• Internal Consistency: The read operations within a transaction, will only observe the latest write operation belonging to that transaction.
• External Consistency: Among two transactions T1 and T2, T2’s read operations with no prior write operation will observe the state written by transaction T1 conditioned no other transaction has written to T1.

For example: Consider a situation where a batch of six students is divided into two teams of three members each on the basis of their roll number is even or odd. Priorly, Team-1 contained students having even roll numbers, and Team-2 had students having odd roll numbers. After some time the teacher decided to change to Team-1 having odd and Team-2 having even rollnumber students. To do so two concurrent transactions are used, T1 which changes odd to even, and T2 which changes even to odd.

Figure 1: Running  transactions using serializable isolation level

As we can see the Table at the end will either contain all members having odd roll numbers or every member having an even roll number. This is because in serializable execution only one transaction will run at a time. T1 will leave the table with even roll numbers only and after T1, T2 will convert all the even roll numbers to odd.

Figure 2: Running  transactions using Snapshot isolation level

Examining the outcome of these transactions using snapshot isolation, we can notice that T1 and T2 both are working on the previous version of the database stored in the tempDB. T1 selects all the odd roll numbers and converts them to even and at the same time T2 selects the rows having even roll numbers (excluding the rows which were changed by T1 to even) and modifies them to odd. In this way, we are precisely able to switch the type of each candidate roll number 

Various Snapshot Isolation levels:

• Read-Committed Snapshot Isolation (RCSI): In RCSI, the SQL server maintains snapshots of data updates made by write operations at statement level i.e. the snapshots are shorter-lived. The data readers will get data stored in the last committed snapshot or version from the tempDB before the read statement begin. RCSI is also known as the statement-level snapshot isolation level.
• Snapshot Isolation: In snapshot isolation, the SQL server maintains snapshots of data updates made by write operations at the transactional level i.e. the snapshot is maintained for the lifetime of a transaction. The data readers will get data stored in the last committed version from the tempDB before the transaction started. Snapshot isolation is also known as transaction-level snapshot isolation level.

Limitations of Snapshot Isolation:

Apart from all the advantages added to the system when snapshot isolation is turned on, there are certain limitations of snapshot level as well. 

• The additional overhead of maintaining the row versions for every transaction updating the data in the tempDB.
• Read-write operations will not block each other but still blocking can occur among two write operations (known as update conflicts).