exclusive锁导致expdp导出hang住

在我们进行expdp导出的时候,Oracle会对其加一个row share锁,假设如果有一个会话在这之前对该表加了exclusive锁,那么expdp就会hang住.之所以要加row share锁,是希望在导这个表的时候,表的结构不会发生改变,或者被删除等等.

---------session1

SQL> select owner,table_name from dba_tables where owner='TEST';

OWNER                          TABLE_NAME

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

TEST                           T2

TEST                           T1

SQL> lock table t1 in exclusive mode;

Table(s) Locked.

---------session2

[oracle@db10g datapump]$ expdp test/test directory=dp dumpfile=test.dmp logfile=test.log

Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 21 July, 2012 15:47:18

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/******** directory=dp dumpfile=test.dmp logfile=test.log

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 11.06 MB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

这个时候我们可以发现session2在导出的时候一直被hang住,我们来具体的看一看我们的expdp需要请求的锁.

SQL> select vs.sid, vp.program PROCESSNAME, vp.spid THREADID

  2  from   v$session vs,

  3         v$process vp ,

  4         dba_datapump_sessions dp

  5  where  vp.addr = vs.paddr(+) and

  6         vs.saddr = dp.saddr;

       SID PROCESSNAME                                      THREADID

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

       149 oracle@db10g.localdomain (TNS V1-V3)             7249

       146 oracle@db10g.localdomain (DM00)                  7253

       144 oracle@db10g.localdomain (DW01)                  7255

我们通过查询以上三个视图,可以看到sid,操作系统的process id,还有我们的process name.接下来我们看看这三个会话的等待事件.

SQL> select sid,event,p1,p2,p3, seconds_in_wait
2   from   v$session_wait
3   where  sid in (144,146,149);

       SID EVENT                                                 P1         P2         P3 SECONDS_IN_WAIT

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

       144 enq: TM - contention                          1414332418      51799          0             864

       146 wait for unread message on broadcast channel  2199568920 2199510048          0              60

       149 wait for unread message on broadcast channel  2199568920 2199510624          0             872

注意看142,这个session就是在wait的session.

SQL> select * from v$lock;

ADDR             KADDR              SID TY    ID1    ID2  LMODE  REQUEST   CTIME  BLOCK
---------------- ---------------- ----- -- ------ ------ ------ -------- ------- ------
00000000830E51B8 00000000830E51D8   165 XR      4      0      1        0    2704      0
00000000830E5260 00000000830E5280   165 CF      0      0      2        0    2704      0
00000000830E53B0 00000000830E53D0   165 RS     25      1      2        0    2701      0
00000000830E5458 00000000830E5478   167 PW      1      0      3        0    2507      0
00000000830E5500 00000000830E5520   166 RT      1      0      6        0    2701      0
00000000830E55A8 00000000830E55C8   144 TO   7429      1      3        0    1460      0
00000000830E5650 00000000830E5670   164 TS      3      1      3        0    2508      0
00000000830E57A0 00000000830E57C0   167 MR      1      0      4        0    2509      0
00000000830E5848 00000000830E5868   167 MR      2      0      4        0    2509      0
00000000830E58F0 00000000830E5910   167 MR      3      0      4        0    2509      0
00000000830E5998 00000000830E59B8   167 MR      4      0      4        0    2509      0
00000000830E5A40 00000000830E5A60   167 MR    201      0      4        0    2509      0
0000000082269B90 0000000082269BB8   159 TM  51799      0      6        0    1518      1
0000000082269CB0 0000000082269CD8   144 TM  51799      0      0        2    1443      0

我们可以看到144这个会话的ID1是51799,这里对应的是OBJECT ID,而159这个会话锁住了51799这个对象,所以我们可以通过DBA_OBJECTS来查看此对象.

SQL> select owner, object_name

  2  from  dba_objects

  3  where  object_id = 51799;

OWNER                          OBJECT_NAME

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

TEST                           T1

接下来我们要用oradebug进行hang的分析.

SQL> oradebug setospid 7255

Oracle pid: 22, Unix process pid: 7255, image: oracle@db10g.localdomain (DW01)

SQL> oradebug dump errorstack 3

Statement processed.

SQL> oradebug tracefile_name

/oracle/app/oracle/admin/db10g/bdump/db10g_dw01_7255.trc

ksedmp: internal or fatal error
Current SQL statement for this session:
LOCK TABLE "TEST"."T1" IN ROW SHARE MODE
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x835ab670        14  package body SYS.KUPD$DATA_INT
0x7dbbfee0      1633  package body SYS.KUPD$DATA
0x7dbc17f8     11005  package body SYS.KUPW$WORKER
0x7dbc17f8      2695  package body SYS.KUPW$WORKER
0x7dbc17f8      7050  package body SYS.KUPW$WORKER
0x7dbc17f8      1340  package body SYS.KUPW$WORKER
0x7db92c50         2  anonymous block

这里我们可以清楚的看到这个会话想对T1上row share锁,结果因为T1这个对象已经被另外一个会话上了exclusive模式的锁,所以此处就被hang住了,当我们对session1做commit或者rollback操作后,expdp才能继续往下走.

---------session1

SQL> commit;

Commit complete.

----------session2

. . exported "TEST"."T1"                                 9.327 MB  100064 rows

. . exported "TEST"."T2"                                 4.921 KB       2 rows

Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:

  /oracle/app/oracle/datapump/test.dmp

Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:22:55

参考文档:Expdp Hangs During Exporting Data Due to a Lock Situation [ID 756834.1]

分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *