"ORA-00054: 資源正忙, 但指定以 NOWAIT 方式獲取資源, 或者超時失效"的快速解決方法今天在導一個臨時表的資料，匯出完成後準備清空資料，執行truncate命令時，遇到如下問題：

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

解決方法如下：
=========================================================
SQL> select session_id from v$locked_object;

SESSION_ID
----------
       56

SQL> SELECT sid, serial#, username, osuser FROM v$session where sid = 56;

       SID    SERIAL# USERNAME                       OSUSER
---------- ---------- ------------------------------ ------------------------------
       56         2088 ghb                          fy

SQL> ALTER SYSTEM KILL SESSION '56,2088';

System altered

執行完上述命令後，提示會話斷開。重新連線資料庫，然後執行truncate操作，成功！

以下是原理部分
==============
Oracle資料庫的鎖型別

根據保護的物件不同，Oracle資料庫鎖可以分為以下幾大類：DML鎖（data locks，資料鎖），用於保護資料的完整性；DDL鎖（dictionary locks，字典鎖），用於保護資料庫物件的結構，如表、索引等的結構定義；內部鎖和閂（internal locks and latches），保護資料庫的內部結構。

DML鎖的目的在於保證併發情況下的資料完整性，。在Oracle資料庫中，DML鎖主要包括TM鎖和TX鎖，其中TM鎖稱為表級鎖，TX鎖稱為事務鎖或行級鎖。

當Oracle 執行DML語句時，系統自動在所要操作的表上申請TM型別的鎖。當TM鎖獲得後，系統再自動申請TX型別的鎖，並將實際鎖定的資料行的鎖標誌位進行置位。這樣在事務加鎖前檢查TX鎖相容性時就不用再逐行檢查鎖標誌，而只需檢查TM鎖模式的相容性即可，大大提高了系統的效率。TM鎖包括了SS、SX、S、X 等多種模式，在資料庫中用0－6來表示。不同的SQL操作產生不同型別的TM鎖。

在資料行上只有X鎖（排他鎖）。在 Oracle資料庫中，當一個事務首次發起一個DML語句時就獲得一個TX鎖，該鎖保持到事務被提交或回滾。當兩個或多個會話在表的同一條記錄上執行 DML語句時，第一個會話在該條記錄上加鎖，其他的會話處於等待狀態。當第一個會話提交後，TX鎖被釋放，其他會話才可以加鎖。

當Oracle資料庫發生TX鎖等待時，如果不及時處理常常會引起Oracle資料庫掛起，或導致死鎖的發生，產生ORA-60的錯誤。這些現象都會對實際應用產生極大的危害，如長時間未響應，大量事務失敗等。

悲觀封鎖和樂觀封鎖

一、悲觀封鎖
鎖在使用者修改之前就發揮作用：
Select ..for update（nowait)
Select * from tab1 for update
使用者發出這條命令之後，oracle將會對返回集中的資料建立行級封鎖，以防止其他使用者的修改。
如果此時其他使用者對上面返回結果集的資料進行dml或ddl操作都會返回一個錯誤資訊或發生阻塞。
1：對返回結果集進行update或delete操作會發生阻塞。
2：對該表進行ddl操作將會報：Ora-00054:resource busy and acquire with nowait specified.

原因分析
此時Oracle已經對返回的結果集上加了排它的行級鎖，所有其他對這些資料進行的修改或刪除操作都必須等待這個鎖的釋放，產生的外在現象就是其他的操作將發生阻塞，這個這個操作commit或rollback.
同樣這個查詢的事務將會對該表加表級鎖，不允許對該表的任何ddl操作，否則將會報出ora-00054錯誤：:resource busy and acquire with nowait specified.

二、樂觀封鎖
樂觀的認為資料在select出來到update進取並提交的這段時間資料不會被更改。這裡面有一種潛在的危險就是由於被選出的結果集並沒有被鎖定，是存在一種可能被其他使用者更改的可能。因此Oracle仍然建議是用悲觀封鎖，因為這樣會更安全。

阻塞
定義：
當一個會話保持另一個會話正在請求的資源上的鎖定時，就會發生阻塞。被阻塞的會話將一直掛起，直到持有鎖的會話放棄鎖定的資源為止。
4個常見的dml語句會產生阻塞
INSERT
UPDATE
DELETE
SELECT…FOR UPDATE

INSERT

Insert發生阻塞的唯一情況就是使用者擁有一個建有主鍵約束的表。當2個的會話同時試圖向表中插入相同的資料時，其中的一個會話將被阻塞，直到另外一個會話提交或會滾。一個會話提交時，另一個會話將收到主鍵重複的錯誤。回滾時，被阻塞的會話將繼續執行。

UPDATE 和DELETE當執行Update和delete操作的資料行已經被另外的會話鎖定時，將會發生阻塞，直到另一個會話提交或會滾。

Select …for update

當一個使用者發出select..for update的錯作準備對返回的結果集進行修改時，如果結果集已經被另一個會話鎖定，就是發生阻塞。需要等另一個會話結束之後才可繼續執行。可以透過發出 select… for update nowait的語句來避免發生阻塞，如果資源已經被另一個會話鎖定，則會返回以下錯誤：Ora-00054:resource busy and acquire with nowait specified.

