Oracle使用append对表insert会阻塞表的其他会话DML操作

 

Oracle使用append对表insert会阻塞其他会话DML操作

 

快春节了，抽点时间把NNNNNNN久之前的东西整理记录。

insert /*+ append */ into会对表持有LOCKED_MODE=6的TM锁，导致其他对该表的DML都会被阻塞。

 

正常的insert into对比。

开启两个会话，sid1=173,sid2=489。

两个会话同时运行：

--173
11:18:07 SYS@test1(173)> insert into "ZKM"."T1" select * from "ZKM"."T1";

--489
11:18:08 SYS@test1(489)> insert into "ZKM"."T1" select * from "ZKM"."T1";

 

 

SQL运行期间查看该表锁以及等待事件情况，均持有3号锁，并且没有被阻塞：

11:22:10 SYS@test1(24)> select lo.session_id,l.TYPE,lo.LOCKED_MODE from v$locked_object lo,v$lock l where lo.object_id in (select object_id from dba_objects where owner='ZKM' and object_name='T1') and lo.session_id=l.sid and l.type='TM';

SESSION_ID TYPE   LOCKED_MODE
---------- ------ -----------
       489 TM               3
       173 TM               3

Elapsed: 00:00:00.03


11:21:12 SYS@test1(330)> select sid,event,status,state,blocking_session from v$session where sid in ('173','489');

       SID EVENT                               STATUS                   STATE                                                     BLOCKING_SESSION
---------- ----------------------------------- ------------------------ --------------------------------------------------------- ----------------
       173 buffer busy waits                   ACTIVE                   WAITED SHORT TIME
       489 buffer busy waits                   ACTIVE                   WAITED SHORT TIME

Elapsed: 00:00:00.00

 

 

 

 

接下来sid1=173执行append的insert，sid2=489还是执行普通的insert，先后执行：

--先173
11:42:47 SYS@test1(173)> insert /*+ append */ into "ZKM"."T1" select * from "ZKM"."T1";

--紧接着489
11:42:48 SYS@test1(489)> insert into "ZKM"."T1" select * from "ZKM"."T1";
卡住等待......

 

SQL运行期间查看该表锁以及等待事件情况，sid1=173先执行先持有6号模式的独占锁（X），阻塞了sid2=489，并且sid2=489想要REQUEST（请求）的锁模式为3：

11:42:50 SYS@test1(24)> select lo.session_id,l.TYPE,lo.LOCKED_MODE,l.REQUEST from v$locked_object lo,v$lock l where lo.object_id in (select object_id from dba_objects where owner='ZKM' and object_name='T1') and lo.session_id=l.sid and l.type='TM';

SESSION_ID TYPE   LOCKED_MODE    REQUEST
---------- ------ ----------- ----------
       489 TM               0          3
       173 TM               6          0

Elapsed: 00:00:00.02

11:43:39 SYS@test1(330)> select sid,event,status,state,blocking_session from v$session where sid in ('173','489');

       SID EVENT                               STATUS                   STATE                                                     BLOCKING_SESSION
---------- ----------------------------------- ------------------------ --------------------------------------------------------- ----------------
       173 direct path write                   ACTIVE                   WAITED SHORT TIME
       489 enq: TM - contention                ACTIVE                   WAITING                                                                173

Elapsed: 00:00:00.00

 

 

通过对比知道，Hint的append去insert数据，需要注意使用场景，避免阻塞DML引起的生产问题。