Return
-
Title
How to configure the Repair command so that it acquires row level locks instead of table level locks -
Description
How to configure the Repair command so that it acquires row level locks instead of table level locks.
-
Cause
General information. -
Resolution
The parameter SP_DEQ_ROW_LOCK_THRESHOLD is for the out of sync row count during repair phase of the Repair command which governs whether the row level or table level locks will come into play when repairing the target table. The parameter has no bearing when running a compare which will always lock the source and target tables, albeit very briefly, to get a read consistent snapshot of these tables when the compare is started. So, regardless of the setting of this parameter, the compare command always locks the tables, if is successful in acquiring the logs and failure to acquire it after repeated attempts results in compare timing out.When it comes to repair, there are two phases in the repair process. During the compare phase, the out of sync are determined and during the repair phase the complete target table or the specific target rows (out of sync rows) are locked. The nature of lock is governed by the parameter SP_DEQ_ROW_LOCK_THRESHOLD. The logic behind the creation of this parameter is, if there are few out of sync rows, the Repair process is better off locking the out of sync rows on target one row at a time during the repair phase. This avoids disruption for any reporting on the target. However, if there are numerous out of sync rows found by repair based on the “where” clause used, it may involve more overhead if row level locking is done since there is frequent lock management. One target row is locked, the out of sync is fixed and the repair process then locks the next out of sync row and so on. If fewer out of sync rows need to be synchronized, if may be advantageous to use a row level lock instead of the complete table lock. The optimum value of the parameter can only be determined by a trial and error.Another nice thing about using row level locks instead of table level locks is that, if someone locks a row in target table, it is not possible to lock the complete target table for repair and the parameter comes handy as it can permit repair to lock another row.Here is the correct description of the parameter:SP_DEQ_ROW_LOCK_THRESHOLDRepair utilizes this threshold to determine if, when utilizing the “where”, “sourcewhere” and/or “targetwhere” option, the row count is sufficiently low enough to merit use of row level locking instead of table level locking. If the percentage of qualified rows is less than or equal to this parameter valueRepair will use row level locking.Default: 1000 rowsRange of valid values: 0 to 50000 rowsTakes effect: Immediately available for the next comparison -
Additional Information
The Shareplex Administrator Guide up to Shareplex 8.6.2 wrongly describes the parameter as below and it will be corrected in future versions: SP_DEQ_ROW_LOCK_THRESHOLD Compare/Repair utilizes this threshold to determine if, when utilizing the “where”, “sourcewhere” and/or “targetwhere” option, the row count is sufficiently low enough to merit use of row level locking instead of table level locking. If the percentage of qualified rows is less than or equal to this parameter value. Compare/Repair will use row level locking. Default: 10% Range of valid values: 0 to 100% Takes effect: Immediately available for the next comparison