18.8 About DML Locking

Use automatic Data Manipulation Language (DML) in Oracle Application Express to update or delete rows of a table.

• About DML Locking
  Use automatic Data Manipulation Language (DML) in Oracle Application Express to update or delete rows of a table.
• APEX_DML_LOCK_WAIT_TIME
  Set the value of an application substitution string, an application item, or a page item to APEX_DML_LOCK_WAIT_TIME to control the DML operation.
• FSP_DML_LOCK_ROW
  Set the value of an application substitution string, an application item, or a page item to FSP_DML_LOCK_ROW to control the DML operation.

18.8.1 About DML Locking

Use automatic Data Manipulation Language (DML) in Oracle Application Express to update or delete rows of a table.

When you use automatic DML in Application Express to update or delete table rows, a transaction is initiated that:

• Locks the row.

• Verifies if the row has changed since it was displayed on the page.

• Issues the actual UPDATE or DELETE statement for the row.

In some environments where locking of rows is prevalent, you may want to control the DML operation and determine if the DML operation waits indefinitely, fails immediately, or waits for a specified period of time.

18.8.2 APEX_DML_LOCK_WAIT_TIME

Set the value of an application substitution string, an application item, or a page item to APEX_DML_LOCK_WAIT_TIME to control the DML operation.

APEX_DML_LOCK_WAIT_TIME supports the following values:

• NULL (the default), results in the same behavior as previous versions of Oracle Application Express, that is, wait indefinitely.

• 0 fails immediately if the row is locked by another database session.

• > 0 and the row is locked, waits for the specified number of seconds.

When set in an application, the value for APEX_DML_LOCK_WAIT_TIME applies to all UPDATE and DELETE DML operations using Automatic DML in the entire application. To control a specific Automatic DML process, update the value of APEX_DML_LOCK_WAIT_TIME before the Automatic DML process and reset it after the Automatic DML process. Note that this does not affect updates and deletes using tabular forms.

18.8.3 FSP_DML_LOCK_ROW

Set the value of an application substitution string, an application item, or a page item to FSP_DML_LOCK_ROW to control the DML operation.

FSP_DML_LOCK_ROW supports the following values:

• If the value is set to FALSE, then no SELECT FOR UPDATE is issued.

• If the value is anything other than FALSE, the default behavior of SELECT FOR UPDATE is performed when issuing an UPDATE or DELETE DML operation using Automatic DML.