当前位置:首页 > 开发 > 数据库 > 正文

Oracle 11g Locking Enhancement(原创)

发表于: 2013-10-31   作者:czmmiao   来源:转载   浏览次数:
摘要: Locking EnhancementsOracle 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

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

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

如有错误,欢迎指正

邮箱:czmcj@163.com

Oracle 11g Locking Enhancement(原创)

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
Overview In Oracle Database 10g, Oracle introduced the new Transparent Data Encryption (TDE)
Automatic Diagnostic Repository The automatic diagnostic repository tracks all problems and i
较之前一版本,SQL Server 2005可以说是作出了根本性的革新。对于一般的编程人员来说,最具吸引力的
在第一部分中,我们讨论了APPLY和CTE这两个T-SQL Enhancement。APPLY实现了Table和TVF的Join,CTE通
Material Status Enhancement Introduction The material status feature was originally developed
Material Status Enhancement Introduction The material status feature was originally developed
Material Status Enhancement Introduction The material status feature was originally developed
原文地址:http://www.2cto.com/database/201208/150620.html 一、Oracle 下载 注意Oracle分成两个文
oracle 11g 装好以后好久木有动了,今天找健健来一起研究,真是好。 先把oracle服务打开,这家伙太
一、Oracle 下载 注意Oracle分成两个文件,下载完后,将两个文件解压到同一目录下即可。 路径名称中
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号