'Oralce uncommitted data in other sessions violating unique constraint leading to never-ending query

I am running into never ending update when there's uncommitted data in other session violating unique constraint.

Here's the scenario:

Say a table t has a unique contraint on one of its columns:

CREATE TABLE T AS SELECT 'PIPE'||level AS CODE, level AS pk_id FROM dual connect by level <=5;

ALTER TABLE t
ADD CONSTRAINT constraint_name UNIQUE (CODE);

Session 1 : Update a record but not committed

DECLARE
    l_dummy   NUMBER;
BEGIN
    SELECT 1
      INTO l_dummy
      FROM t
     WHERE pk_id = 2
    FOR UPDATE NOWAIT;

    UPDATE t
       SET code = 'CHANGED' || 1
     WHERE pk_id = 2;
END;

Session 2: Another record updated in a way that violates unique constraint on an uncommited data.

DECLARE
    l_dummy   NUMBER;
BEGIN
    SELECT 1
      INTO l_dummy
      FROM t
     WHERE pk_id = 1
    FOR UPDATE NOWAIT;

    UPDATE t
       SET code = 'CHANGED' || 1
     WHERE pk_id = 1;
END;

The query in session 2 runs forever.

dba_dml_locks marks this as "Non blocking"

enter image description here

In muti session environments this situation is more likely to happen.

I expect session 2 should atleast fail with an exception. But it doesn't.

In my scenario, both session 1 and session 2 are part of parallel executuion through DBMS_PARALLEL_EXECUTE and need to wait for each other to report success or failure. No exception in Session 2 would mean a never ending wait.

How to handle such scenarios?

Thanks

Edit:

I am trying to perform an atomic transaction in parallel using DBMS_PARALLEL_EXECUTE. i.e., Process and update multiple rows in a table parallely and only commit if all the chunks succeed else rollback all chunks. So each chunk waits for other chunks to report success/failure. Parallel processing with an instance is possible in other programming languages. I am trying to figure it out in Oracle DB.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source