Teradata Enterprise Stage Deadlock error on Terasync table

ananth_kv · Post by **ananth_kv** » Tue Jul 17, 2007 12:06 pm

Hi

we are getting below error when we try to load data from dataset to Teradata table using sessionperplayer=1 on 54 AMP configured teradata server.

Any thoughts why deadlock message is coming? Target table and Terasync tables are avaiable and no lock on this tables.

Any thoughts are welcome.

Tera_DWH_SBMN_FLOW_CNT,3: 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 flowdmload.terasync set ctlstate = 4, eventCount = eventCount + 1, recordcount = recordcount + 54160 , bytecount = bytecount + 13377520, rejectcount = rejectcount + 0 where cookie = 65031884'. Error Code = 2631error code = 1
TeraSync:ExecuteImmediate failed for 'update flowdmload.terasync set ctlstate = 4, eventCount = eventCount + 1, recordcount = recordcount + 54160 , bytecount = bytecount + 13377520, rejectcount = rejectcount + 0 where cookie = 65031884'. Error Code = 2631 error code = 1
DBCAREA msg_txt: Completed successfully. CLI Error Message: Completed successfully.
TeraSync: error advancing to state 4.-243,662,836, unable to execute 'update flowdmload.terasync set ctlstate = 4, eventCount = eventCount + 1, recordcount = recordcount + 54160 , bytecount = bytecount + 13377520, rejectcount = rejectcount + 0 where cookie = 65031884'.
Can't signal player done.

Regards
Ananth

throbinson · Post by **throbinson** » Tue Jul 17, 2007 1:12 pm

What is your transaction size? Is it possible you are attempting an update of a row twice before a commit?

ananth_kv · Post by **ananth_kv** » Tue Jul 17, 2007 1:29 pm

throbinson wrote:What is your transaction size? Is it possible you are attempting an update of a row twice before a commit?

Hi
Thanks for the note.
I am getting this error when Entprise stage is trying to update Terasync table as part of loading to intermediate work table. Job is not even going till target table.

Thanks
venkata

ds · Post by ds » Fri May 30, 2008 2:56 am

ananth_kv wrote:
throbinson wrote:What is your transaction size? Is it possible you are attempting an update of a row twice before a commit?
Hi
Thanks for the note.
I am getting this error when Entprise stage is trying to update Terasync table as part of loading to intermediate work table. Job is not even going till target table.

Thanks
venkata

How did you get around this issue resolved I am facing the same problem

throbinson · Post by **throbinson** » Fri May 30, 2008 5:00 am

There is an E-case and patch from IBM. I apologize for not having the specifics on it.

keshav0307 · Post by **keshav0307** » Fri May 30, 2008 8:00 am

how many session are used,
if TD_REQUESTED _SESSIONS is not defined then all 54 AMPs will be used.
how any players are used?
TD_REQUESTED _SESSIONS=sessionsperplayes*number of players

IF it does not work then try defining a $APT_TERASYNC_DB value other then the target db

getanjalip · Post by **getanjalip** » Fri Jul 18, 2008 8:19 am

Hi Ananth,

Did you check the execution mode on the target tables. I believe it would be parallel. Make it to sequential. I think this should fix the problem.

I faced a similar issue and it got resolved when I made it sequential.

Hope this helps.