Difference between revisions of "ORA-08177: can't serialize access for this transaction"
From Robs_Wiki
Qadmin wiki (talk | contribs) (→Implement the solution) |
Qadmin wiki (talk | contribs) (→Implement the 3rd solution) |
||
| Line 69: | Line 69: | ||
== Implement the 3rd solution == | == Implement the 3rd solution == | ||
=== Explanation === | === Explanation === | ||
| − | This rowdependencies | + | This rowdependencies clause changes the default behavior of Oracle to put an SCN on every row within the tables, and not just one SCN for every physical data block. This adds a hidden column to each row, taking 6 bytes and storing the SCN on a row-by-row basis with your data. NOTE: This is a CREATE TABLE option only. You can’t modify a table to add rowdependencies, and there are a few restrictions for tables where this is enabled. Check the documentation for your version. |
=== Implement the solution === | === Implement the solution === | ||
First check the current table setting: | First check the current table setting: | ||
| Line 108: | Line 108: | ||
</pre> | </pre> | ||
Now re-execute the statements from before, and the error will no longer occur. | Now re-execute the statements from before, and the error will no longer occur. | ||
| + | <pre> | ||
| + | -- Session 1: | ||
| + | SQL> set transaction isolation level serializable; | ||
| + | |||
| + | Transaction set. | ||
| + | |||
| + | ROBLA @ TSTDB [T52-d1-db2-10g-sup] : | ||
| + | SQL> insert into emp2 select * from emp; | ||
| + | |||
| + | 14 rows created. | ||
| + | |||
| + | ROBLA @ TSTDB [T52-d1-db2-10g-sup] : | ||
| + | SQL> insert into emp2 select * from emp; | ||
| + | |||
| + | 14 rows created. | ||
| + | |||
| + | ROBLA @ TSTDB [T52-d1-db2-10g-sup] : | ||
| + | SQL> / | ||
| + | |||
| + | 14 rows created. | ||
| + | |||
| + | ROBLA @ TSTDB [T52-d1-db2-10g-sup] : | ||
| + | SQL> / | ||
| + | |||
| + | 14 rows created. | ||
| + | </pre> | ||
| + | <pre> | ||
| + | -- Session 2 | ||
| + | SQL> set transaction isolation level serializable; | ||
| + | |||
| + | Transaction set. | ||
| + | |||
| + | ROBLA @ TSTDB [T52-d1-db2-10g-sup] : | ||
| + | SQL> insert into emp2 select * from emp; | ||
| + | |||
| + | 14 rows created. | ||
| + | |||
| + | ROBLA @ TSTDB [T52-d1-db2-10g-sup] : | ||
| + | SQL> insert into emp2 select * from emp; | ||
| + | |||
| + | 14 rows created. | ||
| + | |||
| + | ROBLA @ TSTDB [T52-d1-db2-10g-sup] : | ||
| + | SQL> / | ||
| + | |||
| + | 14 rows created. | ||
| + | |||
| + | ROBLA @ TSTDB [T52-d1-db2-10g-sup] : | ||
| + | SQL> / | ||
| + | |||
| + | 14 rows created. | ||
| + | </pre> | ||
Revision as of 16:29, 6 February 2020
Contents
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. SQL> insert into emp2 select * from emp; 14 rows created.
-- Sesssion 2: SQL_02> set transaction isolation level serializable; SQL> insert into emp2 select * from emp; 14 rows created.
-- Session 1: SQL_01> set transaction isolation level serializable; Transaction set. SQL> insert into emp2 select * from emp; ERROR at line 1: ORA-08177: can't serialize access for this transaction
Solutions
- Insert a smaller number of rows (e.g commit more often)
- or Catch the error code in the application and re-try the operation again
- this can be fixed by implementing "rowdependencies" during normal table creation.
Implement the 3rd solution
Explanation
This rowdependencies clause changes the default behavior of Oracle to put an SCN on every row within the tables, and not just one SCN for every physical data block. This adds a hidden column to each row, taking 6 bytes and storing the SCN on a row-by-row basis with your data. NOTE: This is a CREATE TABLE option only. You can’t modify a table to add rowdependencies, and there are a few restrictions for tables where this is enabled. Check the documentation for your version.
Implement the solution
First check the current table setting:
SQL> SELECT owner, table_name, dependencies FROM dba_tables where table_name = 'EMP2'; OWNER TABLE_NAME DEPENDEN ------------ ---------- -------- ROBLA EMP2 DISABLED
Drop and recreate the table, with ROWDEPENDENCIES: (you can't change it in an existing table unfortunately)
SQL> create table robla.emp2 ( EMPNO NUMBER(4), ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) ) ROWDEPENDENCIES / Table created. SQL> create index robla.emp2_indx on robla.emp2(empno); Index created.
Let's check the results:
SQL> SELECT owner, table_name, dependencies FROM dba_tables where table_name = 'EMP2'; OWNER TABLE_NAME DEPENDEN ------------ ---------- -------- ROBLA EMP2 ENABLED
Now re-execute the statements from before, and the error will no longer occur.
-- Session 1: SQL> set transaction isolation level serializable; Transaction set. ROBLA @ TSTDB [T52-d1-db2-10g-sup] : SQL> insert into emp2 select * from emp; 14 rows created. ROBLA @ TSTDB [T52-d1-db2-10g-sup] : SQL> insert into emp2 select * from emp; 14 rows created. ROBLA @ TSTDB [T52-d1-db2-10g-sup] : SQL> / 14 rows created. ROBLA @ TSTDB [T52-d1-db2-10g-sup] : SQL> / 14 rows created.
-- Session 2 SQL> set transaction isolation level serializable; Transaction set. ROBLA @ TSTDB [T52-d1-db2-10g-sup] : SQL> insert into emp2 select * from emp; 14 rows created. ROBLA @ TSTDB [T52-d1-db2-10g-sup] : SQL> insert into emp2 select * from emp; 14 rows created. ROBLA @ TSTDB [T52-d1-db2-10g-sup] : SQL> / 14 rows created. ROBLA @ TSTDB [T52-d1-db2-10g-sup] : SQL> / 14 rows created.
