select…for update nowait

nowait的含义很多人都会误解为“不用等待，立即执行”。但实际上该关键字的含义是“不用等待，立即返回”

如果当前请求的资源被其他会话锁定时，会发生阻塞，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

根据sid查看具体的sql语句

select sql_text from v$session a,v$sqltext_with_newlines b
where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value
  and a.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';

 

系统已更改。

 

select * from game where game_id=1 for update nowait;

有数据返回了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22111412/viewspace-611297/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22111412/viewspace-611297/