Re: transaction enqueue lock

Jon Landenburer wrote:

>We have a process which moves charges from one account to another. To
>do this it updates the charge on the losing account (giving it an end
>date) and then inserts the charge to the gaining account. Many cases
>though the movement of an account may have thousands of charges. In
>order to increase throughput we have attempted to run many of these
>processes at the same time. There should be no row contention because
>each process is working with different accounts and charges. When we
>get above 4 processes running we encounter transaction enqueue locks
>(TX). The holder of the lock is just going on updating and inserting
>where as the blocked process is stopped on the first insert --
>waiting. There is no sharing here the blocked processes will not move
>on until the blockers have completed. Effectively we are being held
>to 4 of these concurrent processes.
>
>looking in v$rollstat there is almost no movement (inserts have no
>before image and the updates are just a date field long-- so no
>rollback contention.
>
>v$session_wait is where I see the enqueue
>
>Not sure where to go from here.
>We are confident there is no row lock, no tables are being held
>exclusively
>
>Any ideas are welcome
>Jon Landenburger

Hello Jon,

try to find the (requested) lock mode of the blocked session(s), from v$lock. If it 4, there is a contention for space (either from the update or the insert statements). If it is 6, there is a 'real' row lock.

BTW: I assume that in this post, MINTRANS should be corrected to INITRANS. Kind Regards,

Herman de Boer
sr. consultant
IT Consultancy Group bv Received on Thu Aug 15 2002 - 01:57:17 CDT