Search code examples
sql-server

does snapshot isolation level protects from Phantom read?


I was reading about snapshot isolation advantages. Snapshot Isolation level protects from dirty reads, non repeatable reads and Phantom reads. But in my below mentioned test I could see Phantom reads happening. So my question is how does snapshot isolation prevents phantom reads?

create table ABC(id int, name varchar(100))

    insert into abc
    values
    (1,'a'),
    (2,'b'),
    (3,'c')


    alter database xyz
    set allow_snapshot_isolation on

-----session 1-----

set transaction isolation level snapshot
begin transaction
update abc
set name = name + '1'
where id between 1 and 3

----session 2---------

set transaction isolation level read committed
begin transaction
insert into abc
values
(2,'inserted')

commit transaction

-------session 1 --------
commit transaction

So in my above test value with id = 2 was allowed to be inserted which should not have been allowed.


Solution

  • Phantom reads are defined as issuing the same query within the same transaction and getting different results. In your case, you never issued a second query within session 1 so, by definition, you didn't experience phantom reads.

    But even if you did, the insert in session 2 wouldn't have been prevented. Snapshot isolation (and the closely related read committed snapshot) increase concurrency by not having to lock the data that is required to provide the guarantees that the isolation level suggests. This comes at the cost of having to maintain a version store. To quote the docs:

    SNAPSHOT isolation specifies that data read within a transaction will never reflect changes made by other simultaneous transactions. The transaction uses the data row versions that exist when the transaction begins. No locks are placed on the data when it is read, so SNAPSHOT transactions do not block other transactions from writing data. Transactions that write data do not block snapshot transactions from reading data. You need to enable snapshot isolation by setting the ALLOW_SNAPSHOT_ISOLATION database option in order to use it.

    Now, if you're trying to prevent the insert in session 2 from happening, you could implement the serializable isolation level for session 1. But I'd really question the need to do so before doing that as it's going to come at the cost of concurrency.