Deadlock on Parallel Update

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 Received on Thu Aug 14 2003 - 09:43:39 CDT