Oracle 11g lock Enhancement

In Oracle 11g, the lock mechanism is further enhanced, mainly reflected in the following three aspects:

• Serializing Locks
• Locking Tables Explicitly
• Sharing Locks

 

Serializing Locks

Some people also generalize this feature into DDL Wait options. In a Prod Database, DBA tries to change the table named SALES and add a column of TAX_CODE to it. This is a common task. When you execute an SQL statement similar to the following:

SQL> alter table sales add (tax_code varchar2(10));

We often encounter such errors rather than "Table altered:

alter table sales add (tax_code varchar2(10)) *ERROR at line 1:ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The error message describes that the table may currently be used by a transaction, so it is unlikely to obtain the exclusive lock for the table. Of course, the rows in the table will not be locked forever. When the session executes the commit action, it will release the lock on these rows, but before that, due to the long release period, other sessions may update other rows in the table-in this way, the time to obtain the table's exclusive lock disappears. In a typical business environment, window locking tables in an exclusive manner opens regularly, but DBA may not be able to execute the alter command at that time.

Of course, DBA can also repeatedly type the same command until it gets an exclusive lock or fails (both take the first ).

In Oracle Database 11GDBA has a better choice: DDL Wait option. She can execute the following command:

SQL> alter session set ddl_lock_timeout = 10; Session altered.

Now, if the DDL statement in the session is not exclusively locked, no error message is displayed. Instead, it will wait for 10 seconds. Within these 10 seconds, the DDL operation will be retried until the operation is successful or times out (the two will first be used ). If you execute the following command:

SQL> alter table sales add (tax_code varchar2(10));

This statement will be suspended and no error message will be displayed. In this way, DBA outsourced repeated attempts to Oracle Database 11.G(Like retrying a busy number through a program), instead of having to repeat it over and over again to get the elusive exclusive lock-up time available.

Since everyone encountered the same problem when changing tables during system busy periods, they found this new feature very helpful. Therefore, DBA would like to know whether the behavior can be set as the default behavior, so that it does not need to execute the alter session Statement every time?

Yes, yes. If you executeAlter system set DDL_LOCK_TIMEOUT = 10, The session will automatically wait for this period of time during the DDL operation. And any otherALTER SYSTEMStatement.ALTER SESSIONStatement overwrite.

Note: The value range of the DDL_LOCK_TIMEOUT parameter is 0-1000000 (seconds ).

Locking Tables Explicitly

We know that in versions earlier than Oracle 11, when a lock table command is issued, if other sessions hold a LOCK on the TABLE, at this time, an error will be returned indefinitely or directly without waiting (NOWAIT clause. In 11g, the new wait option allows a lock table operation to wait for a period of time to obtain the required lock until the timeout returns an error. If the required lock is obtained within the wait period, the command is successfully executed.

In 11g, the lock table command has a new syntax that allows us to specify a time to wait for the dml lock. The syntax is as follows:

Lock table... IN lockmode MODE [NOWAIT | WAIT integer]

Specify NOWAIT if you want the database to return control to you immediately. If the specified table, partition, or table subpartition is already locked by another user, the database returns a message.

Use the WAIT clause to indicate that the lock table statement shocould wait up to the specified number of seconds to acquire a DML lock. there is no limit on the value of the integer. if you specify neither NOWAIT or WAIT, the database waits indefinitely until the table is available, locks it, and returns control to you. when the database is executing DDL statements concurrently with DML statements, a timeout or deadlock can sometimes occur. the database detects such timeouts and deadlocks and returns an error.

■ Wait for up to 10 seconds for a DML lock:
Lock table hr.jobs in exclusive mode wait 10;
■ Do not wait if another user already has locked the table:
Lock table hr. employees in exclusive mode nowait;
■ Lock a table that is accessible through the remote_db database link:
Lock table hr. employees @ remote_db in share mode;

Sharing Locks

In the 11g, the following DDL operation no longer requires an exclusive locks (X)], but a shared lock [exclusive locks (SX)]. the advantage of this improvement is that DML operations are not blocked when these DDL operations are executed.

-CREATE INDEX ONLINE
-CREATE MATERIALIZED VIEW LOG
-ALTER TABLE ENABLE CONSTRAINT NOVALIDATE


In highly concurrent environments, the requirement of acquiring an exclusive lock for example at the end of an online index creation and rebuild cocould lead to a spike of waiting DML operations and, therefore, a short drop and spike of system usage. while this is not an overall problem for the database, this anomaly in system usage cocould trigger operating system alarm levels. this feature eliminates the need row exclusive locks, when creating or rebuilding an online index.