My Oracle Support Banner

Deadlock between PS and BF Locks During Select Query Running in Parallel (Doc ID 1073728.1)

Last updated on MARCH 28, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 11.1]
Information in this document applies to any platform.


Get deadlock when running select statement in parallel and there are no other locks on tables. The select runs in parallel because one of the tables have PARALLEL DEGREE 2. When running same statement with NOPARALLEL hint everything is fine.

Errors in file /opt/oracle/admin/sid/diag/rdbms/sid/sid/trace/sid_p001_18607.trc:
ORA-00060: Deadlock beim Warten auf Ressource festgestellt
ORA-10387: parallel query server interrupt (normal)

The PX slave is waiting for BF enq while holding PS. Both are holding resources in shared mode while requesting exclusive.And from trace file we also see that bloom filter is being used in the plan.

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
BF-3a0101cc-00000000 81 460 S 137 426 S X
PS-00000001-00000001 137 426 S 81 460 S X

Call Stack:

ksedst1 <- ksedst <- dbkedDefDump <- ksqdldnl <- ksqcmi
<- ksqcnv <- ksqcov <- kxfpqsrls <- kxfpqrsod <- kssdel
<- kxfpdelqrefs <- kxfpqsod_qc_sod <- 224 <- kxfpqsod <- kxfrdel
<- kxsffir <- kxsFreeWorkArea <- kxsFreeExecutionHea <- kksumc <- opifch2
<- kpoal8 <- opiodr <- ttcpip <- opitsk <- opiino
<- opiodr <- opidrv <- sou2o <- main <- start


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.