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引起的生产问题。
标签:对表,insert,lo,DML,ZKM,489,173,select From: https://www.cnblogs.com/PiscesCanon/p/17046034.html