轉自：http://blog.163.com/yiyun_8/blog/static/100336422201031505832337/

nowait的含義很多人都會誤解為“不用等待，立即執(zhí)行”。但實際上該關鍵字的含義是“不用等待，立即返回”
如果當前請求的資源被其他會話鎖定時，會發(fā)生阻塞，nowait可以避免這一阻塞，因為
If another user is in the process of modifying that row, we will get an ORA‐00054
Resource Busy error. We are blocked and must wait for the other user to finish with
it.
可以實驗下，我用pl/sql developer鎖定表game
SQL> select * from game where game_id =1;
返回一條記錄
 

SQL> select * from game where game_id=1 for update nowait;
select * from game where game_id=1 for update nowait
             *
ERROR位于第1行:
ORA-00054:資源正忙，要求指定NOWAIT
使用NOWAIT關鍵字，會報ORA‐00054的錯誤
 

如何來查看是什么資源造成這樣的情況呢？并且怎么解決呢？
查看鎖定的對象，用戶和會話
SQL> select lo.oracle_username,do.object_name,s.logon_time,lo.process,s.sid as s
ession_id
 2 from v$locked_object lo,v$session s,dba_objects do
 3 where lo.session_id = s.sid and do.object_id = lo.OBJECT_ID
 4 /
ORACLE_USERNAME
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
 

LOGON_TIME         PROCESS     SESSION_ID
------------------- ------------ ----------
NBA---用戶名稱
GAME---操作的對象
2009-08-04 10:55:15---登錄的時間     840:5176   10
 

使用as sysdba
根據(jù)sid查看具體的sql語句
selectsql_textfromv$session a,v$sqltext_with_newlines b
whereDECODE(a.sql_hash_value,0, prev_hash_value, sql_hash_value)=b.hash_value
 anda.sid=10;
begin :id := sys.dbms_transaction.local_transaction_id; end;
 

kill session
SQL> select sid,serial# from v$session where sid =10;
 

      SID   SERIAL#
---------- ----------
       10        23
SQL> alter system kill session '10,23';
 

系統(tǒng)已更改。
 

select * from game where game_id=1 for update nowait;
有數(shù)據(jù)返回了

當兩個用戶同時更新同一條記錄是， 使用select for update，后執(zhí)行者，會被阻塞，而使用select for update  nowait 則會拋出：ORA-00054 resource busy and acquire with NOWAIT specified 異常，告之用戶這一行已經(jīng)鎖定。