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?