The 12.0.1 docs on Snapshot isolation contain the following statements:
I have two related questions to this:
Background: I'm trying to figure out the impact of snapshot isolation when only now and then a transaction would use that mode, i.e. when updates would mostly take time without a concurrent snapshot isolation transaction - would those updates lead to possibly noticeable worse performance?
I'll answer your second question first. If snapshot is enabled for the database, the server will save copies of modified rows even if there are no other connections. It has to, because the currently-executing transaction may be long-running and a new connection may start that desires snapshot semantics. If so, the server must be able to return consistent copies of rows whose modifications have still not been committed by the first transaction.
As for your first question - the row image that is saved is the "before" image prior to the update. That before image needs to last as long as necessary in order to satisfy any extant snapshot transactions. A COMMIT by the modifying transaction starts the "clock" as to when the copy can be deleted. The original modifying transaction can continue to modify the row as many times as it likes - only the one before image is necessary to satisfy other snapshots.
Multiple (and distinct) copies of a row image will need to be saved when there are several snapshot transactions that start at different times and have different durations, and the row in question has been modified multiple times by different transactions.
answered 24 Aug '12, 06:31