In Oracle 11g there are some new features related to DDL-locking which can be very helpful.
In previous releases a DDL statement always errors out respectively hangs in a hold if an exclusive lock (X) on a required resource is not available.
Let’s have a look at a little example:
The user HR looks a table in shared) mode by acquiring ROW LOCKS (RS) on 0 rows:
HR@orcl SQL> select * from employees where 1=2 for update;
no rows selected
User SYSTEM tries to acquire an EXLUSIVE LOCK (X) on the table:
SYSTEM@orcl SQL> LOCK TABLE hr.employees IN EXCLUSIVE MODE;
Here SYSTEM will wait until the shared table lock is released by HR although there are no rows locked explicitly!!! The SELECT FOR UPDATE does not only lock rows explicitly but also requires a shared table lock.
Already in 10g it was possible to specify NOWAIT in order not to hang in a hold situation but to let the statement error out immediately:
SYSTEM@orcl SQL> LOCK TABLE hr.employees IN EXCLUSIVE MODE NOWAIT;
*ERROR at line 1:ORA-00054:
resource busy and acquire with NOWAIT specified or timeout expired
As of Oracle 11g it is possible to specify a timeout period for which a lock request should wait before erroring out. This can be very helpful because it is very likely that just a few seconds later the EXCLUSIVE ENQUEUE might be available. We now have the chance to wait for it before the DDL statement fails:
SYSTEM@orcl SQL> LOCK TABLE hr.employees IN EXCLUSIVE MODE WAIT 10;
ERROR at line 1:ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Here the DDL statement errors out only after 10 seconds if the requested lock is still not available!
Forthermore Shared exclusive locks (SRX) have been improved in Oracle database 11g.
They are used now instead of Exclusive locks (X) for the following operations:
- CREATE INDEX ONLINE
- CREATE MATARIALIZED VIEW LOG
- ALTER TABLE ENABLE CONSTRAINT NOVALIDATE
In 11 g transactions can be active and hold implicit (RX) or explicit (RS) row locks on a segment which is undergoing one of the above mentioned operations. This can reduce wait times in highly concurrent configurations for example for rebuilding online indexes.
