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.

Leave a Reply