死鎖-deadlock
定義:當兩個使用者希望持有對方的資源時就會發生死鎖.
即兩個使用者互相等待對方釋放資源時,oracle認定為產生了死鎖,在這種情況下,將以犧牲一個使用者作為代價,另一個使用者繼續執行,犧牲的使用者的事務將回滾.
例子：
1：使用者1對A表進行Update，沒有提交。
2：使用者2對B表進行Update，沒有提交。
此時雙反不存在資源共享的問題。
3：如果使用者2此時對A表作update,則會發生阻塞，需要等到使用者一的事物結束。
4：如果此時使用者1又對B表作update，則產生死鎖。此時Oracle會選擇其中一個使用者進行會滾，使另一個使用者繼續執行操作。
起因:
Oracle的死鎖問題實際上很少見，如果發生，基本上都是不正確的程式設計造成的，經過調整後，基本上都會避免死鎖的發生。

DML鎖分類表

表1 Oracle的TM鎖型別
鎖模式鎖描述解釋 SQL操作
0 none
1 NULL 空 Select
2 SS(Row-S) 行級共享鎖，其他物件只能查詢這些資料行 Select for update、Lock for update、Lock row share
3 SX(Row-X) 行級排它鎖，在提交前不允許做DML操作 Insert、Update、Delete、Lock row share
4 S(Share) 共享鎖 Create index、Lock share
5 SSX(S/Row-X) 共享行級排它鎖 Lock share row exclusive
6 X(Exclusive) 排它鎖 Alter table、Drop able、Drop index、Truncate table 、Lock exclusive

1.關於V$lock表和相關檢視的說明

Column    Datatype                           Description
ADDR    RAW(4 |8)        Address of lock state object
KADDR       RAW(4 |8)    Address of lock
SID         NUMBER                                       Identifier for session holding or acquiring the lock
TYPE    VARCHAR2(2)                                  Type of user or system lock. The locks on the user types are obtained by user applications. Any process that is blocking   others is likely to be holding one of these locks. The user type locks are:TM - DML enqueue TX - Transaction enqueue UL - User supplied
--我們主要關注TX和TM兩種型別的鎖
--UL鎖使用者自己定義的，一般很少會定義，基本不用關注
--其它均為系統鎖，會很快自動釋放，不用關注
ID1          NUMBER                                           Lock identifier #1 (depends on type)
ID2          NUMBER Lock identifier #2 (depends on type)
---當lock type 為TM時，id1為DML-locked object的object_id
---當lock type 為TX時，id1為usn+slot，而id2為seq。
--當lock type為其它時，不用關注

LMODE              NUMBER                       Lock mode in which the session holds the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
--大於0時表示當前會話以某種模式佔有該鎖，等於0時表示當前會話正在等待該鎖資源，即表示該會話被阻塞。
--往往在發生TX鎖時，伴隨著TM鎖，比如一個sid=9會話擁有一個TM鎖，一般會擁有一個或幾個TX鎖，但他們的id1和id2是不同的

REQUEST       NUMBER              Lock mode in which the process requests the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
--大於0時，表示當前會話被阻塞，其它會話佔有改鎖的模式
CTIME NUMBER              Time since current mode was granted
BLOCK         NUMBER              A value of either 0 or 1, depending on whether or not the lock in question is the blocker.

2.其它相關檢視說明
檢視名描述主要欄位說明
v$session 查詢會話的資訊和鎖的資訊。
sid,serial#：表示會話資訊。
program：表示會話的應用程式資訊。
row_wait_obj#：表示等待的物件,和dba_objects中的object_id相對應。
lockwait :該會話等待的鎖的地址,與v$lock的kaddr對應.
v$session_wait 查詢等待的會話資訊。 sid：表示持有鎖的會話資訊。
Seconds_in_wait：表示等待持續的時間資訊
Event：表示會話等待的事件，鎖等於enqueue

dba_locks 對v$lock的格式化檢視。
Session_id：和v$lock中的Sid對應。
Lock_type：和v$lock中的type對應。
Lock_ID1：和v$lock中的ID1對應。
Mode_held,mode_requested：和v$lock中的lmode,request相對應。

v$locked_object 只包含DML的鎖資訊，包括回滾段和會話資訊。
Xidusn,xidslot,xidsqn：表示回滾段資訊。和v$transaction相關聯。
Object_id：表示被鎖物件標識。
Session_id：表示持有鎖的會話資訊。
Locked_mode：表示會話等待的鎖模式的信息，和v$lock中的lmode一致。

以下是命令列部分
================
1.查詢資料庫中的鎖

select * from v$lock;
select * from v$lock where block=1;

2.查詢被鎖的物件

select * from v$locked_object;

3.查詢阻塞
查被阻塞的會話
select * from v$lock where lmode=0 and type in ('TM','TX');

查阻塞別的會話鎖
select * from v$lock where lmode>0 and type in ('TM','TX');

4.查詢資料庫正在等待鎖的程式
select * from v$session where lockwait is not null;

5.查詢會話之間鎖等待的關係
select a.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from v$lock a,v$lock b
where a.id1=b.id1 and a.id2=b.id2 and a.block=1 and b.block=0;

6.查詢鎖等待事件
select * from v$session_wait where event='enqueue';

解決方案：
--首先得到被鎖物件的session_id
select session_id from v$locked_object;

--透過上面得到的session_id去取得v$session的sid和serial#，然後對該程式進行終止。
SELECT sid, serial#, username, osuser FROM v$session where sid = session_id;

--最後殺會話
ALTER SYSTEM KILL SESSION 'sid,serial';

oracle鎖表問題處理 v$lock v$locked_object

相關文章