Deadlock while inserting and updating

ajithaselvan · Post by **ajithaselvan** » Thu May 12, 2011 3:48 am

Hi,

I'm trying to insert and update into SQL server table through ODBC stage.
I'm getting the below error. Pls help me to resolve it.

Error 1:
ODBC_Connector_21,0: Execute failed on statement INSERT INTO
tblBloombergMonthly( ID_CUSIP , MTG_FACTOR , MTG_FACTOR_DT , MTG_EST_ACC_RT,UserID)
VALUES
(? ,? ,? ,?,?)

Error 2:
ODBC_Connector_21,0: ODBC function "SQLExecute" reported: SQLSTATE = 40001: Native Error Code = 1,205: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 70) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (CC_OdbcDBStatement::executeInsert, file CC_OdbcDBStatement.cpp, line 673)

I tried re run the job and tried inserting and updating separately.But still getting the same error.

chulett · Post by **chulett** » Thu May 12, 2011 7:02 am

... and does it work if you run the job on a single node?

ajithaselvan · Post by **ajithaselvan** » Fri May 13, 2011 5:10 am

chulett wrote:... and does it work if you run the job on a single node?

I'm able to run in single node. But i suppose to run in 4 nodes

le thuong · Post by **le thuong** » Fri May 13, 2011 5:36 am

ajithaselvan wrote:
chulett wrote:... and does it work if you run the job on a single node?
I'm able to run in single node. But i suppose to run in 4 nodes

I had the same issue months ago and I solved it by adding index on the column of the Update "where" clause.

chulett · Post by **chulett** » Fri May 13, 2011 6:42 am

You should also look at your partitioning, 'Auto' is not your friend at times like this. Typically one would hash partition over the key(s) used in the target.

greggknight · Post by **greggknight** » Fri May 13, 2011 7:27 am

The problem is that there is no index on the where clause columns, and keep in mind the order of the columns makes a difference. By that I mean that the order of the columns in the index needs to match the order of the columns in the where clause.
What happens is that a job when trying to update will try to place a table lock if there is no index and if another process already has a row locked then it cannot get exclusive access to place the table lock.

With an index it does not use a table lock.
I have had the same problems. I had to review all the developers jobs and make sure the appropriate indexs were there.