Repeatable Read

This is similar to Read Committed but it has an additional guarantee that if we issue the same select twice in a transaction we will get the same results on both occasion. It behaves like this by holding on to the shared locks it obtains on the records it reads until the end of the transaction. That means any transactions that tries to modify these records are forced to wait for the read transaction to complete.

As earlier we did, run Query1 then while its running run Query2

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM IsolationDemo
WAITFOR DELAY ’00:00:20′
SELECT * FROM IsolationDemo
ROLLBACK

Query2
UPDATE IsolationDemo
SET Col1 =10

Query-1

Query-2

Now bring the attention towards the results, query1 returns the similar data for both selects even we have executed query2 to modify the data before the 2nd SELECT got executed. Reason behind is that UPDATE query was forced to wait for query1 to finish due to the exclusive locks that got opened as we specified REPEATABLE READ.

Now if we rerun the above Queries but change Query1 to Read Committed we will notice the two selects return different data and that Query2 does not wait for Query1 to finish. One more thing to keep in mind about Repeatable Read is that the data can change between 2 queries if more records are added. Repeatable Read guarantees records queried by a previous select will not be changed or deleted, it does not stop new records being inserted so it is still very possible to get Phantom Reads at this isolation level.

Usage – In this isolation mode new rows can be inserted into the dataset. Under REPEATABLE READ, SQL Server will lock all rows it reads.

In a scenario when we are working with cursors fetching large amounts of records this can cause conflict with other users because they will not be able to obtain locks to update any of the rows read by cursors with Repeatable Read until the cursor is closed.

Serializable
This isolation level considers Repeatable Read & adds the guarantee that no new records will be added removing the chance of getting Phantom Reads. It does this by placing range locks on the queried records. In this scenario any other transactions trying to modify or insert data touched on by this transaction to wait until it has finished.

We know the steps by now, run these queries side by side…

Query1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM IsolationDemo
WAITFOR DELAY ’00:00:20′
SELECT * FROM IsolationDemo
ROLLBACK

Query2
INSERT INTO IsolationDemo (Col1,Col2,Col3)
VALUES (100,100,100)

 

Query-1-b

Query-2-b

We observe that INSERT in Query2 waits for Query1 to complete before it runs eradicating the chance of a phantom read. If we change the isolation level in Query1 to repeatable read, we’ll see the INSERT no longer gets blocked and the two select statements in Query1 return a different amount of rows.

Usage – Remember it’s the most restrictive isolation level with the highest impact on concurrency.

So there can be some scenarios where there is no effect of one transaction seen by another transaction at all. In this, the transaction maintains all locks throughout its lifetime– even those normally discarded after use.

Snapshot
It provides the same guarantees as Serializable does. So where lies the difference? Well it’s more in the way it works, using snapshot doesn’t block other queries from inserting /updating the data touched by the snapshot transaction. Instead row versioning is used so when data is changed the old version is kept in tempdb so existing transactions will see the version without the change. When all transactions that started before the changes are complete the previous row version is removed from tempdb. This means that even if another transaction has committed changes we will always get the same results as we did the first time in that transaction.

So on the positive side we are not blocking anyone else from modifying the data while we run our transaction but…. We’re occupying extra resources on the SQL Server to hold multiple versions of our changes.

To use the snapshot we need to enable it on the database as below
ALTER DATABASE IsolationDemo
SET ALLOW_SNAPSHOT_ISOLATION ON

If we rerun the examples from Serializable but change the isolation level to snapshot we will notice that we still get the same data returned but Query2 no longer waits for Query1 to complete.
Usage- It can be used to prevent locking issues with reads/writes by enabling snapshot isolation level at DB server, here SQL Server will maintain snapshots of records for running transactions.

For e.g. Against conn1, we are executing a big select query. On conn2, we update some of the records that are going to be returned by first SELECT i.e. conn1.

So, Snapshot isolation ON the SQL makes a temp copy of records, affected by update, so SELECT will return original data. This setting is OFF by default.

 

 

Chandra M
Associate Technical Architect