Re: transaction enqueue lock

jon.m.landenburger_at_verizon.com (Jon Landenburer) wrote in message news:<feee7535.0208130543.159977b5_at_posting.google.com>...
> 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

In addition to other people's advice, you may also look at INITRANS on the indexes for the table. Consider setting INITRANS to the same for their base table plus 1, after you increase the setting for the tables. For 8i, the quickest way to increase INITRANS is ALTER TABLE tablename MOVE TABLESPACE tablespacename. You don't need to specify another tablespace; i.e. the end result is not really moving the table to another tablespace. Remember to rebuild indexes after this, since they'll be UNUSABLE unless there's no row in the table.

Yong Huang Received on Thu Aug 15 2002 - 16:28:56 CDT