Deadlock on TeraSync table (2631)

nazconsult · Post by **nazconsult** » Thu Mar 15, 2007 3:45 pm

The OS is actually Linux 2.6.5-7.244-smp.

We are loading (appending) data to a Teradata table via the Enterprise stage and intermittently receive this deadlock on the TeraSync table. Any idea why?

Teradata_Enterprise_1,4: TeraSync:DB Call Failure(success check) Info = 0, Code = 2631, Message = Transaction ABORTed due to Deadlock.error code = 1
TeraSync:DB Call Failure(success check) Info = 0, Code = 2631, Message = Transaction ABORTed due to Deadlock. error code = 1
DBCAREA msg_txt: Completed successfully. CLI Error Message: Completed successfully.
TeraSync:ExecuteImmediate failed for 'update DS_LOAD.terasync set ctlstate = 4, eventCount = eventCount + 1, recordcount = recordcount + 55556 , bytecount = bytecount + 7055612, rejectcount = rejectcount + 0 where cookie = 32830596'. Error Code = 2631error code = 1

trojancjs · Post by **trojancjs** » Mon Apr 30, 2007 9:03 pm

Hello,

We just recently had some issues with Teradata returning 2631 messages. In our situation, the messages were not actually related to lock contention, but to other system level problems that Teradata's support staff had to intervene with.

In this case though, there is a very real possibility that true deadlock is happening. If you EXPLAIN the statement below, are row-hash level locks being used (is "cookie" the primary index of the TeraSync table)? Also, consider enabling and looking at Teradata's DBQL SQL log to see if any other statements are being executed in the same transaction as the UPDATE. I doubt that DataStage uses ACCESS locks when reading from the TeraSync table ...

- Chris

amr_halawa · Post by **amr_halawa** » Tue Nov 20, 2007 5:59 am

I am also facing this problem, i search the forum but am not able to find any solution for it, the error message is given below, When i run a job it sometimes run successully while the next time it fails, I do have appropreiate rights for creating the terasync table. This problem occurs when data is large and i am using Teradata Enterprise Stage as target. Please help me out.

SUBSCRIPTION_CONTRACT_HIST,5: TeraSync:DB Call Failure(success check) Info = 0, Code = 2631, Message = Transaction ABORTed due to Deadlock.error code = 1
TeraSync:DB Call Failure(success check) Info = 0, Code = 2631, Message = Transaction ABORTed due to Deadlock. error code = 1
DBCAREA msg_txt: Completed successfully. CLI Error Message: Completed successfully.
TeraSync:ExecuteImmediate failed for 'update DD_TABLES.terasync set ctlstate = 4, eventCount = eventCount + 1, recordcount = recordcount + 131762 , bytecount = bytecount + 6061052, rejectcount = rejectcount + 0 where cookie = 2049018731'. Error Code = 2631error code = 1
TeraSync:ExecuteImmediate failed for 'update DD_TABLES.terasync set ctlstate = 4, eventCount = eventCount + 1, recordcount = recordcount + 131762 , bytecount = bytecount + 6061052, rejectcount = rejectcount + 0 where cookie = 2049018731'. Error Code = 2631 error code = 1
DBCAREA msg_txt: Completed successfully. CLI Error Message: Completed successfully.
TeraSync: error advancing to state 4.-718,184,464, unable to execute 'update DD_TABLES.terasync set ctlstate = 4, eventCount = eventCount + 1, recordcount = recordcount + 131762 , bytecount = bytecount + 6061052, rejectcount = rejectcount + 0 where cookie = 2049018731'.
Can't signal player done.

rwierdsm · Post by **rwierdsm** » Tue Nov 20, 2007 1:15 pm

What kind of load are you doing, MLoad or TPump? Is there anything in the TeraData utility report file that would shed light?

Rob

amr_halawa · Post by **amr_halawa** » Wed Nov 21, 2007 3:09 am

I am using Teradata Enterprise Stage which do Fastload, and there is no report generated for it. From the log it seems that two
Processes are updating the terasuyn table at the same time, from my point of view each process is writting to terasync table
the number of bytes written by it to the final table but it seems stupid if Datastage is doing this becuase in PX there will be multi
processees running at the same time. I don't know if it is configration problem or not.

JoshGeorge · Post by **JoshGeorge** » Wed Nov 21, 2007 3:54 am

