Jump to content
btidba

Deadlock row-level locks /Share (S)

Recommended Posts

Hi,

I have  a deadlock which happens time to time while running 2 jobs in parallel.Each of those 2 jobs run a procedure which calls in  this order the following update procedures :

ParseCustomerOrder updates A table

ParseInitialWorkOrders updates B table

ParseCard pour updates C table

ParseEnvelopesCount updates C table

EvaluateCoStatus updates A table

ParsePull_FR updates D table

ParseReturnedMail updates E table

ParseExceptionCards updates A et F tables

UpdateCardCount updates C tables

The trace file alert_det.trc is attached . The deadlock occurs on a session while merging the C table (ParseCard  procedure above ) and in the other session while merging in the A table (proedure ParseExceptionCards above ).

In the trace file we can see in deadlock graph that it's row level lock and also it was waiting in shared mode S.

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00540014-00129b60        69     307     X             50     137           S
TX-005c0016-000ad96b        50     137     X             69     307           S

Also:

Rows waited on:
  Session 307: obj - rowid = 00C603F9 - AAxgP5AAAAAAAAAAAA  (dictionary objn - 12977145, file - 0, block - 0, slot - 0)
  Session 137: obj - rowid = 0108C35A - ABCMNaAAAAAAAAAAAA  (dictionary objn - 17351514, file - 0, block - 0, slot - 0)

I am a novice in the deadlock troubleshooting but I have read the Troubleshooting "ORA-00060 Deadlock Detected" Errors (Doc ID 62365.1) to start understand/analyse the case. when I request:

SELECT owner, object_name, object_type FROM dba_objects WHERE object_id in (12977145,17351514) it gives:

OBJECT_ID OWNER OBJECT_NAME OBJECT_TYPE
12977145 PROD A_IDX$$_1DEE00003 INDEX
17351514 PROD C_STATUSSEQ_ID INDEX SUBPARTITION

C_STATUSSEQ_ID is a bitmap index created on table C using:

CREATE BITMAP INDEX C_STATUSSEQ_ID ON C (STATUSSEQ_ID) LOGGING NOPARALLEL TABLESPACE TBS PCTFREE 20 LOCAL;

CREATE INDEX A_IDX$$_1DEE00003 ON A (SOURCECUSTOMERORDERID) LOGGING TABLESPACE TBS..

Knowing that C table is functionaly a child of A table but this is a dataware house  DB and no foreign key are created.

Could you please help to diagnose more this case and can I fix this dadlock issue ?

 

Many thanks in advance!

 

 

 

 

 

alert_det.trc

Share this post


Link to post
Share on other sites

Hi,

When running parallel updates, a common point of contention are indexes, especially bitmap indexes!  Please read:

http://www.dba-oracle.com/oracle_tips_bitmapped_indexes.htm

Can you drop and rebuild the bitmap index after the updates?

Here are directions for finding the block with a deadlocks:

http://www.dba-oracle.com/t_deadlock.htm

Hope this helps. . .

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×