|
|
|
You can use the following strategies to avoid deadlock:
You can also use storage structures to avoid deadlock. For more information, see Storage Structures.
|
|
|
Row locking can be used to avoid deadlock in situations where a nightly modification of the shadow and archive tables is not possible. Row locking uses more resources than hash tables.
Row locking avoids lock contention on the shadow and archive tables because no users touch the same row at the same time. All operations on the base replicated table (insert, update, or delete) cause an insert to be made into the shadow and, sometimes, into the archive table. Because all these inserts add new rows and the rows added by one user do not have the same transaction_id value (the primary part of the key) as the rows added by other users, the locks never contend.
This is a very different situation than for page locking, where a continually increasing transaction_id value causes all inserts to go to the same far right B-tree leaf page. The locking characteristics of the shadow and archive tables (and inserts to the input queue table) follow the locking characteristics of the update of the base replicated table if possible.
The primary requirement for row locking is that the page size of the shadow and archive tables as well as the base table must be at least 4 KB.
Note: The help table statement in the SQL terminal monitor shows the page size of the relevant tables.
The base replicated table must also have a 4 KB page size, and row locking must be enabled for it. For more information, see the SET LOCKMODE statement. If row locking is used to update the base replicated table, the DBMS attempts to open the shadow and archive tables and the input queue table using row locking. If a page size smaller than 4 KB makes this impossible, page locking is used.
|
|
|
The default locking characteristics for the shadow, archive, and input queue tables follow those of the original update. You can override this default for every table in every database for a specific DBMS Server or for all DBMS servers.
To override the default, add the following resources to the config.dat file in the II_CONFIG directory:
ii.nodename.dbms.server_name.rep_sa_lockmode
ii.nodename.dbms.server_name.rep_iq_lockmode
ii.nodename.dbms.server_name.rep_dq_lockmode
where:
Is the name of the node and server_name is the server name (use an asterisk [*] for all servers).
Determines the lock level used for the Ingres Replicator shadow and archive tables when a user update is performed.
Determines the lock level used for the input queue table when a user update is performed and when the distribution threads move records from the input queue.
Determines the lock level used by the Ingres Replicator distribution threads when moving records from the input queue to the distribution queue.
Possible values for these parameters are:
Always use row locking.
Always use page locking.
This is the default for the rep_dq_lockmode parameter.
Always use table locking.
Locking strategy is determined by the user update (not valid for rep_dq_lockmode).
This is the default for the rep_sa_lockmode and rep_iq_lockmode parameters if they are not set, which means that the locking strategy is taken from the user update.
All these locking strategies describe the initial lock level at the time the update is started. If the maxlocks value for any table is exceeded, the locking escalates in the normal manner to table locking. For more information, see Maxlocks Values. You can override the initial lock level used on the input queue table for a specified database session using set lockmode on dd_input_queue where level = level.
To summarize, the level set by set lockmode on the tablename statement overrides all other settings for that table. Otherwise, the level is determined by the config.dat parameters described above. If these parameters are not set, the level is determined by the level used when updating the base user table. Hence, the user is given the ability to determine the exact level used for each update to every table involved in replication.
Note: The set lockmode statement does not affect the updates performed by the distribution threads because they are not performed in the context of a user.
|
|
|
For user updates to the shadow, archive, and input queue tables, the value of maxlocks (in the set lockmode statement) is taken from the value inherited from the system_maxlocks value or set with the set lockmode statement. For shadow and archive tables, this value cannot be overridden.
The input queue table is updated when any update to any base replicated table occurs. Therefore, it is important to avoid lock escalation on this table. For this table, the set lockmode statement works for maxlocks and lets you set the system_maxlocks for the input queue separately. If set lockmode is not used on the input queue, the maxlocks value is inherited from the initial user update.
For distribution threads (that cannot inherit user session attributes), the maxlocks value can be set using the following config parameter:
ii.nodename.dbms.server_name.rep_dt_maxlocks
This parameter must be set to an integer value indicating the maximum number of locks taken before escalation. The distribution threads use this value of maxlocks for all table opens, but only if it exceeds 50. If this value is not set, the threads use the generic system maxlocks value as set in the config.dat parameter:
ii.nodename.dbms.server_name.system_maxlocks
This value is used only if it exceeds 100. If neither parameter is set, the default value is 100 locks.
|
|
|
If the distribution threads receive a deadlock error, they roll back the transaction and try again immediately. If the deadlock problem persists after three tries, a warning is logged to the error log, and the distribution transaction is requeued to the internal pending list for later distribution. This occurs a few seconds or a few minutes later but is always in the current wakeup cycle of one of the distribution threads.