Notes on SQL

In the previous article it was shown that phantom reads and skipped rows were possible with the repeatable read isolation level.
Serializable is the highest isolation level and will not allow phantom reads or skipped rows.

Using the same table that demonstrated repeatable read, an initial execution of the two queries shown produces a count and SUM of 1000541.

Note that yet again the BEGIN/COMMIT TRANSACTION lines have been commented out. The two queries therefore run in their own independent transactions.
If I execute a script to update one of the selected values,

UPDATE dbo.Test4
SET Test_Value = 2
WHERE ID = 887;

whilst repeating the query above, the results appear to be the same as for repeatable read.

The UPDATE was blocked during the execution of the first SELECT, but the SUM query used the updated value.
Reset the data, enable the BEGIN/COMMIT TRANSACTION lines and repeat the test and it produces results that match the same test against repeatable read.

The UPDATE was blocked until the transaction that had acquired the locks had released those locks. Rerun the first SELECT query and the change has been completed once it was allowed to do so.

In this example the behaviour looks the same as that for repeatable read. However, the locks acquired are different and this results in different behaviour in relation to phantom reads and skipped rows.

Adding qualifying data (Phantom Reads)
As with the repeatable read tests, all but 100 rows of data where ‘Index_Column’ has a value of ‘B’ have been removed and a 10-second delay placed between the two queries.

Running the main query and executing the following query during the 10-second delay, which will attempt to add a row to the start of the index.

INSERT INTO [dbo].[Test4]
           ([ID]
           ,[Test_Value]
           ,[Index_Column])
     VALUES
           (1
           ,1
           ,'B')
GO

At this point the difference between repeatable read and serializable becomes apparent.

The insert statement was blocked for the entire transaction, so both SELECT statements returned consistent results. With repeatable read the second query read more rows than the first.
Serializable does not only place locks on the data it is reading but by acquiring a key-range lock it blocks any attempt to insert rows that would have matched this selection criteria, regardless of their location within the index. Because it was locking rows with the ‘Index_Column’ value of ‘B’, it prevented any insertion of rows that also have ‘Index_Column’ set to ‘B’.
Therefore phantom reads are not possible with serializable isolation.

Skipped Rows
Resetting the data and selecting the rows with a value on the ‘ID’ column < 21, produces 16 rows.

In a new window, a transaction is started that will potentially update the row with ‘ID’ = 10.

BEGIN TRANSACTION

UPDATE dbo.Test4
SET Test_Value = 2
WHERE ID = 10;

Notice the absence of a ROLLBACK/COMMIT command, causing a block for the query that will read that area of the index.
Repeat the execution of the ‘WHERE id < 21’ query and it is blocked by the UPDATE query.
In a third window, update the row with ‘ID’ of 20, to move it to the start of the index.

UPDATE dbo.Test4
SET id = 3 
WHERE id = 20;

With repeatable read, this last query completed and effectively moved the data to a point in the index that had already been processed, thereby skipping this data for the main query.
However, because serializable locks all of the keys it will be processing, whether before or after where it has currently reached, this update is blocked.

SPID 73 is

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT  *
FROM [IsolationLevels].[dbo].[Test4] 
WHERE id < 21

which is blocked by

SPID 75, which is

BEGIN TRANSACTION

UPDATE dbo.Test4
SET Test_Value = 2
WHERE ID = 10;

SPID 70 is

UPDATE dbo.Test4
SET id = 3 
WHERE id = 20;

which is blocked by SPID 73 – the serializable query.
Rolling back SPID 75 results in the server choosing a deadlock victim between SPID 70 and 73 and rolling back a transaction, but does demonstrate that serializable does not allow skipped rows, unlike repeatable read.

Conclusion
By looking at examples of serializable isolation it can be seen that the opportunities for blocking other transactions and the possibility of deadlocks is higher than the ‘lower’ isolation levels, potentially reducing the degree of concurrency of the system. This is the price paid for increased isolation of the data changes when using this isolation level.
In addition, if the field selected in the WHERE clause does not have an index then using serializable isolation will lock the entire table, so index design becomes even more important when using this isolation level.