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

Last updated on FEBRUARY 02, 2017

Applies to:

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

Symptoms

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/DWH01/diag/rdbms/dwh01/DWH01/trace/DWH01_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

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms