[20180829]ora-00054.txt

--//以前写的，忘记贴出，现在补上。

--//上午在解决问题时遇到ora-00054错误,导致自己手忙脚乱的,自己心理素质还是存在一些问题.
--//就是在问题面前如何保持冷静,实际上开始出现ora-00054错误,是因为应用程序阻塞,一个update语句无法提交.
--//解锁后,后面出现错误是我自己输入命令.浪费许多时间.

--//自己还是在测试环境模拟错误看看如何解决:

1.环境:
SCOTT@book> @ ver1
PORT_STRING         VERSION    BANNER
------------------- ---------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

$ oerr ora 54
00054, 00000, "resource busy and acquire with NOWAIT specified or timeout expired"
// *Cause:  Interested resource is busy.
// *Action: Retry if necessary or increase timeout.


CREATE  PUBLIC DATABASE LINK LOOPBACK CONNECT TO SCOTT IDENTIFIED BY book USING 'localhost:1521/book:DEDICATED';

--//grant EXECUTE ON  dbms_lock to scott;
CREATE OR REPLACE FUNCTION sleep (seconds IN NUMBER)
RETURN NUMBER
is
BEGIN
  sys.dbms_lock.sleep(seconds);
  RETURN seconds;
END;
/

2.测试1:

create table empx as select * from emp;
create unique index pk_empx on empx(empno);

--//session 1:
select empx.*,sleep(2) from empx;

--//执行要28秒,有足够的时间切换.

--//session 2:
SCOTT@book> ALTER INDEX scott.pk_empx  INVISIBLE;
Index altered.

SCOTT@book> ALTER INDEX scott.pk_empx  VISIBLE;
Index altered.

--//可以发现在执行select时,修改索引属性不会出现ora-00054错误.

3.测试2:
--//session 1:
SCOTT@book> select * from empx where rownum=1 for update ;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

--//session 2:
SCOTT@book> ALTER INDEX scott.pk_empx  INVISIBLE;
ALTER INDEX scott.pk_empx  INVISIBLE
                  *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

--//可以发现如果事务没有提交或者回滚,不能修改索引属性,否者出现ora-00054错误.

4.测试3:
--//session 1:
select empx.*,sleep(2) from empx@loopback;

--//session 2:
SCOTT@book> ALTER INDEX scott.pk_empx  INVISIBLE;
Index altered.

SCOTT@book> ALTER INDEX scott.pk_empx  VISIBLE;
Index altered.

--//可以发现如果是被当作db link访问,修改索引属性不会出现ora-00054错误.

SCOTT@book> @ &r/viewlock
   SID    SERIAL# USERNAME   OSUSER     MACHINE    MODULE       LOCK_TYPE       MODE_HELD  MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER  OBJECT_TYP OBJECT_NAME          BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
   274          7 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction  Exclusive  None       65565      1652       PUBLIC SYNONYM    /bda290b6_HttpSessio No
                                                                                                                                              nBindingLi
--//OBJECT_NAME='/bda290b6_HttpSessionBindingLi'.

5.测试4:
--//session 1:
SCOTT@book> select * from empx where rownum=1 for update ;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

--//session 2:
SCOTT@book> select * from empx where rownum=1 for update ;
--//阻塞挂起!!

--//session 3:
SCOTT@book> ALTER INDEX scott.pk_empx  INVISIBLE;
ALTER INDEX scott.pk_empx  INVISIBLE
                  *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

--//出现阻塞,一定在该表上有事务出现冲突,修改索引属性一定出现ora-00054错误.

6.如何解决:

--//session 3:
SCOTT@book> SCOTT@book> @ &r/viewlock
   SID    SERIAL# USERNAME   OSUSER     MACHINE    MODULE       LOCK_TYPE       MODE_HELD  MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER  OBJECT_TYP OBJECT_NAME          BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
    41          9 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction  None       Exclusive  655368     23170                                             No    0000000084C35DF0
    41          9 SCOTT      oracle     gxqyydg4   SQL*Plus     TM DML(TM)      Row-X (SX) None       90677      0          SCOTT  TABLE      EMPX                 No    0000000084C35DF0
   274          7 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction  Exclusive  None       655368     23170                                             Yes
   274          7 SCOTT      oracle     gxqyydg4   SQL*Plus     TM DML(TM)      Row-X (SX) None       90677      0          SCOTT  TABLE      EMPX                 No

--//理论kill 掉block='YES'的sid,事务能结束事务就ok了.

SCOTT@book> alter system kill session '274,7' immediate ;
System altered.


SCOTT@book> @ &r/viewlock
   SID    SERIAL# USERNAME   OSUSER     MACHINE    MODULE       LOCK_TYPE       MODE_HELD  MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER  OBJECT_TYP OBJECT_NAME          BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
    41          9 SCOTT      oracle     gxqyydg4   SQL*Plus     TM DML(TM)      Row-X (SX) None       90677      0          SCOTT  TABLE      EMPX                 No
    41          9 SCOTT      oracle     gxqyydg4   SQL*Plus     TX Transaction  Exclusive  None       65566      1651       PUBLIC SYNONYM    /3a957d9d_HttpSessio No
                                                                                                                                              nContext

--//奇怪又出现一个OBJECT_NAME='/3a957d9d_HttpSessionContext'.要么等这个事务结束,要么kill会话.

SCOTT@book> alter system kill session '41,9' immediate ;
System altered.

SCOTT@book> ALTER INDEX scott.pk_empx  INVISIBLE;
Index altered.

SCOTT@book> ALTER INDEX scott.pk_empx  VISIBLE;
Index altered.

7.附上脚本:
$ cat viewlock.sql
column sid format 99999
column username format a10
column osuser format a10
column machine format a10
column lock_type format a15
column mode_held format a10
column mode_requested format a10
column lock_id1 format a10
column lock_id2 format a10
column owner format a6
column object_type format a10
column object_name format a20
column block format a5
column lockwait format a20

SELECT se.SID, se.serial#,se.username, se.osuser, se.machine,se.module,
       DECODE (lk.TYPE, 'TX', 'TX Transaction', 'TM', 'TM DML(TM)', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
       DECODE (lk.lmode,
               0, 'None',
               1, 'Null',
               2, 'Row-S (SS)',
               3, 'Row-X (SX)',
               4, 'Share',
               5, 'S/Row-X (SSX)',
               6, 'Exclusive',
               TO_CHAR (lk.lmode)
              ) mode_held,
       DECODE (lk.request,
               0, 'None',
               1, 'Null',
               2, 'Row-S (SS)',
               3, 'Row-X (SX)',
               4, 'Share',
               5, 'S/Row-X (SSX)',
               6, 'Exclusive',
               TO_CHAR (lk.request)
              ) mode_requested,
       TO_CHAR (lk.id1) lock_id1, TO_CHAR (lk.id2) lock_id2, ob.owner, ob.object_type, ob.object_name,
       DECODE (lk.BLOCK, 0, 'No', 1, 'Yes', 2, 'Global') BLOCK, se.lockwait
  FROM v$lock lk, dba_objects ob, v$session se
 WHERE lk.TYPE IN ('TX','TM', 'UL') AND lk.SID = se.SID AND lk.id1 = ob.object_id(+) order by 1;

--//注意如果rac系统,不能使用上述脚本.