Oracle Scratchpad

November 5, 2013

Deadlock

Filed under: Oracle,Parallel Execution,Partitioning — Jonathan Lewis @ 6:14 am BST Nov 5,2013

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.)

Update

Identified by Herald ten Dam as bug “12637294 – Deadlock of PS and BF locks during parallel query operations”. Patched in 11.2.0.4.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,529 other followers