Data concurrency is difficult. Oracle does such a good job at hiding the complexity that many developers simply don’t consider it. I’m going to focus this post on one of the aspects of Oracle’s multi-versioning read consistency that new developers really should know about.
You’re probably aware that Oracle does not allow the reading of dirty data, i.e. data that has been modified by another session but that has not been committed. This allows a session to keep its changes private until it decides to commit or rollback those changes. On commit then all the changes are immediately available for other sessions to see. In light of this, ever considered what happens when a long running query accesses data that is being modified by other sessions, for example session 1 runs a simple SELECT query that takes 1 minute to complete while at the same time session 2 is modifying and committing changes to the data? Unlike some other database systems, readers and writers don’t block each other in Oracle so what will the query output for session 1 look like?
The answer is surprisingly simple: a query runs using a view of the data that was in effect as at the time the query started. Changes made to the data for the duration of the operation are ignored. In Oracle this is termed multi-versioning read consistency. The simplest and clearest way of illustrating this that I’ve seen is contained in Tom Kyte’s excellent book Expert Oracle Database Architecture:
SQL>CREATE TABLE t1
2 (col_x NUMBER(10) NOT NULL)
3 /
Table created.
SQL>INSERT INTO t1
2 SELECT ROWNUM
3 FROM dual
4 CONNECT BY ROWNUM <= 5
5 /
5 rows created.
SQL>COMMIT
2 /
Commit complete.
SQL>VARIABLE rc REFCURSOR
SQL>BEGIN
2 OPEN :rc
3 FOR
4 SELECT *
5 FROM t1;
6 END;
7 /
PL/SQL procedure successfully completed.
SQL>DECLARE
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 BEGIN
4 DELETE
5 FROM t1;
6 COMMIT;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>SELECT *
2 FROM t1
3 /
no rows selected
SQL>PRINT rc
COL_X
----------
1
2
3
4
5
In the above we create a table and populate it with 5 rows. Next we open a cursor that simply selects the data from the table. In an autonomous transaction, which simulates another session, we delete the contents from the table and commit that change. We can see that the table has no rows in it by selecting from the table. However, when we finally get around to pulling the details from the cursor we opened before the deletion we see that all the data. So, regardless of the changes that occurred to the table the cursor displays data as at the point in time that it was opened.
Multi-versioning read consistency is also the reason why the following doesn’t result in an infinite loop (using the same T1 table, repopulated with 5 rows):
SQL>BEGIN
2 FOR i IN (SELECT col_x FROM t1)
3 LOOP
4 dbms_output.put_line ('Inserting value ' || TO_CHAR(i.col_x));
5 INSERT INTO t1 VALUES (i.col_x);
6 END LOOP;
7 END;
8 /
Inserting value 1
Inserting value 2
Inserting value 3
Inserting value 4
Inserting value 5
PL/SQL procedure successfully completed.
Here we’re inserting into the same table that our cursor is reading from. If the data from the cursor was not fixed at the point when it was opened then the rows we’re inserting would be read by the cursor and our loop would never end… well, not until some resource on the server gives out.
One thing to note with multi-versioning read consistency is that it only applies to a single SQL statement and not an entire transaction of multiple statements. So, in the scenario of two SELECT statements, one reading parent entries and one child entries, then the second SELECT may return data for child entries for which the first SELECT did not return the parent entry. This would occur if another session inserted and committed parent/child entries after the parent table SELECT is executed but before the child table SELECT commenced.
It is also worth noting that the read consistency does not extend to SQL statements contained in PL/SQL routines invoked by an SQL statement. To demonstrate this we’ll reuse our T1 table from above and also create another table, T2, with a single column and row. On insert into T1 we’ll run a autonomous transaction trigger that will increment the value in T2. We’ll also create a function that reads the T2 value and use that to insert into T1:
SQL>CREATE TABLE t2
2 (col_y NUMBER(10))
3 /
Table created.
SQL>INSERT INTO t2
2 VALUES (1)
3 /
1 row created.
SQL>COMMIT
2 /
Commit complete.
SQL>CREATE OR REPLACE TRIGGER trg_t1
2 BEFORE INSERT ON t1
3 FOR EACH ROW
4 DECLARE
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7 UPDATE t2
8 SET col_y = col_y + 1;
9 COMMIT;
10 END trg_t1;
11 /
Trigger created.
SQL>CREATE OR REPLACE FUNCTION fnc_1
2 RETURN NUMBER
3 AS
4 l_return_value NUMBER;
5 BEGIN
6 SELECT col_y
7 INTO l_return_value
8 FROM t2;
9 RETURN l_return_value;
10 END fnc_1;
11 /
Function created.
SQL>INSERT INTO t1
2 SELECT fnc_1
3 FROM dual
4 CONNECT BY ROWNUM <= 5
5 /
5 rows created.
SQL>SELECT *
2 FROM t1
3 /
COL_X
----------
1
2
3
4
5
SQL>SELECT *
2 FROM t2
3 /
COL_Y
----------
6
Here can see that the values in T1 reflect the value stored in T2 for each row inserted into T1, i.e. the value returned by the SQL SELECT in function FNC_1 was not locked to what the data looked like when the overall INSERT was executed. Developers who like to call out to PL/SQL functions inside SQL are likely to be causing themselves concurrency headaches.
Mark Hoxey 