- Except with CREATE INDEX, use LOCKING FOR ACCESS if dirty reads are acceptable.
- Consider BTEQ handling of transaction processing. After transaction rollback, BTEQ continues the transaction from the point of failure, not at the beginning of the transaction.
- Set the DeadLockTimeout field via the DBS Control utility to 30 seconds if you have a mix of DSS and PI updates on fallback tables.
- Be sure to use RELEASE LOCKS on Archive/Recovery jobs.
- Use the LOCKING ROW [FOR] WRITE/EXCLUSIVE phrase preceding a transaction. This phrase does not override any lock already being held on the target table. LOCKING ROW is appropriate only for single table selects that are based on a PI or SI constraint. For example:
LOCKING ROW FOR WRITE SELECT y FROM tableA WHERE pi =1; UPDATE tableA SET y=0 WHERE pi =1;
- In macros, use multistatement requests instead of Begin Transactions (BT)/End Transactions (ET) to minimize table-level deadlocking. For example:
LOCKING ROW FOR WRITE SELECT y FROM tableA WHERE pi =1 ; UPDATE tableA SET y=0 WHERE pi =1 ;
This causes all the necessary locks to be applied at the start, which avoids the potential for a deadlock. Use the EXPLAIN modifier to check out the processing sequence.
- Be aware that when you are updating tables where several rows in the base table share a row in the join index subtable (such as in most aggregate join index cases), there can be considerable collision on the same row.