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. Thanks for the answers - I was After some thought, both points are clear to me know. I further assume that a DELETE will have to leave the "before" image in a similar fashion. As a consequence, I conclude that there will always be some impact on storage when snapshot isolation is allowed, even if there are no transactions currently using that mode. So, given I would have only one writing transaction and currently no snapshot-isolated transaction and would be using auto-commit mode, then I guess the following would happen:
Are these assumptions correct on that abstract level?
(24 Aug '12, 07:04)
Volker Barth
Replies hidden
1
Yes. It is the cleaner that is responsible for cleaning up old row versions that are no longer needed, along with its index entries.
(24 Aug '12, 07:15)
Glenn Paulley
|