Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Serializable transaction bug
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 andcommits.
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
yong321_at_yahoo.com
![]() |
![]() |