并行更新或插入会锁全表嘛？

我来答

2022-01-19

并行更新或插入会锁全表嘛？

session1：
SQL> create table test (id number,name varchar(20));
SQL> select distinct sid from v$mystat;
SID
----------
62
SQL> alter session force parallel dml;
SQL> insert /* +parallel(4) */ into test select rownum,'aa'||rownum from dual connect by level < 10000;

session2：
SQL> select distinct sid from v$mystat;
SID
----------
60
SQL> insert into test t values(0,'aa1');
-----hang住了

session1：
SQL> commit; Commit complete.
SQL> update /* +parallel(4) */ test set name='JJJJ' where id< 5000;
4999 rows updated.

session2：
SQL> insert into test t values(0,'aa1');
-----hang住了
SQL> select decode(request,0,'holder: ','waiter: ') ||
2 sid session_id,lmode, request, type
3 from v$lock
4 where (id1, id2, type) in (select id1, id2, type from v$lock where request > 0)
5 order by request;
SESSION_ID LMODE REQUEST TY
---------- ----- -------- --
holder: 62 6 0 TM
waiter: 60 0 3 TM

请问并行更新或插入会锁全表嘛？什么原理？

我来答

添加附件

问题补充

1条回答

默认

最新

cqiwen

    {
                instance: 1 (cqiwendb.cqiwendb1)
                   os id: 29023
              process id: 48, oracle@sdb01 (TNS V1-V3)
              session id: 17
        session serial #: 22255
    }
    is waiting for 'enq: TM - contention' with wait info:
    {
                      p1: 'name|mode'=0x544d0003
                      p2: 'object #'=0x1791a
                      p3: 'table/partition'=0x0
            time in wait: 8.735503 sec
           timeout after: never
                 wait id: 99
                blocking: 0 sessions
             current sql: insert into test2022 values(0,'aa1')
             short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+178<-ksliwat()+2066<-kslwai
tctx()+163<-kjusuc()+3400<-ksipgetctxi()+1759<-ksqcmi()+20798<-ksqgtlctx()+3501<-ktaiam()+712<-ktagetp_internal()+2006<-ktaadm()+258<-kksfbc()+2296<-opiexe()+
2330<-kpoal8()+2380<-opiodr()+917<-ttcpip()+1255<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-m
ain()+201<-__libc_start_main()+253
            wait history:
              * time between current wait and wait #1: 0.001121 sec
              1.       event: 'SQL*Net message from client'
                 time waited: 0.000132 sec
                     wait id: 98              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000111 sec
              2.       event: 'SQL*Net message to client'
                 time waited: 0.000003 sec
                     wait id: 97              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #2 and #3: 0.000026 sec
              3.       event: 'SQL*Net break/reset to client'
                 time waited: 0.000199 sec
                     wait id: 96              p1: 'driver id'=0x62657100
                                              p2: 'break?'=0x0
    }
    and is blocked by
 => Oracle session identified by:
    {
                instance: 1 (cqiwendb.cqiwendb1)
                   os id: 20694
              process id: 54, oracle@sdb01 (TNS V1-V3)
              session id: 395
        session serial #: 15131
    }
    **which is waiting for 'SQL*Net message from client'** with wait info:
    {
                      p1: 'driver id'=0x62657100
                      p2: '#bytes'=0x1
            time in wait: 16.711944 sec
           timeout after: never
                 wait id: 955
                blocking: 1 session
             current sql: <none>
             short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-read()+14<-ntpfprd()+117<-nsbasic_brc()+396<-nsbrecv()+6
9<-nioqrc()+495<-opikndf2()+978<-opitsk()+831<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_
start_main()+253
            wait history:
              * time between current wait and wait #1: 0.000021 sec
              1.       event: 'SQL*Net message to client'
                 time waited: 0.000005 sec
                     wait id: 954             p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000536 sec

*** 2022-01-19 17:26:05.763
              2.       event: 'PX Deq: Signal ACK RSG'
                 time waited: 0.000000 sec (last interval)
                 time waited: 0.015107 sec (total)
                     wait id: 950             p1: 'sleeptime/senderid'=0xa
                                              p2: 'passes'=0x2
              * time between wait #2 and #3: 0.000000 sec
              3.       event: 'PX Deq: reap credit'
                 time waited: 0.000016 sec
                     wait id: 953             
    }
 
Chain 1 Signature: **'SQL*Net message from client'<='enq: TM - contention'**
Chain 1 Signature Hash: 0x163c4cba

这是抓的包,参考下面第8条：
TM锁在下列场景中被申请：

1.在OPS(早期的RAC)中LGWR会以ID1=0 & ID2=0去申请该队列锁来检查 DML_LOCKS 在所有实例中是全0还是全非0。

当一个单表或分区需要做不同的表/分区操作时，ORACLE需要协调这些操作，所以需要申请该队列锁。
启用参考约束referential constraints
修改约束从DIASABLE NOVALIDATE 到DISABLE VALIDATE
重建IOT
创建视图或者修改ALTER视图时可能需要申请该队列锁
分析表统计信息或validate structure时

8. 一些PDML并行DML操作

所有可能调用kkdllk()函数的操作

有用 0

回答交流

提交

问题信息

请登录之后查看

邀请回答

暂无人订阅该标签，敬请期待~~

墨值悬赏