Re: Deadlock on Parallel Update

From: Dave Hau <davehau_nospam_123_at_nospam_netscape.net>
Date: Thu, 14 Aug 2003 20:29:02 GMT
Message-ID: <3F3BF10A.3020001@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 - 15:29:02 CDT