What are Deadlocks?
Deadlocks occur when each of two transactions holds the lock on a database object the other transaction needs next.
Here is an example:
Transaction 1 locks some rows in table 1, and transaction 2 locks some rows in table 2. The next step of transaction 1 is to lock rows in table 2, and transaction 2 needs to lock rows in table 1.
We just described a deadlock situation. Without deadlock handling, both transactions would wait forever, or until they are aborted. On Teradata, deadlocks can happen on one AMP (local deadlock) or across different AMPs (global deadlock), and for various reasons.
Luckily, Teradata uses a queuing strategy, which is serializing locking requests to avoid deadlocks.
There was a change in the naming convention for this locking strategy in Teradata 15.10.
Until Teradata 14.10, this locking strategy was called “pseudo table locks” independently if the lock was on row hash level (for dictionary tables) or table level.
Since Teradata 15.10, table level and partition locking (a new feature) are called “proxy locking”, and rows hash locking on dictionary tables is called “pseudo table locking.”
In my opinion, the strategy is still the same (but what’s new since Teradata 15.10 is partition locking). Just the wording changed.
The “Proxy” or “Pseudo Table” Lock Strategy
Without a proper locking strategy, and two transactions asking for a write lock on the same table, the first transaction could get the lock for the table on some of the AMPs, and the second query takes the locks on another set of AMPs.
None of the transactions would be able to finish its task. Both requests would wait forever (for completeness: a NOWAIT lock modifier is available, which aborts the request if the lock can’t be obtained immediately).
A typical global deadlock situation (involving several AMPs).
The “proxy lock” or “pseudo table” strategy avoids such deadlocks by serializing the requests.
For sure many of you all have seen the term “to prevent global deadlock” when explaining a query (the explain plan is from a Teradata 15.10 system):
Explain UPDATE CUSTOMER SET AGE = 40;
Teradata Deadlock Prevention in Action
Here is an example which shows the deadlock handling strategy in action:
Two update statements execute simultaneously (‘Update 1’ and ‘Update 2’), and they want to update the same table. Each update intends to have the write lock:
UPDATE Customer SET Age=40 ;
UPDATE Customer SET Gender = ‘M’;
“Pseudo table” or “Proxy” locking ensures that each request has to get the pseudo lock on a reserved rowhash before obtaining the required lock.
For each table, “proxy” or “pseudo table” locking defines an AMP, the gatekeeper to the locks. The gatekeeper AMP for each table is found by hashing its “table id” value.
Hashing happens in the same way as primary index hashing. By hashing the “table id”, the gatekeeper AMP is found.
As the hashing algorithm is stable, the ROWHASH for a specific “table id” always is the same. As long as the system configuration is not changed, there is a 1:1 relation between table and gatekeeper AMP.
In our example, two update statements want a write lock on the same table.
Assuming that “update 1” is slightly faster than “update 2”, it will get the “proxy” or “pseudo table” lock on AMP 2, which is the gatekeeper for the table “Customer.” “Update 2” has to wait in the “gatekeeper” queue of AMP 2 and will be next as soon as “update 2” finished and released the write lock.
Teradata Deadlocks and BTEQ
BTEQ can repeat the step that ended in a deadlock; the error code 2631 must occur and “.SET RETRY ON” must be active. BTEQ will then repeat the request (but not the whole transaction). This functionality is not available in other client applications and must be programmed.
Tips for reducing Deadlocks in Teradata
- Use LOCKING FOR ACCESS whenever dirty reads are allowed.
- Avoid BT/ET but use multistatement requests instead to avoid table level deadlocks.
- For unique index access (UPI, USI) Use LOCKING ROW FOR WRITE or EXCLUSIVE before executing a transaction if deadlocks situations should be minimized.
Not all kinds of deadlocks can be avoided with the above-described strategy. It only works if both participating transactions work with table locks. If one or both requests use row-hash locking, deadlocks still can happen.
Furthermore, deadlock detection takes time—Teradata checks by default for global deadlocks every four minutes. Local deadlocks are searched every 30 seconds.
Usually, these times are ok, but you might want to decrease global deadlock detection intervals in some particular cases.
One of my clients uses many join indexes, causing many global deadlocks (the join indexes are needed for primary index access, i.e., row hash locks are used).
The join indexes are utilized by tactical workload requests to keep execution times below a couple of seconds.
Having global deadlock detection set to four minutes is counterproductive in this case.