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
请问并行更新或插入会锁全表嘛？什么原理？
{
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
