How To Solve ORA-8177: can't serialize access for this transaction (Doc ID 464947.1)

Last updated on DECEMBER 22, 2016

Applies to:

PL/SQL - Version 9.2.0.8 and later
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 24th Nov 2016***

Symptoms

The following sample code demonstrating a situation where an ORA-8177 "can't serialize access for this transaction " error is encountered and how to solve it.

Steps to reproduce the error

1. As SYS, grant execute on SYS.DBMS_LOCK to the user SCOTT

SQL> grant execute on SYS.DBMS_LOCK to scott;


2. From SQL*PLUS, connect as SCOTT. This will be referred to as Session 1.

Create a table and insert a single which which will be used to demonstrate the error.

SQL> create table new_x ( id number );
SQL> insert into new_x values(10);
SQL> commit;


Create the following procedure which will be used to set the transaction and initiate the Update.

 

create or replace procedure proc1 is 
begin 
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
   dbms_lock.sleep(30); 
   begin 
      update new_x set id=10 where id=10; 
   end; 
end; 


3. Open a second SQL*Plus session and connect as SCOTT. This will be referred to as Session 2.


4. From Session 1, execute the procedure just created.

SQL> exec proc1;


5. Immediately after executing the proc1 in Session 1, execute the following in Session 2.

SQL> update new_x set id=10 where id=10;
SQL> commit;


6. The error is reproduced as a result of the proc1 execution in Session 1.

Since the procedure is designed to sleep for 30 second, there will be a delay before the error appears.

SQL> exec proc1; 
BEGIN proc1; END; 


ERROR at line 1: 
ORA-08177: can't serialize access for this transaction 
ORA-06512: at "SCOTT.PROC1", line 7 
ORA-06512: at line 1 

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms