Avoiding Locking Scenarios

Avoiding Locking Scenarios• Best Practices – Application – Use least restrictive isolation level that maintains the data integrity requirements of the application – Reduce Isolation level of specific statements by using statement level isolation (i.e., WITH clause) – CLOSE cursors WITH RELEASE to free locks prior to end of transaction– Perform updates as close to the end of the transaction as possible, to reduce exclusive lock duration– COMMIT frequently to release locks – Avoid multiple applications accessing the same tables, but acquiring locks in different orders (Access patterns should be similar) – Avoid having multiple processes that access the same...
Nội dung trích xuất từ tài liệu:
Avoiding Locking ScenariosAvoiding Locking Scenarios • Best Practices – Application – Use least restrictive isolation level that maintains the data integrity requirements of the application – Reduce Isolation level of specific statements by using statement level isolation (i.e., WITH clause) – CLOSE cursors WITH RELEASE to free locks prior to end of transaction – Perform updates as close to the end of the transaction as possible, to reduce exclusive lock duration – COMMIT frequently to release locks – Avoid multiple applications accessing the same tables, but acquiring locks in different orders (Access patterns should be similar) – Avoid having multiple processes that access the same table for both reads and writes within the same transaction Avoiding Locking Scenarios• Best Practices – Database – Avoid lock escalations by increasing DB CFG parameters LOCKLIST and/or MAXLOCKS – Avoid lock timeouts: • Adjust the DB CFG parameter LOCKTIMEOUT or use the SET CURRENT LOCK TIMEOUT command – Avoid deadlocks: • Reduce row blocking during index and table scans: – DB2_SKIPINSERTED to skip/ignore uncommitted inserted rows – DB2_SKIPDELETED to skip/ignore uncommitted deleted rows – DB2_EVALUNCOMMITTED to defer locking until row is known to satisfy query. Uncommitted data will be evaluated. Skips deleted rows on table scans.More Useful Registry Variables forLocking • DB2_KEEPTABLELOCK – allows DB2 to maintain the table lock when an uncommitted read or cursor stability isolation level is closed. The table lock is released at the end of the transaction • DB2_MAX_NON_TABLE_LOCKS – defines the maximum number of NON table locks a transaction can have before it releases these locks. Because transactions often access the same table more than once, retaining locks and changing their state to NON can improve performance • DB2LOCK_TO_RB – specifies whether lock timeouts cause the entire transaction to be rolled back, or only the current statement