SQL server table update Deadlock issue :SQLSTATE = 40001

Roopanwita · Post by **Roopanwita** » Sat Feb 16, 2013 6:31 pm

Hi,

I am trying to upsert a SQLserver table,job design is like

SQL Server Enterprise--> CPY --> SQL Server Enterprise

Process is failing with Error :

ODBC_Connector_51,0: ODBC function "SQLParamData" reported: SQLSTATE = 40001: Native Error Code = 1,205: Msg = [Microsoft][SQL Server Native Client 10.0][SQL Server]Transaction (Process ID ##) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (CC_OdbcDBStatement::executeUpdate, file CC_OdbcDBStatement.cpp, line ###)

I tried to replace SQLServer Enterprise with ODBC connector stage , still no hope.

My target table (table I am trying to update) is Indexed and trying to update on Indexed column only.

If I try to write insert/update statement in SQLserver it works fine(in Management Studio)

I seared in DSXchase and foud the below link and tried to change Arrey to readUncommited , still not working

viewtopic.php?t=140177

Is there any other property I need to change?

Thanks in Advance.

chulett · Post by **chulett** » Sun Feb 17, 2013 8:27 am

Are you updating the same table you are selecting from? Does the job work without issue if you run it on one node?

Roopanwita · Post by **Roopanwita** » Mon Feb 18, 2013 12:02 am

Thank you for reply.
I am not updating same table I am selecting from. Only I am doing update Then insert in target and i am using Odbc connector stage for that.Source and targets are different.I tried to run with 1 node, still no luck. Even I tried update<table_name> with nolock in update query. I tried to change array size to 1 even, then job doesn't fail, it keeps on running. Is that issue with table?
Thank you,

chulett · Post by **chulett** » Mon Feb 18, 2013 8:24 am

Changing the array size to 1 could 'solve' the problem but at the cost of it running very slowly, you'd have to wait for it to complete to know. And are you certain you managed to run it on a single node? If you had I don't see how it could have deadlocked with "another process" - assuming you received the same error.

Have your talked to your DBA about this?