Difference between revisions of "ORA-08177: can't serialize access for this transaction"

From Robs_Wiki
Jump to: navigation, search
(Reproduce the error)
(Error Explanation)
Line 3: Line 3:
 
My Oracle Support Note : Insert Into Table Fails With ORA-08177 (Doc ID 160593.1)
 
My Oracle Support Note : Insert Into Table Fails With ORA-08177 (Doc ID 160593.1)
 
== Error Explanation ==
 
== Error Explanation ==
 +
Symptoms:
 
<pre>
 
<pre>
 
•symptom: Insert into table fails
 
•symptom: Insert into table fails
Line 17: Line 18:
 
  to which the rows are being inserted.  Without any indexes the problem will not reproduce     
 
  to which the rows are being inserted.  Without any indexes the problem will not reproduce     
 
</pre>
 
</pre>
 +
Cause:<br />
 +
Concurrent inserts into a table with index using serializable transactions
 +
could lead to an index split that the transaction tries to re-read and cause an error ORA-08177
  
 
== Reproduce the error ==
 
== Reproduce the error ==

Revision as of 14:37, 6 February 2020

Reference

My Oracle Support Note : Insert Into Table Fails With ORA-08177 (Doc ID 160593.1)

Error Explanation

Symptoms:

•symptom: Insert into table fails

•symptom: ORA-08177: can't serialize access for this transaction

•symptom: transaction isolation level is serializable

•symptom: The table has index definitions

•symptom: Several transaction insert data into same table

•cause: The problem manifest itself when there are indexes on the table   
 to which the rows are being inserted.  Without any indexes the problem will not reproduce     

Cause:
Concurrent inserts into a table with index using serializable transactions could lead to an index split that the transaction tries to re-read and cause an error ORA-08177

Reproduce the error

First, as use robla, create the test objects:

SQL> create table emp2 as select * from emp where rownum < 1;

Table created.

SQL> create index emp2_indx on emp2(empno); 

Index created.

In two separate SQL sessions, as user robla, enter the following commands, i.e. type each individual command on one session followed immediately on the other session. (Do not connect as sys as you can't set the transaction isolation level as sys):

-- Session 1:
SQL_01> set transaction isolation level serializable;

Transaction set.

-- Sesssion 2:
SQL_02> set transaction isolation level serializable;