Disabling update locks for searched UPDATE and DELETE

You can use the XLKUPDLT subsystem parameter to disable update locks on searched UPDATE and DELETE statements.

About this task

Begin program-specific programming interface information.

By specifying the use of X-locks for searched UPDATE and DELETE operations, you can save the cost of additional requests to upgrade locks that are used for searching when rows are updated or deleted. The cost savings of this approach are greatest in data sharing environments because the main cost of the request to upgrade the locks is from recording the request in the coupling facility.

Procedure

To disable update locks on searched UPDATE and DELETE statements:

Set the value of the XLKUPDLT subsystem parameter when most or all searched UPDATE and DELETE statements use an index or can be evaluated by stage-1 processing.
  • When you specify YES, Db2 uses an X-ock on rows or pages that qualify during stage 1 processing. With CS isolation, the lock is released if the row or page is not updated or deleted because it is rejected by stage 2 processing. With RR isolation or RS isolation, Db2 acquires an X-lock on all rows that fall within the range of the selection expression. Thus, a lock upgrade request is not needed for qualifying rows, though the lock duration is changed from manual to commit. The lock duration change is not as costly as a lock upgrade.
  • When you specify TARGET, Db2 treats the rows or pages of the specific table targeted by the update or delete as if the value of XLKUPDLT was YES. It treats rows or pages of other tables referenced by the query, such as those in referenced only in the WHERE clause, as if XLKUPDLT was set to NO. By specifying this blended processing, you can prevent time outs caused by strong lock acquisition of the read-only non-target objects referenced in the update or delete statement.
  • When you specify NO, Db2 might use lock avoidance when scanning for qualifying rows. When a qualifying row is found, an S lock or a U lock is acquired on the row. The lock on any qualifying row or page is then upgraded to an X-lock before performing the update or delete. For stage-1 non-qualifying rows or pages, the lock is released if CS or RS isolation is used. For RR isolation, an S-lock is retained on the row or page until the next commit point. This option is best for achieving the highest rates of concurrency.End program-specific programming interface information.