How to make MERGE serializable

2
votes

When doing concurrent MERGEs while every session uses a different value (shown as *** in the snippet below) for the primary key column id, everything is fine if I do it manually in 2 terminal sessions.

MERGE
 INTO x
   USING (SELECT *** as id FROM DUAL) MERGE_SRC
     ON (x.id = MERGE_SRC.id)
 WHEN MATCHED THEN UPDATE SET val = val + 1 WHERE id = ***
 WHEN NOT MATCHED THEN INSERT VALUES (***, 99);
COMMIT;

However, running a multi-threaded load test with 3 or more threads, I will relatively quickly run into ORA-08177 with locked table. Why is that? (And why is it non-deterministic in that it does not always happen when transactions overlap?)

The table was created using

create table x (id int primary key, val int);

SQL Server btw never throws exceptions with an equivalent MERGE statement, running the same experiment. That is even true when working on the same row simultaneously.

Is it because perhaps MERGE is not atomic, and the serializable mode runs optimistically, so that the race might only show with sufficient contention? Still, why does it happen even when not working on the same row concurrently?

Btw, my attempts to fix this using the strictest lock available were unsuccessful. So any ideas on how to make this atomic are very much appreciated. It looks like relaxing the isolation level would rid me of the exception, but risk inconsistencies in case there turn out to be 2 updates on the same row (otherwise why would it balk in serializable mode in the first place).

2
oraclemergeatomic
have you tried an update and an insert in a single transaction instead of using merge ? (merge can be a very quarky creature ...). – haki
You mean update and if rowcount=0 then insert? When in serializable mode, I guess I have to do a manual lock as well, right? – Evgeniy Berezovsky
can you explain why you insist on using seralization ? if session A and B are executing roughly at the same time and you need each session to operate on the state that existed when the transaction began then serialization is enough. that's it's purpose. – haki
Serialization is enough to make exceptions fly. However, I want my transactions to succeed while leaving a consistent state. When in serializable mode, it seems I have to use an unwieldy loop as suggested by @BobJarvis to get this to run, even though I never work on the same row in different sessions at the same time. Btw by 'serialization is enough' you mean to say 'serialization through the use of locks, without actually using the isolation mode serializable'. That would certainly make sense. – Evgeniy Berezovsky

2 Answers

3
votes

The exception you're seeing is a direct consequence of using strict serialization. If you have more than one transaction active simultaneously, each started with SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, when any one of them commits the others will get an ORA-08177. That's how strict serialization is enforced - the database throws an ORA-08177 in any session started with ISOLATION LEVEL SERIALIZABLE if another transaction commits into a table which the serializable session needs. So, basically, if you really need strict serialization you have to handle the ORA-08177's intelligently, as in the following:

DECLARE
  bSerializable_trans_complete  BOOLEAN := FALSE;
  excpSerializable              EXCEPTION;
  PRAGMA EXCEPTION_INIT(excpSerializable, -08177);
BEGIN
  <<SERIALIZABLE_LOOP>>
  WHILE NOT bSerializable_trans_complete
  LOOP
    BEGIN
      SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

      MERGE ...; -- or whatever

      COMMIT;

      bSerializable_trans_complete := TRUE;  -- allow SERIALIZABLE_LOOP to exit
    EXCEPTION
      WHEN excpSerializable THEN
        ROLLBACK;
        CONTINUE SERIALIZABLE_LOOP;
    END;
  END LOOP;  -- SERIALIZABLE_LOOP
END;

Serialization is not magic, and it's not "free" (where "free" means "I as the developer don't have to do anything to make it work properly"). It requires more planning and work on the part of the developer to have it function properly, not less. Share and enjoy.

2
votes

In Oracle, the SERIALIZABLE mode works optimistically, in contrast to e.g. SQL Server, which does pessimistic locking in that mode. Which means that in the latter case you can even concurrently change the same row without running into exceptions.

Despite the docs:

Oracle Database permits a serializable transaction to modify a row only if changes to the row made by other transactions were already committed when the serializable transaction began. The database generates an error when a serializable transaction tries to update or delete data changed by a different transaction that committed after the serializable transaction began:

load testing showed that exceptions may also get thrown when not working on the same row simultaneously, although that is not guaranteed, in contrast to when working on the same row, which will always result in an ORA-08177.