Re: Deadlock on Parallel Update

"Bosco Ng" <boscong_at_leccotech.com> wrote in message news:<3f3b9e8b$1_at_shknews01>...
> I got a night time batch process that needs to update on some of the rows of
> some million-row scale tables.
>
> Ok, due to time constraint, we now plan to split the process into 4
> processes and each of the 4 processes will update a table TABLE_A based on
> the SID, which is the unique key.
>
> So basically:
>
> Process 1: UPDATE TABLE_A SET COL_A = :b1 WHERE SID = :b2
> Process 2: UPDATE TABLE_A SET COL_A = :b1 WHERE SID = :b2
> Process 3: UPDATE TABLE_A SET COL_A = :b1 WHERE SID = :b2
> Process 4: UPDATE TABLE_A SET COL_A = :b1 WHERE SID = :b2
>
> And I am pretty sure that the SID allocated to each processes are unique and
> will not overlap, so they are all updating different rows.
>
> But we are experiencing deadlocks here.
>
> After investigation, my hypothesis is that on the ITL list of the data
> block.
>
> I happened to know that TABLE_A is set up with INITRANS = 1 and PCTFREE = 0.
> So there is no room for the the block hander to expend the ITL when needed,
> so if it happened that any of the 2 processes try to update a data block
> together (because those 2 SID happened to reside in the same block) there
> will be a dead lock.
>
> Am my hypothesis RIGHT?
>
> If I am right, should I rebuild the table using say INITTRANS = 4, would
> that be enough? And should I change the PCT_FREE setting?

Hi, Bosco,

Others have good input. I just want to add that in your parallel DML, your initrans has to be at least 4. I.e., if you set pctfree to a value such that ITLs could automatically grow to 4, leaving initrans at 1 may result in ORA-12829 (Deadlock - itls occupied by siblings at block %s of file %s). This is one of two cases you can't rely on a non-zero pctfree to increase ITLs (see Steve Adams' book p.49).

Indexes need a little higher ITLs. Try setting initrans to at least 5 if your table initrans is 4.

Unless your Oracle version is pre-8i, you can alter table move to rebuild the table with new physical attributes. "move" doesn't mean you have to move; it really means rebuild. Existing blocks are also rebuilt. Remember to rebuild the indexes after that.

Yong Huang Received on Fri Aug 15 2003 - 09:40:00 CDT