`

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

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

如有错误,欢迎指正

邮箱:czmcj@163.com

0
0
分享到:
评论

相关推荐

    64位架构下rhel linux 6.4、oracle 11g双节点RAC搭建

    根据提供的文件标题、描述、标签以及部分内容,我们可以深入地探讨64位架构下RHEL Linux 6.4与Oracle 11g双节点RAC(Real Application Clusters)的搭建过程。以下是对这一主题的详细解析,旨在为读者提供一个全面的...

    oracle 11g partitioning

    ### Oracle 11g Partitioning关键技术点解析 #### 一、Oracle 11g Partitioning概述 Oracle Database 11g Release 2 (11.2) 的分区功能(Partitioning)是数据库管理系统的一项重要特性,它允许用户将大型表或索引...

    Oracle10g RAC集群技术.rar

    Oracle 10g RAC(Real Application Clusters)集群技术是Oracle数据库系统中的一种高可用性和高性能解决方案。它允许多个数据库实例同时访问同一物理数据库,以实现数据的共享和负载均衡,从而提高系统的可靠性和...

    oracle 11g rac搭建(VMware环境)

    ### Oracle 11g RAC搭建(VMware环境) #### 一、前期准备与环境配置 在进行Oracle 11g RAC(Real Application Clusters)集群的搭建之前,需要做好充分的准备工作,并确保所有环境均符合Oracle RAC的要求。 **...

    Oracle Database Transactions and Locking Revealed

    ### Oracle数据库中的事务处理与锁定揭秘 #### 一、引言 在开发高度并发和可扩展的数据库应用程序时,理解底层数据库如何管理事务至关重要。Oracle作为业界领先的数据库管理系统之一,其事务管理和锁定机制的设计...

    Oracle Database Transactions and Locking Revealed(Apress,2014).

    Oracle Database Transactions and Locking Revealed provides much-needed information for building scalable, high-concurrency applications and deploy them against the Oracle Database. Read this short, ...

    Windows server 2003 Enterprice + oracle10g 双机热备安装手册

    ### Windows Server 2003 Enterprise + Oracle 10g 双机热备安装手册 #### 一、概述 本文档旨在提供一个详尽的指南,帮助读者理解如何在 Windows Server 2003 Enterprise 版本上部署双机热备环境,并在此环境下...

    Oracle Locking Survival Guide

    《Oracle Locking Survival Guide》是针对Oracle数据库管理系统中锁定机制的一个深入指南,旨在帮助数据库管理员和开发人员理解和解决与锁定相关的问题。Oracle数据库是企业级应用广泛使用的数据库系统,其锁定机制...

    Optimistic Locking with Concurrency in Oracle

    乐观锁在Oracle数据库并发控制中的应用 乐观锁是一种在数据库管理系统中实现并发控制的方法,它假设在多数情况下读多写少的情况,因此在读取数据时不会加锁,只有在更新数据时才会检查在此期间是否有其他事务修改了...

    oracle 10G rac

    ### Oracle 10g RAC在Linux下的安装与配置详解 #### 一、Oracle 10g RAC简介 Oracle Real Application Clusters (RAC) 是一种数据库集群技术,允许多个Oracle数据库实例同时访问相同的数据库文件,从而提高系统的...

    Oracle+11g安装后参数规范设置.pdf

    ### Oracle 11g安装后参数规范设置详解 #### 一、引言 在完成Oracle 11g的安装及数据库构建之后,为了确保数据库能够高效稳定地运行,需要进行一系列参数配置与优化。这些参数调整不仅涉及RAC(Real Application ...

    Oracle_11G_R2(64) RAC VMware workstation 集群部署

    ### Oracle 11G R2 (64位) RAC 在 VMware Workstation 上的集群部署 #### 软件与硬件准备 ##### **一、软件准备** 为了搭建Oracle 11g R2 RAC集群环境,需要准备以下软件资源: - **Oracle 11g R2 RAC 安装介质**...

    oracle linux7.2 安装oracle 11.2.0.4全过程图文附各种bug解决方案

    1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="36000c2909828cac1b0ed9a17212b4f1f", SYMLINK+="asmdisk1", OWNER="oracle", GROUP="dba", MODE="0660" ``` 最后,...

    oracle10g 概念 oracel concepts

    Oracle 10g是Oracle公司推出的数据库管理系统的一个版本,它提供了强大的数据存储、管理与分析功能。"Oracle Concepts"是Oracle官方文档的一部分,主要讲解了Oracle数据库的基础概念、架构和工作原理,对于学习和...

    VMWareServerGSX3.2 LinuxAs4下安装Oracle10gRAC.doc

    在VMWare Server GSX 3.2环境下,在Linux As 4操作系统上安装Oracle 10g RAC是一项复杂的任务,涉及到多个步骤和配置。以下是详细的过程: 1. **修改共享存储设置**:为了支持Oracle RAC(Real Application ...

    精通Oracle10g PL_SQL编程

    Oracle 10g是一款强大的关系型数据库管理系统,其PL/SQL是Oracle特有的过程化语言,专为数据库操作设计。本书“精通Oracle10g PL/SQL编程”旨在深入讲解这一编程语言,帮助读者掌握在Oracle环境中进行高效数据库开发...

Global site tag (gtag.js) - Google Analytics