The MERGE command in Oracle is a great tool for modifying data in a table; efficiently solving the old problem of insert collisions and updates of non-existent rows. Most of the time we use it becaue it “just works.” But, what really goes on when you issue a merge? In particular, which triggers fire? How does it work with interaction of other sessions, and how does statement restart impact it?
To examine these I’ll set up a small test table, a bunch of triggers, a logging table, and some code to populate the log as a statement executes.
CREATE TABLE mergetest
(
id INTEGER PRIMARY KEY,
val VARCHAR2(50)
);
CREATE TABLE mergetest_log
(
logtime TIMESTAMP,
logmessage VARCHAR2(100)
);
CREATE OR REPLACE PROCEDURE write_mergetest_log(p_message IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO mergetest_log(logtime, logmessage)
VALUES (SYSTIMESTAMP, SYS_CONTEXT('userenv', 'client_info') || ' ' || p_message);
COMMIT;
END;
CREATE OR REPLACE FUNCTION trigger_action
RETURN VARCHAR2
IS
BEGIN
RETURN CASE
WHEN INSERTING
THEN
'INSERT'
WHEN UPDATING
THEN
'UPDATE'
WHEN DELETING
THEN
'DELETE'
ELSE
'UNKNOWN'
END;
END;
CREATE OR REPLACE TRIGGER trg_before_delete
BEFORE DELETE
ON mergetest
BEGIN
write_mergetest_log($$plsql_unit || ' ' || trigger_action);
END;
CREATE OR REPLACE TRIGGER trg_before_insert
BEFORE INSERT
ON mergetest
BEGIN
write_mergetest_log($$plsql_unit || ' ' || trigger_action);
END;
CREATE OR REPLACE TRIGGER trg_before_update
BEFORE UPDATE
ON mergetest
BEGIN
write_mergetest_log($$plsql_unit || ' ' || trigger_action);
END;
CREATE OR REPLACE TRIGGER trg_after_delete
AFTER DELETE
ON mergetest
BEGIN
write_mergetest_log($$plsql_unit || ' ' || trigger_action);
END;
CREATE OR REPLACE TRIGGER trg_after_insert
AFTER INSERT
ON mergetest
BEGIN
write_mergetest_log($$plsql_unit || ' ' || trigger_action);
END;
CREATE OR REPLACE TRIGGER trg_after_update
AFTER UPDATE
ON mergetest
BEGIN
write_mergetest_log($$plsql_unit || ' ' || trigger_action);
END;
CREATE OR REPLACE TRIGGER trg_before_row_delete
BEFORE DELETE
ON mergetest
FOR EACH ROW
BEGIN
write_mergetest_log($$plsql_unit || ' ' || trigger_action);
END;
CREATE OR REPLACE TRIGGER trg_before_row_insert
BEFORE INSERT
ON mergetest
FOR EACH ROW
BEGIN
write_mergetest_log($$plsql_unit || ' ' || trigger_action);
END;
CREATE OR REPLACE TRIGGER trg_before_row_update
BEFORE UPDATE
ON mergetest
FOR EACH ROW
BEGIN
write_mergetest_log($$plsql_unit || ' ' || trigger_action);
END;
CREATE OR REPLACE TRIGGER trg_after_rowdelete
AFTER DELETE
ON mergetest
FOR EACH ROW
BEGIN
write_mergetest_log($$plsql_unit || ' ' || trigger_action);
END;
CREATE OR REPLACE TRIGGER trg_after_rowinsert
AFTER INSERT
ON mergetest
FOR EACH ROW
BEGIN
write_mergetest_log($$plsql_unit || ' ' || trigger_action);
END;
CREATE OR REPLACE TRIGGER trg_after_rowupdate
AFTER UPDATE
ON mergetest
FOR EACH ROW
BEGIN
write_mergetest_log($$plsql_unit || ' ' || trigger_action);
END;
So, that’s simple enough. Two tables, all the triggers do the same thing, simply logging who they are and what they are doing and the logging procedure uses an autonomous transaction so the progress can be captured even if the calling statements are rolled back.
You might wonder why I used so many triggers instead of a single composite trigger, or at least combining INSERT/UPDATE/DELETE of each trigger type. In fact, my early tests when writing this article did use a single trigger. The problem with that was I lost some granularity of information when statement restart occurred and some actions became undefined. I’ll highlight this in later example code and output.
Now, we’ll execute 3 merge statements, the first into the empty table, then a merge update of the row we just inserted, and then another merge that should create another new row. Then we’ll check the log table and see what happened.
clear screen
TRUNCATE TABLE mergetest;
TRUNCATE TABLE mergetest_log;
exec dbms_application_info.set_client_info('SESSION ONE');
exec write_mergetest_log('MERGE 1');
MERGE INTO mergetest t
USING (SELECT 1 id, 'mergeupdate' updateval, 'mergeinsert' insertval FROM DUAL) s
ON (t.id = s.id)
WHEN MATCHED
THEN
UPDATE SET t.val = s.updateval
WHEN NOT MATCHED
THEN
INSERT VALUES (s.id, s.insertval);
exec write_mergetest_log('COMMIT');
COMMIT;
exec write_mergetest_log('MERGE 2');
MERGE INTO mergetest t
USING (SELECT 1 id, 'mergeupdate' updateval, 'mergeinsert' insertval FROM DUAL) s
ON (t.id = s.id)
WHEN MATCHED
THEN
UPDATE SET t.val = s.updateval
WHEN NOT MATCHED
THEN
INSERT VALUES (s.id, s.insertval);
exec write_mergetest_log('COMMIT');
COMMIT;
exec write_mergetest_log('MERGE 3');
MERGE INTO mergetest t
USING (SELECT 2 id, 'mergeupdate' updateval, 'mergeinsert' insertval FROM DUAL) s
ON (t.id = s.id)
WHEN MATCHED
THEN
UPDATE SET t.val = s.updateval
WHEN NOT MATCHED
THEN
INSERT VALUES (s.id, s.insertval);
exec write_mergetest_log('COMMIT');
COMMIT;
select * from mergetest_log order by logtime;
Note, both insert and update statement-level triggers fire; but for the row-level triggers, only the operation actually performed are fired.
LOGTIME LOGMESSAGE ---------------------------- ----------------------------------------- 2018-01-07 15:41:43.337157 SESSION ONE MERGE 1 2018-01-07 15:41:43.386852 SESSION ONE TRG_BEFORE_INSERT INSERT 2018-01-07 15:41:43.387073 SESSION ONE TRG_BEFORE_UPDATE UPDATE 2018-01-07 15:41:43.404012 SESSION ONE TRG_BEFORE_ROW_INSERT INSERT 2018-01-07 15:41:43.437701 SESSION ONE TRG_AFTER_ROW_INSERT INSERT 2018-01-07 15:41:43.437873 SESSION ONE TRG_AFTER_INSERT INSERT 2018-01-07 15:41:43.438004 SESSION ONE TRG_AFTER_UPDATE UPDATE 2018-01-07 15:41:43.491317 SESSION ONE COMMIT 2018-01-07 15:41:43.524916 SESSION ONE MERGE 2 2018-01-07 15:41:43.552057 SESSION ONE TRG_BEFORE_INSERT INSERT 2018-01-07 15:41:43.552230 SESSION ONE TRG_BEFORE_UPDATE UPDATE 2018-01-07 15:41:43.552452 SESSION ONE TRG_BEFORE_ROW_UPDATE UPDATE 2018-01-07 15:41:43.552599 SESSION ONE TRG_AFTER_ROW_UPDATE UPDATE 2018-01-07 15:41:43.552715 SESSION ONE TRG_AFTER_INSERT INSERT 2018-01-07 15:41:43.552827 SESSION ONE TRG_AFTER_UPDATE UPDATE 2018-01-07 15:41:43.609417 SESSION ONE COMMIT 2018-01-07 15:41:43.641952 SESSION ONE MERGE 3 2018-01-07 15:41:43.669958 SESSION ONE TRG_BEFORE_INSERT INSERT 2018-01-07 15:41:43.670092 SESSION ONE TRG_BEFORE_UPDATE UPDATE 2018-01-07 15:41:43.670259 SESSION ONE TRG_BEFORE_ROW_INSERT INSERT 2018-01-07 15:41:43.670442 SESSION ONE TRG_AFTER_ROW_INSERT INSERT 2018-01-07 15:41:43.670551 SESSION ONE TRG_AFTER_INSERT INSERT 2018-01-07 15:41:43.670643 SESSION ONE TRG_AFTER_UPDATE UPDATE 2018-01-07 15:41:43.726528 SESSION ONE COMMIT 24 rows selected.
Next, let’s try it with a second session involved and see how the locking impacts the merge. I’ll color code the sessions to help indicate which session is producing which output. Green background for session 1, red background for session 2.
clear screen exec dbms_application_info.set_client_info('SESSION ONE'); TRUNCATE TABLE mergetest; TRUNCATE TABLE mergetest_log; INSERT INTO mergetest VALUES (1, 'start'); COMMIT; exec write_mergetest_log('UPDATE'); UPDATE mergetest set val = 'update' WHERE id = 1; pause "Run merge in other session" clear screen exec dbms_application_info.set_client_info('SESSION TWO'); SELECT * FROM mergetest; exec write_mergetest_log('MERGE'); MERGE INTO mergetest t USING (SELECT 1 id, 'mergeupdate' updateval, 'mergeinsert' insertval FROM DUAL) s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.val = s.updateval WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.insertval); exec write_mergetest_log('COMMIT'); COMMIT; exec write_mergetest_log('COMMIT'); COMMIT; exec write_mergetest_log('INSERT'); INSERT INTO mergetest VALUES (1, 'insert');
When run, the output looked like this…
15:54:31 SQL> exec dbms_application_info.set_client_info('SESSION ONE'); PL/SQL procedure successfully completed. 15:54:31 SQL> TRUNCATE TABLE mergetest; Table truncated. 15:54:32 SQL> TRUNCATE TABLE mergetest_log; Table truncated. 15:54:32 SQL> INSERT INTO mergetest VALUES (1, 'start'); 1 row created. 15:54:32 SQL> COMMIT; Commit complete. 15:54:32 SQL> exec write_mergetest_log('UPDATE'); PL/SQL procedure successfully completed. 15:54:32 SQL> UPDATE mergetest set val = 'update' WHERE id = 1; 1 row updated. 15:54:32 SQL> pause "Run merge in other session" "Run merge in other session" 15:54:41 SQL> exec dbms_application_info.set_client_info('SESSION TWO'); PL/SQL procedure successfully completed. 15:54:41 SQL> SELECT * FROM mergetest; ID VAL ---------- -------------------------------------------------- 1 start 15:54:41 SQL> exec write_mergetest_log('MERGE'); PL/SQL procedure successfully completed. 15:54:41 SQL> MERGE INTO mergetest t 15:54:41 2 USING (SELECT 1 id, 'mergeupdate' updateval, 'mergeinsert' insertval FROM DUAL) s 15:54:41 3 ON (t.id = s.id) 15:54:41 4 WHEN MATCHED 15:54:41 5 THEN 15:54:41 6 UPDATE SET t.val = s.updateval 15:54:41 7 WHEN NOT MATCHED 15:54:41 8 THEN 15:54:41 9 INSERT VALUES (s.id, s.insertval); 1 row merged. 15:54:54 SQL> exec write_mergetest_log('COMMIT'); PL/SQL procedure successfully completed. 15:54:54 SQL> COMMIT; Commit complete. exec write_mergetest_log('COMMIT'); 15:54:54 SQL> COMMIT; Commit complete. 15:54:54 SQL> exec write_mergetest_log('INSERT'); PL/SQL procedure successfully completed. 15:54:54 SQL> INSERT INTO mergetest VALUES (1, 'insert'); INSERT INTO mergetest VALUES (1, 'insert') * ERROR at line 1: ORA-00001: unique constraint (SDS.SYS_C0048605) violated
The logs then again show the statement-level triggers fire for both insert and update, regardless of what the merge actually does; but look at the merge in session two.
The before triggers fire a second time due to statement restart, but the activity is actually “UNKNOWN”, which I’ve highlighted in purple. Using before-triggers is always tricky with statement restart, but this adds an extra layer of complication. If you have a trigger that uses the “INSERTING/UPDATING/DELETING” activity functions to determine the action, on statement restart those triggers will fail. This is what happened to me when I used a single consolidated trigger in my initial tests. By using individual triggers for each action I could track exactly which activity is causing the trigger to fire even if the activity itself is undefined.
15:54:54 SQL> select * from mergetest_log order by logtime; LOGTIME LOGMESSAGE ----------------------------- -------------------------------------------- 2018-01-07 15:54:31.449622 SESSION ONE TRG_BEFORE_INSERT INSERT 2018-01-07 15:54:31.450485 SESSION ONE TRG_BEFORE_ROW_INSERT INSERT 2018-01-07 15:54:31.544272 SESSION ONE TRG_AFTER_ROW_INSERT INSERT 2018-01-07 15:54:31.544476 SESSION ONE TRG_AFTER_INSERT INSERT 2018-01-07 15:54:31.581790 SESSION ONE UPDATE 2018-01-07 15:54:31.599431 SESSION ONE TRG_BEFORE_UPDATE UPDATE 2018-01-07 15:54:31.599642 SESSION ONE TRG_BEFORE_ROW_UPDATE UPDATE 2018-01-07 15:54:31.599823 SESSION ONE TRG_AFTER_ROW_UPDATE UPDATE 2018-01-07 15:54:31.599954 SESSION ONE TRG_AFTER_UPDATE UPDATE 2018-01-07 15:54:40.794142 SESSION TWO MERGE 2018-01-07 15:54:40.820151 SESSION TWO TRG_BEFORE_INSERT INSERT 2018-01-07 15:54:40.820316 SESSION TWO TRG_BEFORE_UPDATE UPDATE 2018-01-07 15:54:53.233075 SESSION TWO TRG_BEFORE_ROW_UPDATE UPDATE 2018-01-07 15:54:53.233366 SESSION TWO TRG_BEFORE_UPDATE UNKNOWN 2018-01-07 15:54:53.233495 SESSION TWO TRG_BEFORE_INSERT UNKNOWN 2018-01-07 15:54:53.233703 SESSION TWO TRG_BEFORE_ROW_UPDATE UPDATE 2018-01-07 15:54:53.233857 SESSION TWO TRG_AFTER_ROW_UPDATE UPDATE 2018-01-07 15:54:53.233978 SESSION TWO TRG_AFTER_INSERT INSERT 2018-01-07 15:54:53.234079 SESSION TWO TRG_AFTER_UPDATE UPDATE 2018-01-07 15:54:53.262496 SESSION ONE INSERT 2018-01-07 15:54:53.296106 SESSION TWO COMMIT 2018-01-07 15:54:59.362455 SESSION ONE TRG_BEFORE_INSERT INSERT 2018-01-07 15:54:59.362682 SESSION ONE TRG_BEFORE_ROW_INSERT INSERT 23 rows selected.
While I’ve used merge many times I’ll admit I hadn’t really thought about the trigger implications as much as I should have. In particular I hadn’t considered the possibility of an UNKNOWN action inside of trigger and what I’d do to accommodate one when encountered. I hope you’ve found this useful as well.