When Parallelism Bites - ORA-12860 on a _high Oracle Autonomous DB (OCI)

Recently, I was prototyping a small app using Oracle Autonomous Database on OCI. I didn’t think much and just picked the first available _high service name, not sensing the trouble ahead. After all, "_high" sounds better than "_low", right?

Some time later, I started noticing odd behavior, especially when deleting and re-inserting records into a table within the same transaction.

The table was simple:

CREATE TABLE foo (
    bar_id      VARCHAR2(255) PRIMARY KEY,
    bar_user_id VARCHAR2(255) NOT NULL,
    bar_content VARCHAR2(2000)
);

I had some records in there and was trying to delete them and re-insert both old and new ones:

DELETE FROM foo WHERE bar_user_id = 'some_user_id';

INSERT INTO foo (bar_id, bar_user_id, bar_content) VALUES ('old_id_1', 'some_user_id', 'new_content');
INSERT INTO foo (bar_id, bar_user_id, bar_content) VALUES ('old_id_2', 'some_user_id', 'new_content');
-- ...more inserts

Suddenly, I get this:

ORA-12860: deadlock detected while waiting for a sibling row lock

Not quite what I expected from such a simple operation.

I checked table settings, indexes, triggers - nothing stood out. Then it hit me - I remembered something about the DB services being optimized for different workloads. The actual issue turned out to be the session’s parallelism level when using the _high service. A quick look at gv$px_session from a DBA account confirmed degree value higher than 1.

Joining the v$session and v$transaction by saddr quickly gave me more insights. The initial DELETE was spawning 4 parallel slaves. Then the following INSERTs of the previously deleted rows hit the internal deadlock inside the same transaction.

Unsurprisingly, switching to the _low service name, which disables parallelism, resolved the issue.

If you ran into the ORA-12860 error for a similar and found this, I hope this quick explanation saved you a bit of head-scratching. At the time of writing, search results for this error aren’t too helpful.

See the OCI Oracle Autonomous Database service descriptions for more info.