Diagnosing Transaction Enqueue (TX) Locking Issue

[Tim Callaghan]

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

[Mark D Powell]

Re: Diagnosing Transaction Enqueue (TX) Locking Issue

Tim Callaghan <tcallahan@crun chtime.comwrote in message news:<06k1mv4pj 39g8992j15k36q1 0tfpg6h5hn@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 --

[Ryan Gaffuri]

Re: Diagnosing Transaction Enqueue (TX) Locking Issue

Tim Callaghan <tcallahan@crun chtime.comwrote in message news:<06k1mv4pj 39g8992j15k36q1 0tfpg6h5hn@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

are you getting a table level TX lock or a row level TX lock? If its
row level that is because your first computer is not committing or
rollback so the row is still locked.

if its table level... are these child records with a foreign key
constraint? if so index your foreign keys and the problem will go
away.