July 15, 2016 at 2:02 am
I'm trying to get my head around how SQL Server handles the above. I understand that undo is held in the transaction log, but I'm just trying to figure out how the following scenario is handled:
We have two users, user 1 and user 2 - both with READ COMMITTED isolation levels.
User 1 updates a very large table (too big for the buffer), dirty pages start getting written to the datafiles.
User 2 performs a select on the same table, on a row that has already been updated (but not committed)
Where does SQL Server get the previous version of the row from? I assume it would be the undo in the transaction log? Is the above scenario even possible?
July 15, 2016 at 4:18 am
User 2 will be blocked, as User1 will be holding exclusive locks on the table until the update is complete. User 2's select won't run until User 1 has completed the update, at which time User 2 will get the new values. There's no 'previous versions' involved here at all.
If we change User 2's isolation level, then things change.
If User 2 is running in read committed snapshot or snapshot isolation level, then previous versions of the rows are stored in TempDB, in the version store, and User 2 will get the correct earlier version of the rows from the version store
If User 2 is running in read uncommitted isolation level, then they'll see whatever values the row has (pre or post update depending on time) when they run the query.
In none of these cases is User 1's work undone or rolled back, and User 1's isolation level is irrelevant.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2016 at 4:25 am
Ok, many thanks - makes perfect sense.
July 15, 2016 at 7:39 am
Sorry to dig this back up, but I had a shower thought...
What if there were no users with a transaction isolation level of read committed snapshot or snapshot at the start of the update, but then later came in to query the updated (not committed) rows. Would the versioned rows still be in TempDB? Or would they just be blocked until the lock is released?
July 15, 2016 at 7:42 am
As soon as either of the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION_LEVEL database options are enabled, data modifications write their old versions to TempDB, no matter what else is running at the time the data modifications start.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply