RETRY_ROW_ON_CHANGE

RETRY_ROW_ON_CHANGE Hint Description

The documentation for the hint RETRY_ROW_ON_CHANGE is limited to say the least.  This is what the manual has to say on the subject

"This hint is valid only for UPDATE and DELETE operations. It is not supported for INSERT or MERGE operations. When you specify this hint, the operation is retried when the ORA_ROWSCN for one or more rows in the set has changed from the time the set of rows to be modified is determined to the time the block is actually modified. "

It has nothing to say on when or how this would happen.  I think in most cases this hint will have no affect whatsoever.  I would be interested in peoples view on how they have used this.

But one example where this may be useful, is if you have changed the default isolation level of the database.  In circumstances, where you are using optimistic locking or scenarios where phantom reads can be an issue, then I can see this hint being particularly useful.  That said there are not many applications around today that require the default isolation level on the database to be changed.

Phantom Reads

A phantom read occurs when a transaction reads a set of rows, and then another transaction inserts or deletes rows that meet the same query criteria. When the first transaction re-executes the same query, it sees the newly added or deleted rows, which appear as "phantoms" because they weren't there before. 

Optimistic Locking

Optimistic Concurrency Control (OCC) is a concurrency control method that assumes conflicts between transactions are rare. It prioritizes performance over strict data consistency by avoiding locking mechanisms.

Here's how it works:

1. Read and Update: A transaction reads a data item and modifies it without acquiring any locks.

2. Commit Check: Before committing the transaction, the system checks if any other transaction has modified the same data item since the initial read.

3. Conflict Resolution: If no other transaction has modified the data, the transaction commits successfully. If a conflict is detected, the transaction is aborted and rolled back or retried. The retry can be automatic, or the user is notified of the conflict and can retry the operation manually.

Published on 

Published 6th December 2024