Locks on Apply

If there are database locks on the Apply database this will slow down the replication.

The locks will be visible in the Apply log.

The steps to identifying the locks are and to resolve them are:

1. Run the List transactions command in the Dbvisit Replicate command console several times and see if the same transaction keeps showing up. 

2. Run the following SQL on the target to see which SQL it is currently waiting on:

select
    sid,
    sql_text 
from
    v$session s,
    v$sql q 
where
    sid in
    (select
       sid
    from
       v$session
   where
       state in ('WAITING')
   and
       wait_class != 'Idle'
   and 
       event='enq: TX - row lock contention'
   and 
      (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));

3. Find our which statement is the blocking session by running the following SQL:

select
blocking_session,
sid, serial#,
wait_class,
seconds_in_wait 
from
v$session 
where
blocking_session is not NULL
order by
blocking_session;

4. The transaction causing the lock can sometimes be committed or rolled back to ensure the continuation of the replication. This can be with the following commands:

When the Transaction ID of the statement is found, then the record can be committed with:

dbvrep> apply commit transaction xxx

This will cause the statement waiting for the lock to proceed as the locking statement is now committed on the target database.

The transaction can also be rolled back with command:

dbvrep> apply rollback transaction xxx