enq: TX - row lock contention

-----数据库版本

SQL> select *From v$version;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

 

 

实验一：

SQL> create table ha as select *From dba_objects;

表已创建。

SQL> create table hb as select *From dba_objects where object_id<3000;

表已创建。

SQL> alter table ha add constraint pk_ha primary key(object_id);

表已更改。

SQL> alter table hb add constraints fk_hb foreign key(object_id) references ha(object_id);

表已更改。

SQL> select owner,constraint_name,constraint_type,table_name,index_name from dba_constraints  WHERE TABLE_NAME in ('HA','HB');

OWNER                                                        CONSTRAINT_NAME                C TABLE_NAME                     INDEX_NAME
------------------------------------------------------------ ------------------------------ - ------------------------------ ----------------
SCOTT                                                        FK_HB                          R HB
SCOTT                                                        PK_HA                          P HA                     PK_HA

session 1:

SQL> update ha set owner='SYS' WHERE object_id>10000 and object_id<50000;

已更新39805行。

 

session 2:

SQL> conn scott/tiger
已连接。
SQL> update ha set owner='SYS' WHERE OBJECT_ID>10000 AND OBJECT_ID<15000;

一直在等待

 

session  3：

 

SQL> select  sid ,event,WAIT_CLASS From v$session   where  WAIT_CLASS!='Idle';

       SID EVENT                                                            WAIT_CLASS
---------- ---------------------------------------------------------------- ---------------
       132 enq: TX - row lock contention                                    Application

 

 

session 4:

SQL> delete from hb where object_id>1000 and object_id<1500;

已删除488行。

 

session 5:

SQL> delete from hb where object_id>1000 and object_id<1500;

一直等待

SQL> select  sid ,event,WAIT_CLASS From v$session   where  WAIT_CLASS!='Idle';

       SID EVENT                                                            WAIT_CLASS
---------- ---------------------------------------------------------------- -------------------------
        69 enq: TX - row lock contention                                    Application
       194 enq: TX - row lock contention                                    Application
       199 SQL*Net message to client                                        Network

 

 

结论：当两个session同时更新和删除某一行（或是具有重叠部分），其中一个session必须可以正常更新删除，行级加锁，其他session不能修改，另外一个session必须等待前一个 session执行完，并且出现等待事件  enq: TX - row lock contention，前一个session提交后，后一个session才能执行。

 

 

实验二：

SQL> create table   ha  as select *From  emp where 0=1;

表已创建。

SQL> create table hb as select *from dept where 0=1;

表已创建。

SQL> alter table ha add constraints pk_ha primary key(EMPNO);

表已更改。

SQL> alter table hb add constraints pk_hb  primary key (deptno);

表已更改。

SQL> alter table ha add constraints fk_ha foreign key(deptno) references hb(deptno);

表已更改。

SQL> insert into hb select *From dept where deptno=10;

已创建 1 行。

SQL> select *From hb;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SQL> insert into ha select *From emp where deptno=10;

已创建3行。

 

SQL> select *From ha;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

同一个sessin插入主表和外表数据不会出现等待，不同session会出现等待

session 1：

SQL> insert into hb select *From dept where deptno=20;

已创建 1 行。

session  2：
SQL> insert into ha select *From emp where deptno=20;

等待

 

SQL> select  sid ,event,WAIT_CLASS From v$session   where  WAIT_CLASS!='Idle';

       SID EVENT                                                            WAIT_CLASS
---------- ---------------------------------------------------------------- ----------------
        71 enq: TX - row lock contention                                    Application

 结论：当具有主外键关系的两个表，主表插入数据未提交的情况下，外表插入数据就会出现等待主表提交，而出现等待事件 enq: TX - row lock contention.

 

SQL> select sess.sid,
  2       sess.serial#,
  3     lo.oracle_username,
  4        ao.object_name,
  5         lo.locked_mode
  6         from v$locked_object lo,
  7        dba_objects ao,
  8       v$session sess
  9     where ao.object_id = lo.object_id and lo.session_id = sess.sid;

 SID    SERIAL# ORACLE_USERNAME                OBJE LOCKED_MODE
---- ---------- ------------------------------ ---- -----------
 194         15 SCOTT                          HB             3
  71        303 SCOTT                          HB             3
 194         15 SCOTT                          HA             3
  71        303 SCOTT                          HA             3

SQL>  select s.sid,s.serial#,p.spid from v$process p,v$session s where p.addr=s.paddr and s.sid in(194,71);

 SID    SERIAL# SPID
---- ---------- ------------------------
  71        303 6056
 194         15 1376

SQL>  select sid,serial#,username,SQL_ID,BLOCKING_SESSION,BLOCKING_SESSION_STATUS from v$session where event='enq: TX - row lock contention';

 SID    SERIAL# USERNAME                       SQL_ID        BLOCKING_SESSION BLOCKING_SE
---- ---------- ------------------------------ ------------- ---------------- -----------
  71        303 SCOTT                          6tdj1bvp8hdcq              194 VALID

 

 

SQL> oradebug setospid  6056
Oracle pid: 21, Windows thread id: 6056, image: ORACLE.EXE (SHAD)

SQL>  oradebug dump processstate 10
已处理的语句

SQL>  oradebug tracefile_name
d:\app\administrator\diag\rdbms\ora11g\ora11g\trace\ora11g_ora_6056.trc

 

SQL> oradebug setospid  1376
Oracle pid: 31, Windows thread id: 1376, image: ORACLE.EXE (SHAD)
SQL>  oradebug dump processstate 10
已处理的语句
SQL>  oradebug tracefile_name
d:\app\administrator\diag\rdbms\ora11g\ora11g\trace\ora11g_ora_1376.trc

 

 

SQL> select event#,NAME,PARAMETER1,PARAMETER2,PARAMETER3 FROM v$event_name where name='enq: TX - row lock contention';

    EVENT# NAME                           PARAMETER1 PARAMETER2      PARAMETER3
---------- ------------------------------ ---------- --------------- ----------
       237 enq: TX - row lock contention  name|mode  usn<<16 | slot  sequence

 

 

SQL> select owner,index_name from dba_indexes where table_name='HA'
  2  ;

OWNER                          INDEX_NAME
------------------------------ ------------------------------
SCOTT                          PK_HA

SQL> select owner,index_name from dba_indexes where table_name='HB';

OWNER                          INDEX_NAME
------------------------------ ------------------------------
SCOTT                          PK_HB

SQL> select object_id from dba_objects where object_name='PK_HA';

 OBJECT_ID
----------
     85948

SQL> select object_id from dba_objects where object_name='PK_HB';

 OBJECT_ID
----------
     85949

SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         9         11       7891        803          3          7
         7         14       7893       2801          3         24

SQL> select usn,name from v$rollname where usn in(7,9);

       USN NAME
---------- ------------------------------
         7 _SYSSMU7_137577888$
         9 _SYSSMU9_1126410412$

SQL> select object_id,data_object_id from dba_objects where   object_name='HA';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     85946          85946

SQL> select object_id,data_object_id from dba_objects where   object_name='HB';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     85947          85947

SQL> select object_id,data_object_id from dba_objects where   object_name='PK_HA';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     85948          85948

SQL> select object_id,data_object_id from dba_objects where   object_name='PK_HB';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     85949          85949

 

 

 

 

 

 

参考http://hi.baidu.com/zhang_gt/item/a5647916cbfd77ed9813d604