Oracle MERGE INTO statement behavior in concurrent sessions

A question - if we have 2 concurrent sessions executing exactly the same MERGE INTO statement on a table... what will happen? Are we going to get one row inserted, two rows inserted, one inserted and one updated... an exception... what will happen?

Well... let's see in following experiment 😏

A SETUP

We have a table named TEST_MERGE with following structure:

CREATE TABLE TEST_MERGE

(

  UQ_COLUMN_1  NUMBER                           NOT NULL,

  UQ_COLUMN_2  NUMBER                           NOT NULL,

  SOME_TEXT    VARCHAR2(4000 BYTE)

)

LOGGING 

NOCOMPRESS 

NOCACHE

/

CREATE UNIQUE INDEX TEST_MERGE_PK ON TEST_MERGE

(UQ_COLUMN_1, UQ_COLUMN_2)

LOGGING

/

ALTER TABLE TEST_MERGE ADD (

  CONSTRAINT TEST_MERGE_PK

  PRIMARY KEY

  (UQ_COLUMN_1, UQ_COLUMN_2)

  USING INDEX TEST_MERGE_PK

  ENABLE VALIDATE)

/

And we have a MERGE INTO statement

MERGE INTO TEST_MERGE A

     USING (SELECT 1                       AS UQ_COLUMN_1,

                   2                       AS UQ_COLUMN_2,

                   'What will happen?'     AS SOME_TEXT

              FROM dual) B

        ON (A.UQ_COLUMN_1 = B.UQ_COLUMN_1 AND A.UQ_COLUMN_2 = B.UQ_COLUMN_2)

WHEN NOT MATCHED

THEN

    INSERT     (UQ_COLUMN_1, UQ_COLUMN_2, SOME_TEXT)

        VALUES (B.UQ_COLUMN_1, B.UQ_COLUMN_2, B.SOME_TEXT)

WHEN MATCHED

THEN

    UPDATE SET A.SOME_TEXT = B.SOME_TEXT;

There are 2 sessions connected to the same database schema.

SCENARIO 1 - table is empty

First, we execute MERGE INTO statement from one session and, as expected, a new record is inserted into table. Transaction is not yet committed because we want to see how will second session behave after executing the statement.

Then we execute the same MERGE INTO from second session.

Second session is waiting to process the statement... because first session inserted a record and locked it - remember, transaction is still opened (no commit or rollback).

Then we execute a commit in first session and finish the transaction. Record lock is removed.

Second session produces a unique constraint error! It also wanted to insert a record, because in a statement execution moment there was no persistent record in database and database decided to execute a WHEN NOT MATCHED part or merge statement! It hasn't checked actual state of records in a table (record exists or not) after record became persistent.

SCENARIO 2 - record already exists

In previous scenario a record was inserted in table and transaction was committed. Table is not empty any more.

If we repeat the process and execute the MERGE INTO statement in a first session, leave the transaction unfinished (no commit) and execute the MERGE INTO statement in a second session... the second session will again wait for the first session to finish a transaction and unlock the record.

After the first session commits a transaction and removes the record lock, second session will finish the MERGE statement successfully - without exception, like in previous scenario. A record already existed in a moment of statement execution and database decided to execute WHEN MATCHED part of MERGE statement and update the record.