Re: Deadlock on Parallel Update

Thanks all guys.

I have no idea why they set PCT_FREE = 0 coz I was called in for help because of such deadlock. Personally, I think that's a bad setting and the row chaining because of such will well defeat the purpose of tightly pack the blocks for better read access.

In fact, I believe this is not the only table that will be causing deadlocks, I believe a major review is needed on these settings.

And I'll give it a try on my hypothesis today and hopefully I can get some positive results :-)

Bosco

Dave Hau <davehau_nospam_123_at_nospam_netscape.net> wrote in message news:<3F3BF10A.3020001_at_nospam_netscape.net>...
> Richard Kuhler wrote:
> > 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).
>
> My guess is this is a data warehouse and these updates are infrequent
> night-time batch jobs, and they want to optimize read performance to
> access as few blocks as possible, although if PCTFREE is set to 0, I
> would always rebuild the table after each night-time update job because
> it's hard to be absolutely sure that an update involves net zero size
> change to the row, and if the row size is increased just slightly, you
> get row chaining across different blocks which defeats the purpose of
> packing the blocks tight in the first place because now you need to
> access multiple blocks to read a single row.
>
> Don't know if COL_A has an index or not. If so, the deadlock might also
> be due to updating the index, assuming you have the index packed tight
> and INITRANS set to 0 as well.
>
> Another way you can solve this problem is to either range-partition the
> table based on the unique key SID, or use an index-organized table, so
> that you can allocate your 4 processes to different ranges of the unique
> key and know that these rows will not be stored together as in a
> heap-organized table.
>
> Cheers,
> Dave
>
>
>
>
>
> >
> > Richard Kuhler
> >
Received on Thu Aug 14 2003 - 20:41:40 CDT