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.

Answer 2

This is a confirmed bug, and Oracle stated that they do not plan on fixing it. Here's an excerpt from their response to my service request (January 2015):

These symptoms are due to the Serializable transaction been found with the known issues and you conclusion with the Bug 440317 is correct.

Bug 440317 - ISOLATION LEVEL SERIALIZABLE CAUSES NO DATA FOUND ON ROWS SELECTED AFTER INSERT
Bug 16803610 - ROWS INSERTED USING INSERT INTO ARE LOST IN SERIALIZABLE ISOLATION LEVEL TRANSAC

Both these Bugs are published, so you can see the details in the MOS bug search.

As per development, there was multiple bugs for the same issue with very long history. The design isn't easy to change, hence there's no fix till the moment forking out this feature a not very useful one.

Development has closed the bug saying code fix is not feasible.

The workarounds suggested are
Application Code modifiction:
change the logic to have the commit before the select
or dont use serializable
Without application code modification:
Do not use the primary key or the indexes on the table