How to make MERGE serializable

问题:

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).

回答1:

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:

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.