3. Planning Your Replication Scheme : Replication Scheme Design Issues : Storage Structures
 
Share this page                  
Storage Structures
In Ingres Replicator, deadlock can occur when tables involved in a query are replicated. When you update the base table, the DBMS must insert a record in the shadow table, possibly the archive table, and the input queue table within the same transaction. The additional inserts can cause deadlock.
The following example shows how deadlock can occur with just two sessions:
1. User A inserts a record into page 10 of the base table; this locks page 10 of the base table, page 5 of the shadow table, and page 8 of the input queue table to maintain the necessary support tables. The insert is completed and the user holds the transaction—and therefore the locks—open.
2. User B inserts a record into page 11 of the base table; this locks page 11 of the base table and also requires a lock on page 5 of the shadow table to insert the shadow record. The lock on page 5 of the shadow table cannot be obtained, so the session waits for it.
3. Meanwhile, user A tries to insert a record into page 11 of the base table. This insert waits for the lock, which user B holds on the base table. Hence, deadlock. User A needs the lock on page 11 of the base table that user B holds, and user B needs the lock on page 5 of the shadow table that user A holds.
The first part of the primary key on both the shadow table and the input queue table is the transaction_id column. This column contains the low part of the Ingres internal 8-byte transaction ID. This transaction ID is incremented by 1 each time a new transaction—which can be user or system internal—is started in the Ingres installation. This means that a nearly monotonically increasing primary key value is generated for each replicated update.
The default structure for the shadow and archive tables is B-tree. Because the primary part of the key is always increasing, every insert to the support tables causes a ladder lock down the right side of the B-tree index, resulting in a lock on the far right leaf page to insert the new index value. This structure therefore makes it likely that all users want to lock the same pages at the same time.
The column ordering on which Ingres Replicator support tables is indexed cannot be changed because access using these keys is built into the internals of the DBMS (much like the Ingres standard system catalogs, of which the Ingres Replicator support tables are just the visible extension). This makes the alteration or re-ordering of key columns impossible.
To avoid deadlock, the solution is to make sure the sessions do not try to lock the same pages in the same tables at the same time. To achieve this, you need some understanding of the keying and table structures of the Ingres Replicator support tables. For detailed information and examples of how to use locking parameters to avoid deadlock, see Strategies for Avoiding Deadlock. The other means of avoiding deadlock is to use storage structures. For more information, see Using Hash Structures to Avoid Deadlock.