ASA Database Administration Guide
  Database Options
    Alphabetical list of options

OPTIMISTIC_WAIT_FOR_COMMIT

Function 

Controls locking behavior for WAIT_FOR_COMMIT option.

Allowed values 

ON, OFF

Default 

OFF

Description 

By default, OPTIMISTIC_WAIT_FOR_COMMIT is OFF.

Locking behavior is changed as follows when both OPTIMISTIC_WAIT_FOR_COMMIT and WAIT_FOR_COMMIT are ON:

• No locks are placed on primary key rows when adding a foreign key row without a matching primary key row.

• If a transaction adds a primary row, it will be allowed to commit only if the transaction has exclusive locks on all foreign rows that reference the primary row at the time the primary row is added.

This feature is for use in migrating 5.x applications to 8.x by mimicking 5.x locking behavior when transactions add foreign rows before primary rows (as long as no two transactions concurrently add foreign rows with the same key value).

Note that this option is not recommended for general use as there are a number of situations in which transactions will not be allowed to commit, including:

• If transactions concurrently add foreign rows with the same key value when the primary row does not exist, at most 1 of the transactions will be allowed to commit (the one that adds the corresponding primary row).

• If a transaction deletes a primary row and then adds it back, it likely will not be allowed to commit (unless it has somehow obtained exclusive locks on all of the matching foreign rows).