When Snapshots Begin

Kendra Little explains when a transaction really begins when you are in the snapshot isolation level:

  • 00.000 – Session A sets its isolation level to snapshot

  • 00.001 – Session A explicitly begins a transaction with BEGIN TRAN

  • 00.002 – Session A starts a WAITFOR command for 15 seconds

  • 10.000 – Before the WAITFOR completes, Session B inserts rows into dbo.Table

  • 15.001 – Session A starts a SELECT from dbo.Table, which returns the rows that Session B inserted

This seems wrong, because many of us commonly say things like, “in Snapshot Isolation level, all statements see data consistent with the beginning of the transaction.”

But in this case, Session B inserted the rows after Session A began its transaction using Snapshot Isolation level. So why did Session A see those rows?

Kendra explains the nuance well, so read the whole thing.

Related Posts

SQL Server Isolation Levels

Pamela M. takes us through a humorous journey covering transaction isolation levels in SQL Server: READ COMMITTED:  One step up (and the default for SQL Server).    A query in the current transaction can’t read data modified by someone else that hasn’t yet committed.  No dirty reads.  BUT….data could be changed by others between statements in […]

Read More

New(ish) VLF Status: 4

Paul Randal points out a new VLF status which can appear if you’re using an Availability Group: At least since I started working on the SQL Server team (just after 7.0 shipped) and since then there have only been two VLF status codes: 0 = the VLF is not active (i.e. it can be (re)activated […]

Read More

Categories

June 2017
MTWTFSS
« May Jul »
 1234
567891011
12131415161718
19202122232425
2627282930