0

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.

DeekuSen
  • 95
  • 2
  • 9
  • "I expect session 2 should atleast fail with an exception" - this is not a deadlock scenario so do not expect an ORA-0060 here. Did you checked gv$locked_objects or gv$session column final_blocking_session. – Pankaj Mar 24 '22 at 16:56
  • Yes its not a deadlock. But never ending update statement also is not correct. There should be a way to resolve this. Isn't it? – DeekuSen Mar 24 '22 at 16:57
  • The only way you can resolve lock is by killing the session causing lock. – Pankaj Mar 24 '22 at 16:58
  • How will this work in an application scenario? End users kill sessions? – DeekuSen Mar 24 '22 at 16:59
  • Yes end user will have to kill session or complete transaction - commit/rollback – Pankaj Mar 24 '22 at 17:09
  • Yes. But 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 error would mean this will go into loop – DeekuSen Mar 24 '22 at 17:12
  • In this case the implementation needs to be re-looked at. This is as-per-design in Oracle. I have pasted few images in answer to help diagnose the scenario. – Pankaj Mar 24 '22 at 17:22
  • What is your blocking session doing? If it’s part of a parallel execute task then it’s really got no excuse to be idle? – Andrew Sayer Mar 24 '22 at 17:39
  • Why the ‘nowait’? –  Mar 24 '22 at 18:12
  • Why do you try to run transactions that do not commit? Unique constraint is not violated because the value is not persisted until commit: it may be committed or may be changed to some other value, why should it fail other sessions? Please, provide more context of what you try to achieve. – astentx Mar 24 '22 at 19:38
  • 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. – DeekuSen Mar 24 '22 at 23:45

0 Answers0