Thank you so much for your inputs Steve. We will work on upgrading to the latest patch.
Just curious, can you advise on how the latest patch of TPT handles deadlocks better than the version we're currently on: 15.00.00.00?
Also, I read in TPT documentation that upon receiving deadlocks, TPT usually retires about 5 times before actually failing the job. But the job log I provided here shows that it retried only once before failing. (Line#147 above, also pasted below)
Is that essentially what the latest patch fixes? It will retry 5 times instead of doing it once in the current version: 15.00.00.00
LOAD_OP: preparing target table
**** 07:40:01 This job will use the following tables:
Target Table: '"STG"."TBL1"'
Error Table 1: '"WRK"."t20160502073945_322274840705"'
Error Table 2: '"WRK"."t20160502073945_322217997408"'
**** 07:40:02 Deadlock error received. Retrying Request.
LOAD_OP: TPT10508: RDBMS error 2631: Transaction ABORTed due to Deadlock.
**** 07:40:10 TPT10508: RDBMS error 2631: Transaction ABORTed due to Deadlock.
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0
I believe in this case, the "Retrying request" message was output in error.
As indicated above, we will handle it better.
There were some areas where requests resulting in the deadlock were not retried.
We haven't done the TPT upgrade yet. We're scheduled to go to Teradata version: 15.10 in next few days. It appears that when we upgraded our Test system to 15.10, the # of deadlocks reduced.
So once we upgrade our production system to 15.10, we're hoping the deadlocks reduce too. If that doesn't happen, then we have a few options:
1. As feinholz suggested, upgrade tpt to latest patch and verify if deadlocks go away.
2. Analysis of locking logger data shows no deadlocks but does show an increased amount of blocking on 2 dbc tables: sometimes it is dbc.accessrights and sometimes it is dbc.eventlog. For dbc.accessrights, we plan to provide direct access rights to the user launching the tpt jobs rather than through roles. For dbc.eventlog, we plan to reduce the overall # of sessions the tpt user initiates and reduce the overall # of sessions being initiated by any other users.
3. Last but not the least, we're going to look at reducing the # of tpt jobs we run in parallel at any point in time. I don't know how much this would help because it seems we get deadlocks even when the overall concurrency is low(once we received deadlocks when only 2 load jobs were running)
HTH. I'll continue to update as this mystery unfolds :)
15.00/15.10 has several improvements around locking for TPT: dbc.accessrights has another PI and there is the partition level locking.
I strongly suggest that you familiarize yourself with the new features of 15.10.
Direct roles and reducing sessions will not help. It is the creation of the error/temp tables of TPT that lock the dbc.accessrights as the rights are being written to the table. Only solution until you use 15.10 is using different users that use TPT as the PI is currently on (UserId ,DatabaseId). Or use another database.
I am also facing deadlock issue when i am trying to load the target table using tdload. The target table is a temporary table created by the script before running tdload. So, there won't be any other processes trying to load the target table but still i am getting the deadlock issue.
$INSERTER:TPT10508:RDBMS error 2631:Transaction ABORTed due to deadlock
$INSERTER:TPT14117 :An error occured while inserting row <259628> into the database by Instance <1>
Any help to fix the issue would be very helpful.
Thanks Johannes. We are in the process of familiarizing ourselves with the new features of 15.10. You're right. The PI changes could help. Hopefully the deadlocks reduce a bit after the upgrade. I'll keep you guys posted.
I see that you are using the Inserter operator.
Are you using multiple instances?
In order to cut down on the deadlocks on the target table, you will need to restrict your Inserter to a single instance and single session.
For anyone else interested, I would like to specify that this issue is now finally resolved in our environment. We upgraded our TPT utilities on our ETL server to latest version. After the upgrade, we are not receiving deadlocks anymore.
As feinholz mentioned earlier, if anyone is having this issue, please consider upgrading to latest version of TPT.
Thanks Feinholz. I owe you a drink!!!