Definition
Isolation is the “I” in ACID principal which rules the transactional state and its concurrency. The higher the isolation level, the lower the concurrency effects. And vice versa.
ISO Standard Isolation
The ISO standard defines 4 different isolation levels, from lowest to highest:
– Read uncommitted
– Read committed
– Repeatable read
– Serializable, where transactions are completely isolated from one another.
SQL Server Database Isolation
SQL Server Database supports all levels of isolation defined by ISO standard. In addition, it adds another isolation levels: the Snapshot isolation level (ALLOW_SNAPSHOT_ISOLATION).
SQL Server Database also add another implementation of Read Committed isolation level: Read Committed Snapshot Isolation or RCSI (READ_COMMITTED_SNAPSHOT). The original Read Committed implementation is referred to RC.
In Azure SQL Database, the default setting for Read Committed is RCSI (both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are set to ON).
In on-premise SQL Server, the default is RC (both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are set to OFF). But can be set to use RCSI by setting them to ON.
These two isolation levels, Snapshot isolation and RCSI, use row versioning that is maintained in tempdb.
Code
To check database settings for Snapshot isolation:
SELECT name, snapshot_isolation_state, is_read_committed_snapshot_on FROM sys.databases
To check current connection’s transaction isolation level:
SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
To set transaction isolation level for current connection. Transaction isolation level is per session / connection.
SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }
Isolation Level and Concurrency Effects Matrix
| Isolation Level |
Dirty Read |
Lost Update |
Non Repeatable Read |
Phantom |
| Read uncommitted |
Yes |
Yes |
Yes |
Yes |
| Read committed |
No |
Yes |
Yes |
Yes |
| Repeatable read |
No |
No |
No |
Yes |
| Snapshot |
No |
No |
No |
No |
| Serializable |
No |
No |
No |
No |
Performance
In a load test, performance is significantly higher in RCSI, but it requires a lot higher throughput in tempdb (some 50x larger I/O). So planning on tempdb scaling is very important.
Like this:
Like Loading...
