Following on from some recent questions this post goes back to basics with regard to what Oracle terms “multi-versioning read consistency”. I have posted on this subject before but as it’s so critical to how applications using Oracle will behave it’s worth another post, with emphasis on a very important point at the end…
“Multi-versioning read consistency” might appear to be just a rather grand sounding name (or another bit of techno-jargon) so here’s a brief outline of what it means:
- The data reurned by a query is based on what the data in the underlying tables contained when the query commenced
- By extension of the above point, the query is not impacted by changes to the table data over the time taken to execute and retrieve the query data
It’s time for an example to illustrate this. First we’ll set up a simple table, TAB, and insert a single row:
CREATE TABLE tab (id NUMBER(6) NOT NULL) / INSERT INTO tab VALUES (1); COMMIT;
Next we’ll open a cursor that returns the data in TAB but we won’t actually retrieve the data yet.
VARIABLE rc1 REFCURSOR BEGIN OPEN :rc1 FOR SELECT * FROM tab ORDER BY id; END; /
Let’s head back to our table and do some data changes. We’ll do these changes as autonomous transactions so as to simulate an external process coming in and making these changes, removed from the session with the open cursor:
DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN UPDATE tab SET id = 2; INSERT INTO tab VALUES (2); COMMIT; END; /
… and once again open a new cursor onto our table:
VARIABLE rc2 REFCURSOR BEGIN OPEN :rc2 FOR SELECT * FROM tab ORDER BY id; END; /
Lastly, just to reinforce things, we’ll do the whole thing yet again followed by a last update:
DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN UPDATE tab SET id = 3; INSERT INTO tab VALUES (3); COMMIT; END; / VARIABLE rc3 REFCURSOR BEGIN OPEN :rc3 FOR SELECT * FROM tab ORDER BY id; END; / DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN UPDATE tab SET id = 4; INSERT INTO tab VALUES (4); COMMIT; END; /
At the end of all this we have performed 4 inserts and 3 updates to our data. If we display the contents of our table as it exists at the end of the changes we get:
SQL> SELECT * 2 FROM tab 3 ORDER BY id; ID ---------- 4 4 4 4
Now let’s retrieve the data from our cursors and see what they contain:
SQL> PRINT rc1 ID ---------- 1 SQL> PRINT rc2 ID ---------- 2 2 SQL> PRINT rc3 ID ---------- 3 3 3
Even though the data in the table had changed and was committed after we opened the cursors Oracle still returned the data as it was at the point in time we opened the cursor, not what the table contained when we read from the cursor. This is Oracle’s multi-versioning read consistency in action.
Now for that very important point that I mentioned at the start…
The above exercise also highlights another aspect of Oracle:
Readers and writers don’t block each other.
If you look closely at the example, whilst we were holding open cursors to the table we were still able to update existing rows as well as insert new ones. No locks on the table data were being held by the cursors to prevent this… yet the data returned by the cursors were still as the table looked when the cursor was opened. This is a fundamental of how Oracle works. If you want to build a system that is performant under high levels of concurrent activity this is exactly the behaviour you will require.