Oracle 11g Locking Enhancement(原創)

Locking Enhancements

Oracle Database 11g provides more efficient capabilities relating to the implementation of object locking. These new capabilities include allowing a DDL lock to wait for a DML lock instead of failing if it can’t get one right away. In addition, the database makes less use of exclusive locks. Allowing DDL Locks to Wait for DML Locks

One of the problems with DDL statements is that if they can’t immediately obtain a DML lock on the tables, they fail. In Oracle Database 11g, you can specify a time interval for which the DDL statement will wait for a DML lock, instead of the DDL failing automatically when it can’t get an immediate DDL lock. Use the new ddl_lock_timeout parameter to specify the length of time a DDL statement can wait for a DML statement. The default value of zero for this parameter produces the default Oracle behavior. Execute the alter session statement, shown here, to set the duration that the DDL statement can wait for a DML lock:

SQL> alter session set ddl_lock_timeout = 30;

Session altered.

The alter session statement here will enable a DDL statement to wait for 30 seconds for a necessary DML lock, after which the DDL statement fails. You can set a value as high as 1,000,000 seconds (11.5 days) for the ddl_lock_timeout parameter.

Here's an example:

session 1

建立測試表

yang@rac1>create table yangtab as select * from all_objects where rownum <200;

表已建立。

對表執行DML

yang@rac1>update yangtab set object_name='yangql';

已更新199行。

yang@rac1>show parameter ddl_lock

NAME                                 TYPE        VALUE

------------------------------------ ----------- ----------

ddl_lock_timeout                     integer     0

yang@rac1>update yangtab set wner='yangql';

已更新199行。

session 2 對表執行ddl操作

yang@rac1>alter table yangtab drop column owner;

alter table yangtab drop column owner

            *

第 1 行出現錯誤:

ORA-00054: 資源正忙, 但指定以 NOWAIT 方式擷取資源, 或者逾時失效

已用時間:  00: 00: 00.01 --注意時間

設定 ddl_lock_timeout =10 秒，實作DDL語句等待DML語句

yang@rac1>alter session set ddl_lock_timeout=10;

會話已更改。

已用時間:  00: 00: 00.10

yang@rac1>alter table yangtab drop column owner;

alter table yangtab drop column owner

            *

第 1 行出現錯誤:

ORA-00054: 資源正忙, 但指定以 NOWAIT 方式擷取資源, 或者逾時失效

已用時間:  00: 00: 10.03 --等待時間接近10秒

yang@rac1>alter session set ddl_lock_timeout=8;改為8秒

會話已更改。

已用時間:  00: 00: 00.02

yang@rac1>alter table yangtab drop column owner;

alter table yangtab drop column owner

            *

第 1 行出現錯誤:

ORA-00054: 資源正忙, 但指定以 NOWAIT 方式擷取資源, 或者逾時失效

已用時間: 00: 00: 08.01 --等待8秒

yang@rac1>alter session set ddl_lock_timeout=0;

會話已更改。

已用時間:  00: 00: 00.00

yang@rac1>alter table yangtab drop column owner;

alter table yangtab drop column owner

            *

第 1 行出現錯誤:

ORA-00054: 資源正忙, 但指定以 NOWAIT 方式擷取資源, 或者逾時失效

已用時間:  00: 00: 00.00

Note:The dl_lock_timeout default value is 0,that is to say,it'll return the error message once you execute the DDL statement and can't get the exclusive lock immediately。And this is parameter won't be effective for add column clause.

Alter table lock_tab add(description1 varchar2(30));

Explicit Table Locking

In addition to the new feature that lets you control the time for which a DDL statement waits to obtain a DML lock, Oracle Database 11g also has enhanced the lock table statement so you can specify the time a statement will wait for a DML lock on that table. Any DDL statement you issue on a table, such as a statement that adds a column, needs to acquire an exclusive DML lock on the table. Currently, an attempt to add a column to a table will fail if the database can’t immediately acquire an exclusive lock on the table. If your users frequently update a table to which you are planning to add a column, the new lock table syntax provides a way to control the time for which your DDL statements will wait to acquire the necessary exclusive DML lock on the table. Here’s the syntax of the enhanced lock table command:

lock table...in lockmode    mode [nowait | wait integer]

The mode parameter can take two values—wait and nowait. Here’s how the two options affect the waiting behavior for a DML lock:

• The nowait option immediately returns control to you if the table is already locked by others.
• The wait option lets the statement wait for execution for the period you specify. You can set any value for the nowait parameter.

If you omit the mode parameter altogether, the database locks the table once it becomes available and returns control to you. Thus, the default behavior now is for a DDL statement to wait until it gets an exclusive DML lock, however long the wait may be.

• create index online
• create materialized view log
• alter table enable constraint novalidate