Archives January 2023

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.

Add a todo task list for today using python

Code:

#Assign temporary list
tasks = []



#Set the flag and loop through it
flag = True
while flag == True:
    """Add a selection and perform add, view and exit"""
    selection = input("Type 1 for add ,2 for view, 3 for edit and 4 for exit: ")

    if selection == '1':
        task = input("Enter a todo: ")
        tasks.append(task)
    elif selection == '2':
        for x in tasks:
            print(x.title())
    elif selection == '3':
        new_idx = int(input("Enter the item to be edited and updated on the list, there are {0} items in list : ".format(len(tasks))))
        new_idx -= 1
        tasks[new_idx] = input("Enter the task to be updated: ")
        print("Item is successfully updated")
        for x in tasks:
            print(x)
    elif selection == '4':
        flag = 'False'
    else:
        print("Enter the correct option number given above")
        continue
print()
print('**** Exiting the list ****')

O/P:

Type 1 for add ,2 for view, 3 for edit and 4 for exit: 1
Enter a todo: learn
Type 1 for add ,2 for view, 3 for edit and 4 for exit: 1
Enter a todo: play
Type 1 for add ,2 for view, 3 for edit and 4 for exit: 1
Enter a todo: sleep
Type 1 for add ,2 for view, 3 for edit and 4 for exit: 2
Learn
Play
Sleep
Type 1 for add ,2 for view, 3 for edit and 4 for exit: 3
Enter the item to be edited and updated on the list, there are 3 items in list : 2
Enter the task to be updated: fly
Item is updated
learn
fly
sleep
Type 1 for add ,2 for view, 3 for edit and 4 for exit: 4

**** Exiting the list ****

Process finished with exit code 0

PROGRAM TO CHECK NEGATIVE NUMBERS IN A LIST IN PYTHON

def negative():
    N = 0
    n = list(map(int, input().split()))
    for x in n:
        if x < 0:
            N = x
    return N

negative()
5 4 3 2 -1
-1