There an interesting example of a deadlock on the OTN database forum:
DEADLOCK DETECTED ( ORA-00060 ) [Transaction Deadlock] Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits PS-00000001-00000011 92 423 S 33 128 S X BF-2ed08c01-00000000 33 128 S 92 423 S X
One of the responses to the post points out that Oracle error ORA-00060 is an application error and the OP needs to fix his code – and that’s usually a valid comment, especially if the deadlock involves only TX enqueues, TM enquees or a mixture of both; but this deadlock is between a BF and a PS enqueue.
PS enqueue resources represent parallel execution slaves
BF enqueue resources represent Bloom Filters
The OP tells us that he’s getting this deadlock when running a parallel query (i.e. select, not insert, update, or delete). Parallel query is exactly the environment where parallel execution slaves get allocated and freed, and where parallel execution slaves and the query co-ordinator write and read to Bloom Filters.
You may want to read the entire thread to see where we got to – but the end user found an initial workaround to the problem by setting the hidden parameter “_bloom_pruning_enabled” to false. (The query involved a partitioned table, and partition pruning is one of the places where Bloom filtering can be used.)
Identified by Herald ten Dam as bug “12637294 – Deadlock of PS and BF locks during parallel query operations”. Patched in 220.127.116.11.