Reduce Deadlocks | Teradata Vantage - 17.00 - 17.05 - Reducing Deadlocks - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
  • 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.