Oracle: Select immediately after insert in serializable transaction

Question

I have run into a strange problem lately, programming in an Oracle database: inside a serializable transaction, i do a mass insert (INSERT ... SELECT), and immediately after, I open a cursor with a SELECT on the altered table. I assumed that this cursor would include the newly inserted rows, but, to my surprise, its contents are erratic, sometimes including all the newly inserted rows, and sometimes only a subset.

I have solved this problem by commiting before opening the cursor, but the behaviour has puzzled me. Can a select after an insert inside the same transaction, without an intervining commit, actually be trusted? Or is this behaviour somehow related to the transaction being serializable?

Followup: When trying to create a reproducible test case, I was only able to obtain this behaviour once I added an index (in this case a primary key index, on the actual code it was a regular index). Perhaps the problem lies in the time spent building the index, so that the SELECT actually uses an incomplete index to retrieve the results? Anyway, here goes a reproducible test case:

-- Create empty source table
CREATE TABLE TEST_CASE_1 AS 
  (SELECT 'CONTENT' AS CONTENT
   FROM DUAL
   WHERE 1 = 2)

-- Add primary key
ALTER TABLE TEST_CASE_1
ADD CONSTRAINT TEST_CASE_1_PK PRIMARY KEY (CONTENT);

-- Create empty destination table
CREATE TABLE TEST_CASE_2 AS 
  (SELECT 'CONTENT' AS CONTENT
   FROM DUAL
   WHERE 1 = 2)

-- Example of faulty code
BEGIN

  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

  -- Populate with 100.000 rows (I used ALL_OBJECTS but any source of 100.000 rows is good)
  INSERT INTO TEST_CASE_1
    (SELECT ROWNUM 
     FROM ALL_OBJECTS
     WHERE ROWNUM <= 100000);

  INSERT INTO TEST_CASE_2
    (SELECT *
     FROM TEST_CASE_1
     WHERE CONTENT > 0);

  COMMIT;

END;

In this example, I would expect TEST_CASE_2 to also have 100.000 rows. Reproducing this test case (in a load-free database), I obtained about 400-500 rows inserted. Removing the statement setting the transaction as serializable, I obtained the correct 100.000 row count.

Answer 1

This seems to be a bug; if you've got access to Oracle's support website look at note 1455175.1, which dates back to 8i. There are a couple of bug numbers listed (7592038 - 'SILENTLY INVISIBLE DATA FROM SELECT/UPDATE OF NEWLY INSERTED ROW IN SERIALIZABLE', 6363019) but they're closed as duplicates of 440317 ('ISOLATION LEVEL SERIALIZABLE CAUSES NO DATA FOUND ON ROWS SELECTED AFTER INSERT'), which is shown as still open and being investigated by development - even though it was originally raised against version 7(!).

You seem to be right that's it's related to the PK. The workarounds listed are:

• Commit the work executed to that point.
• Execute additional (but different) statements (perhaps after rolling back to a savepoint established earlier in the transaction).
• Roll back the entire transaction and restart the transaction from beginning.
• Perform a full table scan and avoid using the indexes.

You know the first workaround is effective already, and I don't think the second or third will help you? You could try the fourth, adding a /*+ FULL(TEST_CASE_1) */ hint to the select for the second insert.

I don't get the error in 11.2.0.2 (Linux), though I can't find anything suggesting the bug has been fixed; and I don't have an 11.1 environment to try it on - so I can't check that last work-around applies to this test case.

There's a note that you can get ORA-08177 instead in 11G. I had that problem if I ran the anonymous block too soon after creating the tables, or if I had too many rows inserted, which also seems to be related to the PK. This previous question may be relevant.

Seems like this will continue to be a problem, so if the workarounds don't help you may need to reconsider if you really do need to change the isolation level; and if you do you may have to raise a service request with Oracle to get a better answer.