Deadlock On Commit Materialized View

(Doc ID 1312379.1)

Last updated on FEBRUARY 02, 2017

Applies to:

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

Symptoms

A refresh is occurring for a Materialized View with on commit upon ten base tables. Concurrently,
an insert is happening from two processes. The processes hang in a deadlock with the following events:

enq: MS - contention
enq: JI - contention


For example:


SID
 LMODE
 REQUEST
 Type
153 6 0 Blocking
142 0 6 Blocked
142 6 0 Blocking
153 0 6 Blocked


The problem is occurring while an insert and a select are performing at the
same time using a Materialized View.

For example:

a) While select:
Elapsed times include waiting on following events:


Event waited on Times Waited Max. Wait Total Waited
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
enq: JI - contention 397 3.07 369.19
db file sequential read 6 0.00 0.00


b) While insert:
Elapsed times include waiting on following events:


Event waited on
 Times Waited
 Max. Wait
 Total Waited
enq: JI - contention 1 0.52 0.52
enq: JI - contention 397 2.93 969.19

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