oracle 添加约束 resources busy,ora-00054 表被lock导致资源忙等待不能操作案例

8月20日

1.现场状态

时间：2012年8月20日

数据库版本：Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

操作系统平台：HP-UX

告警日志： more   alert_mdsoss.log

2.定位问题

报错现象：

ORA-00054: resource busy acquire with nowait specified        资源忙

协成日志报错：SQL ERR ：insert into table GNTCPCNN12082015     不能插入表

3.分析原因

一般像这种情况可能是这个表正在被使用，有可能有lock暂时不能使用，在dba权限下查看一下是否有v$locked_object，有的话把session干掉

SQL> select * from v$locked_object;

XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME  OS_USER_NAME    PROCESS  LOCKED_MODE

---------- ---------- ---------- ---------- ---------- ---------------- --------------- --------------------

13         13     681477   79913476        855 MDSOSS          mdsdb2          12897              6

7         41     600151   79914479        998 MDSOSS           npmuser         10468              3

7         41     600151   79914483        998 MDSOSS           npmuser         10468              3

7         41     600151   79914481        998 MDSOSS           npmuser         10468              3

19         27     701115   79913681       1032 MDSOSS         mdsdb3          8658               6

61         30     182840     153240         1059 MDSOSS         oracle             12738              3

61         30     182840        212            1059 MDSOSS         oracle             12738              3

61         30     182840        165            1059 MDSOSS         oracle             12738              3

30          7     545652   79915059        1073 MDSOSS         npmuser         20387              3

52         39     385940   79913551       1085 MDSOSS         mdsdb3          12960              6

51         17     461665   79914748       1090 MDSOSS         npmuser         15454              3

51         17     461665   79914756       1090 MDSOSS         npmuser         15454              3

51         17     461665   79914763       1090 MDSOSS         npmuser         15454              3

58         24     262815   79914375       1175 MDSOSS         npmuser         6369               3

58         24     262815   79914380       1175 MDSOSS         npmuser         6369               3

58         24     262815   79914378       1175 MDSOSS         npmuser         6369               3

10         46     680893   79913459       1179 MDSOSS         mdsdb2          2581               6

17 rows selected

SQL>  select * from v$locked_object;

XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME  OS_USER_NAME    PROCESS  LOCKED_MODE

---------- ---------- ---------- ---------- ---------- ---------------- --------------- --------------------

42         22     507847   79915291        976 MDSOSS           npmuser         25324              3

42         22     507847   79915263        976 MDSOSS           npmuser         25324              3

42         22     507847   79915282        976 MDSOSS           npmuser         25324              3

1          5     650009   79915671        989 MDSOSS           npmuser         1175               3

1          5     650009   79915675        989 MDSOSS           npmuser         1175               3

1          5     650009   79915676        989 MDSOSS           npmuser         1175               3

我进行了2次查找。第一次发现mdsdb2、mdsdb3用户操作的插入对象正在被锁定，并且锁的级别都是6级，级别非常高，导致资源被占用，如果此时在对表操作就会报ora-00054，后来查询了第二次，就没有mdsdb2、mdsdb3用户的锁对象了，此时就可以操作表了。

4.查询哪些用户下哪些表被锁住了

select A.sid, b.serial#,

2  decode(A.type,

3      'MR', 'Media Recovery',

4      'RT','Redo Thread',

5      'UN','User Name',

6      'TX', 'Transaction',

7      'TM', 'DML',

8      'UL', 'PL/SQL User Lock',

9      'DX', 'Distributed Xaction',

10      'CF', 'Control File',

11      'IS', 'Instance State',

12      'FS', 'File Set',

13      'IR', 'Instance Recovery',

14      'ST', 'Disk Space Transaction',

15      'TS', 'Temp Segment',

16      'IV', 'Library Cache Invalida-tion',

17      'LS', 'Log Start or Switch',

18      'RW', 'Row Wait',

19      'SQ', 'Sequence Number',

20      'TE', 'Extend Table',

21      'TT', 'Temp Table',

22      'Unknown') LockType,

23  c.object_name,

24  b.username,

25  b.osuser,

26  decode(a.lmode,   0, 'None',

27              1, 'Null',

28              2, 'Row-S',

29              3, 'Row-X',

30              4, 'Share',

31              5, 'S/Row-X',

32              6, 'Exclusive', 'Unknown') LockMode,

33  B.MACHINE,D.SPID

34  from v$lock a,v$session b,all_objects c,V$PROCESS D

35  where a.sid=b.sid and a.type in ('TM','TX')

36  and c.object_id=a.id1

37  AND B.PADDR=D.ADDR

38  ;

SID     SERIAL#     LOCKTYPE     OBJECT_NAME           USERNAME      OSUSER   LOCKMODE  MACHINE         SPID

--------------- ------------ ------------------- ----------------------- --------------- --------- ----------------------------- ------------

867      6254        DML               TMP2720003           MDSOSS        npmuser         RowX          TJGRAPP          28373

935      46865      DML              GNWEBBRW12082016  MDSOSS        mdsdb1    Exclusive     TJ-Unicom-Group-Analyse-02    4632

1120    33384     DML               TMP244701              MDSOSS        npmuser         RowX         TJGRAPP            844

1120      33384 DML                 TMP244702              MDSOSS        npmuser         RowX         TJGRAPP            844

1120      33384 DML                 TMP244703              MDSOSS        npmuser         Row-X        TJGRAPP            844

1030      53327 DML                 GNMMO12082016     MDSOSS        mdsdb3         Exclusive TJ-Unicom-Group-Analyse-02    5211

867       6254    DML                 TMP2720001            MDSOSS        npmuser         Row-X        TJGRAPP           28373

867       6254    DML                 TMP2720002            MDSOSS        npmuser         Row-X        TJGRAPP           28373

1014     46058  DML                 TMP748801              MDSOSS        npmuser         Row-X        TJGRAPP           3086

1014      46058 DML                 TMP748802              MDSOSS        npmuser         Row-X        TJGRAPP           3086

1014      46058 DML                 TMP748803              MDSOSS        npmuser         Row-X        TJGRAPP           3086

1176      22983 DML                 TMP790301              MDSOSS        npmuser         Row-X        TJGRAPP           3168

994        20324 DML                  TMP791601              MDSOSS        npmuser        Row-X        TJGRAPP           3170

13 rows selected

小结：例如 GNWEBBRW12082016 这个表现在已经被mdsdb1用户使用，在没有commit之前，其他用户是不能使用的，如果其他用户此时也想访问这个表就会发生ora-00054错误！就会有资源争用导致资源忙等待

解决方法：

1.重启数据库  startup  force

2.等待锁释放

3.强制kill会话

Leonarding

2012.8.20

天津&autumn

分享技术~收获快乐

Blog：http://space.itpub.net/26686207