Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Serializable transaction bug

RE: Serializable transaction bug

From: yong huang <>
Date: Thu, 21 Sep 2000 16:07:39 -0700 (PDT)
Message-Id: <>

If anybody is still interested in this bug (Oracle doesn't acknowledge), here's a little progress, or lack of. It's Doc ID 145339.999 on Metalink. To recap the problem briefly:

1)Table has an index 
2)Session #1 starts with set session serializable 
3)Session #2 starts with set session serializable and inserts a record and
4)Session #1 inserts and gets the 8177 error. 5)If I remove the index and retest, there is no problem.

Here's some response:
**********************begin quote**********************
From: Oracle, Helen Schoone 21-Sep-00 15:05 Subject: Re : ORA-08177: Serializable transactions cannot insert rows into the same table

Hi. There are a variety of scenarios where this error can be signalled that is expected behavior. There have been a couple of bugs to. In your example, a bug was filed against 8.1.5 describing an identical scenario: 2 sessions concurrently inserting into a table and ORA-8177 is signalled (intermittantly) if there is an index on the table. If there is no index in the scenario, the error did not occur.

The bug was closed as not a bug with an explanation that it does not matter whether there are 2 sessions inserting into the table or one single session inserting into the table. The issue is that if the insert leads to an index block split and the transaction attempts to reread those blocks, ORA-8177 is signalled. Retrying the insert should succeed for that block, but may fail on the next split. You may be able to minimize the occurrence by inserting a smaller number of rows per execute, and then retry the operation if an ORA-8177 is signalled.

It is important to note that when using serializable transactions, the application needs to be coded to handle an ORA-8177 error. There are several documentation bugs filed (1089910/915553) requesting that the documentation provide further details of the circumstances under which this error can be signalled.
**********************end quote**********************

And my comment is:

The only meat in her message is mention of index block split. But my understanding of that is that it only occurs in this scenario: I have an index block containing EMPID's 1 to 100 with some EMPID's missing (say, EMPID 45 is missing) and the block is full, i.e. very close to PCTFREE. Now I want to add a record with EMPID 45. I can't directly add the index info to this block because it's full. So Oracle moves EMPID 46 to 100 to a new block before I append EMPID 45.

Her message is wrong as an answer to the problem because we can create a very simple table with a column of number type and insert 1 and 2 sequentially to reproduce the error. This won't cause an index block split. Besides, repeated inserts in the serializable session continue to fail, until rollback or commit to end the transaction.

Yong Huang

Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger. Received on Thu Sep 21 2000 - 18:07:39 CDT

Original text of this message