Oracle 11g中lock的增強

在Oracle 11g中,lock機制進一步增強,主要體現在如下3個方面

• Serializing Locks
• Locking Tables Explicitly
• Sharing Locks

 

Serializing Locks

也有人把這個特性歸納為DDL Wait選項.在一個Prod Database中,DBA嘗試更改名為 SALES 的表，為其添加一列 TAX_CODE。這是很常見的任務,當執行了類似以下 SQL 陳述式：

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

往往我們會遇到這樣的錯誤，而非“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

錯誤訊息描述的是：該表目前可能正由一個事務使用，因此要獲得該表的獨佔鎖定不太可能。當然，表的行不會永遠鎖定。當會話執行提交動作後，會釋放對這些行的鎖定，但在此之前，由於解除鎖定期間很長，其他會話可能會更新表的其他行 — 這樣，獲得表的獨佔鎖定的時機又消失了。在典型的商務環境中，以獨佔方式鎖定表的視窗會定期開啟，但 DBA 可能無法恰好在那時執行 alter 命令。

當然，DBA也可以反覆鍵入相同的命令，直到獲得獨佔鎖定或者失敗（兩者取其先）。

在 Oracle 資料庫 11g 中，DBA有更好的選擇：DDL Wait 選項。她可以執行以下命令：

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

現在，如果會話中的 DDL 語句沒有獲得獨佔鎖定，也不會顯示錯誤訊息。相反，它將等待 10 秒鐘。在這 10 秒鐘內，它將不斷重試 DDL 操作，直到成功或逾時（兩者取其先）。如果執行以下命令：

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

該語句將掛起，並且不會顯示錯誤訊息。這樣，DBA 就將重複嘗試操作外包給了 Oracle 資料庫 11g（就像電話通過程式重試繁忙號碼），而不必反覆嘗試以獲得難以捉摸的獨佔鎖定可用時機。

由於在系統繁忙期間更改表時，每個人都遇到過相同的問題，他們都發現這個新特性非常有協助。因此，DBA很想知道是否可以將該行為設為預設行為，這樣就不需要每次都執行 ALTER SESSION 語句？

是的，可以。如果您執行 ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 10，會話將在 DDL 操作期間自動等待該時間段。與任何其他 ALTER SYSTEM 語句一樣，該語句可被 ALTER SESSION 語句覆蓋。

註：參數DDL_LOCK_TIMEOUT的設定範圍是0-1000000 (秒).

Locking Tables Explicitly

我們知道，在Oracle 11前的版本中，當發出一個LOCK TABLE命令時,如果正好有其他session對這個表持有了鎖，這時會無限期的等待或是直接不等待(NOWAIT clause )返回一個錯誤。在11g中，新增的wait option允許一個lock table操作等待一段時間去擷取需要的lock直到timeout返回錯誤，如果在wait的時間段內獲得了需要的lock，命令將成功執行。

在11g中，LOCK TABLE這個命令又有了一個新的文法，允許我們指定一個等待DML鎖的時間,文法如下：

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 should 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

在11g中，下面的DDL操作不再需要一個獨佔鎖[exclusive locks (X)]，而是一個共用鎖定[exclusive locks (SX)]，這樣改進的好處就是當執行這些DDL操作的時候不會阻塞DML操作。

– 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 could 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 could trigger operating system alarm levels. This feature eliminates the need row exclusive locks, when creating or rebuilding an online index.