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.

Note:the waiting period indicate in the wait statement,will override the ddl_lock_timeout value.
Reduced Need for Exclusive Locks
When you perform an operation such as online index creation or rebuild, there is a requirement for acquiring an exclusive DML lock. In a database that has heavy concurrent usage, this requirement of applying a DML exclusive lock to a table leads to a severe drop in performance, as user sessions are kept waiting for the online operation to complete. Oracle Database 11g removes the requirement for an exclusive lock on tables during the following operations:

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

参考至:《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》
      http://space.itpub.net/22664653/viewspace-703365
      http://www.oracle-base.com/articles/11g/ddl-lock-timeout-11gr1.php

本文原创，转载请注明出处、作者

如有错误，欢迎指正

邮箱:[e-mail]