Reducing Deadlocks
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.