ORA-12838: cannot read/modify an object after modifying it in parallel
Does parallel DML or Direct path load are the only operations which throw ora-12838?
Yes any of the parallel DML(INSERT, UPDATE or DELETE) does not allow the consequent read or modify operation after the table rows are changed.
Demonstration:
When parallel is disabled at the table level, the error disappear
kIsH@STAX<>alter table ztbl parallel 1;
Table altered.
kIsH@STAX<>alter session enable parallel DML;
Session altered.
kIsH@STAX<>update ztbl set CITY='Tokyo' where xid=1047390;
1 row updated.
kIsH@STAX<>select * from ztbl where xid=1047390;
YCOL
--------------------------------------------------------------------------------
XID
----------
CITY
--------------------------------------------------------------------------------
SALARY
----------
xHBDnufFcY
1047390
Tokyo
541295
If the parallel is set to 8 at table level, the error creep in
kIsH@STAX<>alter table ztbl parallel 8;
Table altered.
kIsH@STAX<>alter session enable parallel DML;
Session altered.
kIsH@STAX<>update ztbl set CITY='Tokyo' where xid=1047390;
1 row updated.
kIsH@STAX<>select * from ztbl where xid=1047390;
select * from ztbl where xid=1047390
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
Execution plan shows that the update happens in parallel for the transaction
kIsH@STAX<>alter session enable parallel DML;
Session altered.
kIsH@STAX<>explain plan for update ztbl set CITY='Tokyo' where xid=1047390;
Explained.
kIsH@STAX<>set lines 200 pages 1000
kIsH@STAX<>SELECT * from DBMS_XPLAN.DISPLAY();
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3116472774
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 14 | 4 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 14 | 3 (0)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
| 3 | INDEX MAINTENANCE | ZTBL | | | | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 1 | 14 | 3 (0)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ10001 | 1 | 14 | 3 (0)| 00:00:01 | Q1,01 | P->P | RANGE |
| 6 | UPDATE | ZTBL | | | | | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 1 | 14 | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND HASH (BLOCK ADDRESS)| :TQ10000 | 1 | 14 | 3 (0)| 00:00:01 | Q1,00 | S->P | HASH (BLOCK|
| 9 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
|* 10 | INDEX RANGE SCAN | Z_IDX | 1 | 14 | 3 (0)| 00:00:01 | Q1,00 | SCWP | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("XID"=1047390)
Note
-----
- Degree of Parallelism is 8 because of table property
26 rows selected.