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.

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.