ORA-00054: 資源正忙

一、悲觀封鎖

鎖在使用者修改之前就發揮作用：

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仍然建議是用悲觀封鎖，因為這樣會更安全。

Oracle的TM鎖類型

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表和相關視圖的說明
TM - DML enqueue
TX - Transaction enqueue
UL - User supplied
--我們主要關注TX和TM兩種類型的鎖
--UL鎖使用者自己定義的，一般很少會定義，基本不用關注
--其它均為系統鎖，會很快自動釋放，不用關注

---當lock type 為TM時，id1為DML-locked object的object_id
---當lock type 為TX時，id1為usn+slot，而id2為seq。
---當lock type為其它時，不用關注      

---根據usn等查詢sql（不太準）      

select /*+ rule*/c.SQL_TEXT
  from v$transaction a, v$session b, v$sql c
where a.XIDUSN = &XIDUSN
   and a.XIDSLOT = &XIDSLOT
   and a.XIDSQN = &XIDSQN
   and a.ADDR = b.TADDR
   and b.SQL_ADDRESS = c.ADDRESS
   and b.SQL_HASH_VALUE = c.HASH_VALUE;      

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';

解決方案：
select session_id from v$locked_object; --首先得到被鎖對象的session_id
SELECT sid, serial#, username, osuser FROM v$session where sid = session_id; --通過上面得到的session_id去取得v$session的sid和serial#，然後對該程序進行終止。
ALTER SYSTEM KILL SESSION 'sid,serial' immediate;      

---建立索引時失敗報錯      

在建立語句中添加online，會話釋放資源之後，該語句會自動執行。      

create index sa.idx_test_1_id on sa.test_1 (id) online;