X LOCK FOR SEARCHED U/D field (XLKUPDLT subsystem parameter)
The XLKUPDLT subsystem parameter specifies the locking method that is to be used when Db2 executes a searched update or delete.
| Acceptable values: | YES, NO, TARGET |
|---|---|
| Default: | NO |
| Update: |  option 32 on panel DSNTIPB |
| DSNZPxxx: | DSN6SPRM XLKUPDLT |
- YES
- Db2 uses an X-lock on qualifying rows or pages. For ISOLATION(CS), the lock is released if the rows or pages are not updated or deleted. For ISOLATION(RS) or ISOLATION(RR), an X-lock is retained until the next commit point. A value of YES is beneficial in a data sharing environment when most or all searched updates and deletes use an index. If YES is specified and searched updates or deletes result in a table space scan, the likelihood of timeouts and deadlocks greatly increases.
- NO
- Db2 uses an S- or U-lock when scanning for qualifying rows. For any qualifying rows or pages, the lock is upgraded to an X-lock before performing the update or delete. For non-qualifying rows or pages the lock is released if ISOLATION(CS) is used. For ISOLATION(RS) or ISOLATION(RR), an S-lock is retained on the rows or pages until the next commit point. Use this option to achieve higher rates of concurrency.
- TARGET
- Db2 combines YES and NO behavior. Db2 uses an X-lock on qualifying rows or pages of the specific table that is targeted by the update or delete statement. Db2 uses an S- or U-lock when scanning for rows or pages of other tables that are referenced by the query (for example, tables that are referenced only in the WHERE clause of the query). For non-qualifying rows or pages the lock is released if ISOLATION(CS) is used. For ISOLATION(RS) or ISOLATION(RR), an S-lock is retained on the rows or pages until the next commit point.