Re: Deadlock on Parallel Update

Bosco Ng wrote:
> Hi, I need your input on this, thanks in advance.
>
> 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?
>
>
> Thanks
> Bosco
>
>

You're probably on the right track if you're certain the processes aren't trying to update the same rows. The transaction settings can definitely cause such a deadlock (see Note 115467.1 on Metalink). In fact, I've seen it on our ETL system before when we have parallel loads (as many as 64 processes in our case).

You're also right that you'll have to 'rebuild' the table after the change for it to have any affect on existing blocks. If you are positive that no more than 4 parallel processes will attempt updates then 4 should be enough. Personally, the amount of space for a transaction entry is small enough that I'd set it much higher to avoid having to rebuild again later (especially if your block size is large).

You're also right that the PCT_FREE could help to avoid this issue but that's no guarantee since that space is fair game for data as well. I would suggest you rely on the INITRANS setting and set PCT_FREE just for your expected data growth. I'm not sure why you would have it set to 0 if you're really doing updates though (unless you know they are net zero size change type updates).

Richard Kuhler Received on Thu Aug 14 2003 - 14:16:21 CDT