Oracle Replication and Locking

From: Gary Eckhardt <gary_eckhardt_at_realworld.com>
Date: 1997/01/08
Message-ID: <32D416D0.7B42@realworld.com>

Howdy all,

We are currently experencing problems with deadlocks occurring with Oracle replication
when the network connection is down. We are trying to simulate a network down
situation by simply removing the network cable from a hub. When the network is
down, and the system has a lock on a snapshot, all inserts into the table get
deadlocked until the network connection is restored.

Here's the results of the tests we performed here on the machines.

Scenario 1

---

Using server manager, I scanned the lock table until there were no locks showing on the database at all. We then quickly disconnected the network line from the hub, and tried to insert a record into a table. In this case, everything works fine, and the record is inserted, and the proper jobs are queued up to send the update to the snapshot back to the master database. If the network is reconnected,
then everything gets replicated as expected.

Scenario 2

---

Again, using server manager, I scanned the lock table until there I saw that there *WERE* locks on the system. (I'm assuming that these are locks associated with a snapshot update, as they came and went on our fastest snapshot update interval, about 5 seconds). We then unhooked the network line, and the locks did not go away. We then tried to insert
into the table, and the insert locked, as expected, because there were exclusive locks on tables.

Here's a listing of the locks:

Username     Session   Serial   Lock   Resource   Resource   Mode   Mode
                  ID   Number   Type       ID 1       ID 2   Held   Req
FUEL               7   36385    DX           13          0   NULL   NONE
FUEL              29   18637    JQ            0       2030   X      NONE
FUEL              11   19922    JQ            0       2032   X      NONE
FUEL               8   36339    JQ            0       2035   X      NONE
FUEL              10   36355    JQ            0       2031   X      NONE
FUEL              10   36355    DX           10          0   NULL   NONE
FUEL              29   18637    TM          119          0   SS     NONE
FUEL               7   36355    TM         4765          0   X      NONE
FUEL              10   36355    TM         4788          0   X      NONE
FUEL              10   36355    TX       196647     155706   X      NONE
FUEL              29   18637    TX       131107     155892   X      NONE
FUEL               8   36339    TX       262147     155291   X      NONE
FUEL              11   19922    TX       262161     155290   X      NONE
FUEL              10   36355    TM          119          0   SS     NONE
FUEL               8   36339    TM         4816          0   X      NONE
FUEL              11   19922    TM          119          0   SS     NONE
FUEL               7   36385    TM         4767          0   X      NONE
FUEL               7   36385    TX       327746     154971   X      NONE
FUEL               8   36339    TM          119          0   SS     NONE
FUEL              29   18637    TM         4777          0   X      NONE
FUEL              29   18637    TM         4774          0   X      NONE
FUEL               7   36385    TM          119          0   RX     NONE
FUEL              10   35355    TM         4785          0   X      NONE
FUEL               8   36339    TM         4813          0   X      NONE
ROOT              12      34    TM         4785          0   NONE   RX

Object ID's

119           SNAP$
4765          SNAP$_ROAD_CALL
4788          USLOG$_TRANSACT
4816          USLOG$_TANK
4767          USLOG$_ROAD_CALL
4777          USLOG$_MTOTRANS
4774          SNAP$_MTOTRANS
4785          SNAP$_TRANSACT
4813          SNAP$_TANK

The problem table/snapshot is "TRANSACT", which is what the last line of the preceding output shows that it's waiting for a RX lock, but it can't because the line two lines up shows that a process has it in X mode. BTW, the "FUEL" user is where we have our replication defined, and it running. The "ROOT" user is the Oracle user where our background daemons are logged into.

I guess my questions for this behavior would be:

1. In Oracle replication and snapshot updates, do tables get locked into full-table exclusive mode, even for the slightest second?
2. If this is true, is there a way to make the replication NOT use exclusive locks?
3. Is this the way that it's supposed to be? If so, is there anything we can do on the side where the network is down (short of rebooting the system) to release these locks so that we can continue processing on the disconnected system?
4. Does this sound like a plausable explanation for what we're seeing? Or am I missing something?

-- 




---------------------------+----------------------------------------------

Gary Eckhardt              |   "in this day & age...music performed by
Database Consultants, Inc. |    humans...hum!?"  --wilde silas tomkyn
dcigary_at_txdirect.net       |                R,DW,HAHB!
gary_eckhardt_at_realworld.com| R^3 = "Real World.  Real Smart.  Real
Quick."
(210)344-6566              |         http://www.realworld.com/

Received on Wed Jan 08 1997 - 00:00:00 CST