Error ORA-30926 in Update

[souldeath]

Recently one of our customers who uses Oracle 12C running in Exadata began to receive this error:

Ora-30926: Unable to Get to Stable Set of Rows in The Source Tables 99] Cause: The Stable Set of Rows Could Not Be Got Because of Large DML Activity or a non-deterministic Where Clause.
Action: Removes Any Non-Determistic Where Clauses and Reissue The DML.

From what I researched is a more common mistake of happening in a merge, but never had seen on an update.

This is the SQL that makes the update:

Select all

 
UPDATE available_numbers_view n 
SET    n.id_transact = ?, 
       n.date_updated =? 
WHERE  ROWNUM <=? 
AND    n.uid =? 
AND    n.region_code =? 
AND    n.status =? 
AND    n.blocked =? 
AND    n.reserved IS NULL 

The process executed by the application looks for a number available in this table and the reservation updating the ID_TRANSACT field and the rownum is filtered with 1.

The problem of this is that the application has multiple instances and this process began to run with a very high frequency, generating problems Lock and various "EnQ: TX - Row Lock Contention" events were observed in the AWR.

This is due to the update always return the same record in the Were Clause and several instances trying to update the same record.

I used a non-elegant solution by doing a select and sorting the results with dbms_random.value and filtering by rownum at the end to ensure that instances do not try to update the same record.

The application design does not allow using the Select for Update Skip Locked, which would be a better alternative.

Is anyone ever faced a similar problem?

[dr_gori]

I will move this topic to Exadata.