Locks on Apply

If there are database locks on the Apply database, then use the following method to identify the locks are taken place and how to resolve them.

The steps to identifying the locks 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 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. 

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

dbvrep> apply commit transaction xxx

This will then cause the update statement to proceed as the insert statement is now committed on the target database.

The transaction can also be rolled back with command:

dbvrep> apply rollback transaction xxx