Re: Diagnosing Transaction Enqueue (TX) Locking Issue

Tim Callaghan <tcallahan_at_crunchtime.com> wrote in message news:<06k1mv4pj39g8992j15k36q10tfpg6h5hn_at_4ax.com>...
> We have an inhouse replication process which is causing us headaches
> when we try to run more than one copy of it against the same target
> database (we support unlimited remote databases so we may have to
> process in 20 replication files on a Monday morning).
>
> The issue I'm seeing is that computer 1 starts processing in the
> inserts/updates and continues through the process.
>
> Computer 2 starts processing inserts/updates fine as well but at some
> point gets blocked by computer 1.
>
> The lock is a TX lock and I'm trying to understand what could cause
> it.
>
> Any help would be appreciated.
>
> Tim Callaghan

Tim , please do not crosspost.

A TX lock is a transaction enqueue and relates to the rollback segment for the transaction. It is only a problem if it is what the other processes are waiting on. What does the output of $ORACLE_HOME/rdbms/admin/utllockt run during the problem period show?

Based on your discription of the processing it sounds like you may have multiple processes attempting to insert into one target. If this is true recreate the target table with multiple transaction work areas (initrans) to provide preallocated work area for concurrent transactions affecting one block and with multiple free lists to attempt to spread the inserts into different blocks. The initrans parameter also applies to the indexes on the table.

If your processing includes deletes or updates to columns that are referrenced as FK constrainst the lack of an index on the child tables can also adversely affect proformance of the processes.

HTH -- Mark D Powell -- Received on Fri Sep 12 2003 - 08:42:27 CDT