Skip to Main Content
  • Questions
  • UPDATE through MERGE. What happens if the row is deleted when merge is running?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Lasse.

Asked: October 09, 2023 - 2:10 pm UTC

Last updated: October 09, 2023 - 4:30 pm UTC

Version: 19c

Viewed 1000+ times

You Asked

Lets say we have the following tables:
WHENEVER SQLERROR EXIT

-- Clean if objects exist
set serveroutput on size 1000000
DECLARE
  l_cnt NUMBER;

  procedure drop_table(table_i IN varchar2) as
    l_cnt NUMBER;
  begin
    select count(*) into l_cnt from user_tables where table_name = table_i;
    if l_cnt > 0 then
      execute immediate 'drop table '||table_i||' purge';
      dbms_output.put_line(table_i ||' dropped.');
    end if;
  end;
BEGIN
  drop_table('CAR_AGREEMENT');
  drop_table('CAR');
  drop_table('CAR_RENTAL');
  drop_table('CUSTOMER');
END;
/
---------------------------------------
-- Table: CUSTOMER
---------------------------------------
create table CUSTOMER (
  id number constraint owner_pk primary key
);

insert into CUSTOMER(id) select level from dual connect by level <= 100000; 

---------------------------------------
-- Table: CAR
---------------------------------------
create table CAR (
  id          number constraint car_pk primary key,
  fk_customer number,
  constraint car_customer_fk foreign key(fk_customer) references customer(id)
);

insert into CAR select level, mod(level,60000)+1 from dual connect by level <= 1000000;

create table CAR_RENTAL (
  id          number constraint car_rental_pk primary key,
  fk_customer number,
  constraint car_rental_customer_fk foreign key(fk_customer) references customer(id)
);

insert into CAR_RENTAL select level, mod(level, 50000) + 50001 from dual connect by level <= 1000000;

---------------------------------------
-- Table: CAR_AGREEMENT: Used to update a second system
---------------------------------------
create table CAR_AGREEMENT (
  fk_customer       number constraint car_agreement_pk primary key,
  upd_type          number,
  constraint caragr_updtype_ck check (upd_type in (0,1,2,3,4)) 
  -- 0: update completed, 1: new agreement, 2: delete agreement, 3: process of updating new started, 4: process of deleting started
);

---------------------------------------
-- MERGE
---------------------------------------

merge into CAR_AGREEMENT a using (select distinct c.fk_customer 
                                         from CAR c
                                         UNION
                                         select distinct cr.fk_customer
                                         from CAR_RENTAL cr) o on (a.fk_customer = o.fk_customer )
        when matched then
          update set upd_type = 1 where upd_type in (-1,2,4)
        when not matched then
          insert (fk_customer, upd_type) values (o.fk_customer, 1);

update car_agreement set upd_type=4;
commit;


Then we want to run the following MERGE again as the OWNER:
merge into CAR_AGREEMENT a using (select distinct c.fk_customer 
                                         from CAR c
                                         UNION
                                         select distinct cr.fk_customer
                                         from CAR_RENTAL cr) o on (a.fk_customer = o.fk_customer )
        when matched then
          update set upd_type = 1 where upd_type in (-1,2,4)
        when not matched then
          insert (fk_customer, upd_type) values (o.fk_customer, 1);

Shortly after the merge is started I run the following in a second session as another user (DBA):
delete from <owner>.car_agreement where FK_CUSTOMER=100000; 
commit;
-- Output: 
-- 1 row deleted.
-- Commit complete.


The delete is not hanging and the deletion is completed. This means the merge has not gotten to the deleted row when the delete starts. The merge completes successfully without any error.
When checking the CAR_AGREEMENT table after the merge completes, the deleted row still exists. This means that the MERGE handled this correctly.
If I don't do the commit in the second session, the MERGE hangs (when "arriving" the deleted row). When I then commit in the second session, the MERGE completes, and the deleted row still exists.

I guess what happens is the following:
If the MERGE gets to the actual row before the delete, the delete will hang, and then complete after the MERGE completes. The update in the MERGE in then lost.
If the MERGE has not jet gotten to the row being deleted, the delete will go through. When the MERGE get to the deleted row, it will hang until the delete is commited. When the delete is commited (either right away or later) the MERGE will do an INSERT, and the DELETE is lost.

Then I tried the same with the following update in the second session:
delete from <owner>.car_agreement where FK_CUSTOMER=1; 
commit;
-- Hangs. And completes when the MERGE is commited


In this case the MERGE arrive at the row which the second session wants to delete first, and lock the row. Now the delete hangs until the MERGE is commited.

In both cases we could get an lost update or a lost delete.

Do I have a correct understanding of what happens when a delete is running while another session is doing a MERGE update on the same row?



and Chris said...

Thanks for the test case!

Yes, that is my understanding too.

If you leave the sessions uncommitted, you'll see whichever started second blocked and waiting on "enq: TX - row lock contention". This is released as soon as the first commits.

The second statement then completes and wipes out the update/delete from the first. Like regular lost updates, the problem can happen if the sessions are not concurrent.





Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.