轉(zhuǎn)自：http://blog.163.com/yiyun_8/blog/static/100336422201031505832337/

nowait的含義很多人都會(huì)誤解為“不用等待，立即執(zhí)行”。但實(shí)際上該關(guān)鍵字的含義是“不用等待，立即返回”
如果當(dāng)前請求的資源被其他會(huì)話鎖定時(shí)，會(huì)發(fā)生阻塞，nowait可以避免這一阻塞，因?yàn)?br /> 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.
可以實(shí)驗(yàn)下，我用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關(guān)鍵字，會(huì)報(bào)ORA‐00054的錯(cuò)誤
 

如何來查看是什么資源造成這樣的情況呢？并且怎么解決呢？
查看鎖定的對象，用戶和會(huì)話
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---登錄的時(shí)間     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ù)返回了

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