How To Solve ORA-8177: can't serialize access for this transaction
Last updated on DECEMBER 22, 2016
Applies to:PL/SQL - Version 126.96.36.199 and later
Oracle Database - Enterprise Edition - Version 188.8.131.52 to 184.108.40.206 [Release 11.2]
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
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> insert into new_x values(10);
Create the following procedure which will be used to set the transaction and initiate the Update.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
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.
5. Immediately after executing the proc1 in Session 1, execute the following in Session 2.
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.
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
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