![on_tsql_error option [compatibility]](gif/backward.gif)
Controls locking behavior for the wait_for_commit option.
On, Off
Off
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 version 5.x.x applications to version 8.0.x or later by mimicking version 5.x.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 would 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 would not be allowed to commit (unless it has somehow obtained exclusive locks on all of the matching foreign rows).