The 12.0.1 docs on Snapshot isolation contain the following statements:

When snapshot isolation is enabled for a database, each time a row is updated, the database server adds a copy of the original row to the version stored in the temporary file. The original row version entries are stored until all the active snapshot transactions complete that might need access to the original row values.

I have two related questions to this:

  1. "...each time a row is updated..."
    Does that mean: Each time a commit is made - meaning if a transaction would change a row several times, only the last state would be stored as a versioned row?

  2. "...are stored until all the active snapshot transactions complete..."
    What happens when there is no current transaction using any of the snapshot isolation levels at the time the row version is stored? Will it be stored anyway (and immediately deleted - or short after) or will the storing be skipped?

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?

asked 24 Aug '12, 03:58

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 24 Aug '12, 06:25

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.

permanent link

answered 24 Aug '12, 06:31

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

Thanks for the answers - I was hoping sure this is a still a topic for you:)

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:

  1. UPDATE myTable SET col1 = 1;
    Row is modified, before image is stored as row version
  2. COMMIT;
    Server checks (immediately or with a cleaner process?) that there is no transaction needing the versioned row - and deletes it.

Are these assumptions correct on that abstract level?

(24 Aug '12, 07:04) Volker Barth
Replies hidden

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 24 Aug '12, 03:58

question was seen: 3,114 times

last updated: 24 Aug '12, 07:15