DML / DDL operations and their Locking mechanism in Teradata - How to avoid dead locks in Teradata

DML/DDL operations and the Locks acquired

Deadlocks and Deadlock Resolution

A deadlock occurs when transaction 1 places a lock on resource A, and then needs to lock resource B. But resource B has already been locked by transaction 2, which in turn needs to place a lock on resource A. This state of affairs is called a deadlock.

Both the Transaction need the resource of the other for completion

 To resolve a deadlock, Teradata Database aborts the younger transaction, the one that has held the resource the least amount of time, and performs a rollback.

In the diagram below, the Transaction T2 will have to release its lock as its the younger transaction.

Deadlocks in Teradata and their resolution

If you used BTEQ to submit the transaction, the database reports the deadlock abort to BTEQ. BTEQ resubmits only the request that caused the error (the default behavior), not the complete transaction. Because this can result in partially committed transactions, you must take care when writing a BTEQ script to ensure that the transaction is one request. For example, a statement in BTEQ ends with a semicolon (;) as the last non‑blank character in the line. Thus, BTEQ sees the following example as two requests:

    sel * from x;

    sel * from y;

However, if you write these same statements in the following way, BTEQ sees them as only one request:

   sel * from x

   ; sel * from y;

If you want to know more about our online/classroom classes, kindly send us your details and we will call you back.