Reduce Deadlocks | Teradata Vantage - Reducing Deadlocks - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2024-10-04
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantageā„¢
  • 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.