16.10 - Reducing Deadlocks - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)
  • 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.