2631 error indicates that this can be a typical deadlock scenario - one transaction has a lock on a resource that another transaction is trying to obtain.

Manual indicates:
1) an excessive number of transactions were queued waiting for locks and the user's request would have blocked on the lock queue or
2) the request conflicted with another request, causing a deadlock or
3) a software problem (for example, a lost message) occurred, causing the request to "time out".

This is probably a temporary situation and the job should be re-submitted. If not, more questions arise, how many Teradata Enterprise Stages you have in the same job, how many loads are initiated at the same time and how many are using the same target table... Consulting your DBA will be a good idea for more clarity.

throbinson · Post by **throbinson** » Wed Nov 21, 2007 6:57 am

Caveat - I don't understand the Terasync table but I do know this error.

I see that the 5th node process in your SUBSCRIPTION_CONTRACT_HIST Teradata Enterprise Stage failed attempting to access the same row in the Terasync table. The problem is not the number of Teradata utility slots being taken. The problem is that a single Teradata Enterprise Stage has 5+ players attempting to update the same row in the DD_TABLES.terasync table. That is, 5+ nearly concurrent Update statements attempting to lock and update the same row. Deadlock ensues, retries happen and the job fails.

How many TOTAL SUBSCRIPTION_CONTRACT_HIST players were actually updating the Terasync table? I am keenly interseted in the answer to this question. It can be obtained from the Director log and seeing which TOTAL SUBSCRIPTION_CONTRACT_HIST, <node> has the highest <node> number. If it equal to the (#total AMPS on the Teradata system/2) - 1 then you must change some defaults.

The default # of nodes can be reduced by defining RequestedSessions and SessionsPerPlayer in the db additonal connections Property in the Teradata Enterprise Stage. If you've already done this then you must reduce them further or accept the occasional failure.[/b]

amr_halawa · Post by **amr_halawa** » Wed Nov 21, 2007 7:44 am

In my job i am using one Teradata Enterprise Stage, which is loading data from a Seqeuncial File, Containing about 250000 records, As the sequencial File is serial so the no of input streams and output streams will not be equal so in this case we need to change partition of the Enterprise stage to Round Robin to do equal Distribution. The SUBSCRIPTION_CONTRACT_HIST is actually the name of the table i am populating and 5 shows the process number, My TD machine as 64 AMPS and the number of processes created by Datastage is 32, 0,1,...31 ID, now about 7000 records are being processed by each process. The purpose of Terasync is that it should keep track of the number of bytes written to the target table and the cookie name for error tables. So I guess When each process write to the table it do an update query on the terasync table to update the bytes written but due to this i am getting this error. I tried to search it but no useful help at the moment. I think it is very stupid of DS that each process is updating the terasync table instead the Cordinator Thread which handles Cummunication with all node should wirte this once, but it also willl be a problems if muliti tables are being pouplated through different job. I hope someone would help me out withthis problem.

amr_halawa · Post by **amr_halawa** » Wed Nov 21, 2007 8:11 am

throbinson, thanks for your reply, i think decreasing the Requested session did the trick, i have change the Session from (# of Amp)/2-1 to about 28 and i run the job 4 times and its working fine, so i guess this was the problem that we need to decrease the sessions. I give SessionPerPlayer=1, RequestedSession=28, and i have 2 node in Configatration file. If i got this problem again i will keep you people posted.

throbinson · Post by **throbinson** » Wed Nov 21, 2007 8:41 am

If you are going to use a 2-node configuration file against a 64 AMP Teradata system then I would recommend these settings;
RequestedSessions= 64
SessionsPerPlayer = 32
This will mean 2 TES players to sync with the number of players for your other EE stages.
The number of processes created by the Teradata Enterprise Stage is actually independent of the Configuration file. It is dependent on the RequestedSessions and SessionsPerPlayer settings. Therefore I would suggest keeping them in sync with the configuration File used at runtime via the variable settings mentioned above.
The end result will be only two Update statements against the Terasync table and not your previous of 64. This will reduce or eliminate the deadlock issue.

rwierdsm · Post by **rwierdsm** » Fri Nov 23, 2007 10:59 am

Have you talked to IBM support? I have a dim memory of getting a patch for this issue, however, that was in Windows. Couldn't find the emails from the day.

Rob