The dml_lock_timeout Parameter in the DBMS_REDEFINITION Package

The dml_lock_timeout Parameter in the DBMS_REDEFINITION Package

Oracle’s DBMS_REDEFINITION.finish_redef_table procedure can now time out waiting for DML locks, as demonstrated by a 10-second timeout triggering ORA-42042 in Oracle 12c.

Why This Matters

Without the dml_lock_timeout parameter, the finish_redef_table procedure would wait indefinitely for exclusive table locks, risking operational delays or downtime. This highlights the tension between idealized transactional isolation and real-world concurrency conflicts, where blocking operations can cascade into system-wide failures.

Key Insights

• “8-hour App Engine outage, 2012”: Not directly relevant, but underscores the cost of unbounded lock waits.
• “Sagas over ACID for e-commerce”: Not applicable here, but illustrates trade-offs in transaction management.
• “Oracle 12c introduced dml_lock_timeout to mitigate lock contention during online redefinition.”

Working Example

SQL> EXEC DBMS_REDEFINITION.finish_redef_table('USEF', 'MYTBL', 'MYTBL_TEMP', dml_lock_timeout => 10);
ORA-42012: error occurred while completing the redefinition
ORA-42042: time out in acquiring DML lock during online redefinition
Elapsed: 00:00:11.11

Practical Applications

• Use Case: Set dml_lock_timeout to avoid indefinite waits during online table redefinition in high-concurrency environments.
• Pitfall: Overly aggressive timeout values may cause redefinition failures before data consistency is guaranteed.

References:

• https://dev.to/vahidusefzadeh/the-dmllocktimeout-parameter-in-the-dbmsredefinition-package-4lkl

---