My Oracle Support Banner

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

Last updated on MAY 08, 2018

Applies to:

PL/SQL - Version and later
Information in this document applies to any platform.
***Checked for relevance on 24th Nov 2016***


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 
      update new_x set id=10 where id=10; 

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 



To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.