Getting deadlock when execute UPDATE at same time

Question

I'm load testing the application and get deadlock error. The scenario is inserting and updating database concurrently by 10 different users. I looked up online and still couldn't find the way to solve it. Here attached my sample code involved in deadlock.

Can anyone give me some advice to solve deadlock? Thank you in advance.

SampleController:

onSubmit(userAccount)
{
     sampleBO.testDeadLock(userAccount.getUserAccountId());

}

SampleBO:

public void testInsert(Long id)
{
    sampleDAO.testInsert4(id);      
}

public void testDeadLock(Long id)
{
    testInsert(id);
    sampleDAO.testUpdate4(id);  
}

SampleDAO:

public void testInsert4(Long id)
{
    StringBuffer sbSql = new StringBuffer();
    sbSql.append(" INSERT INTO Test ");
    sbSql.append(" ( ");
    sbSql.append(" id, ");
    sbSql.append(" note ");
    sbSql.append(" ) ");
    sbSql.append(" VALUES ");
    sbSql.append(" (");
    sbSql.append(""+id+",");
    sbSql.append(" 'test' ");
    sbSql.append(" )");

    //Execute SQL using Spring's JDBC Templates
    this.getSimpleJdbcTemplate().update(sbSql.toString());
}

public void testUpdate4(Long id)
{       
    StringBuffer sbSql = new StringBuffer();
    sbSql.append(" UPDATE Test WITH(ROWLOCK) SET ");
    sbSql.append(" note = 'test1111'");
    sbSql.append(" WHERE id="+id);

    //Execute SQL using Spring's JDBC Templates
    this.getSimpleJdbcTemplate().update(sbSql.toString());
}

Also, please post the table definition, including any keys, indexes, constraints and/or triggers, because it's 90% likely that that is where the problem is. — RBarryYoung, Apr 27 '12 at 22:37
am i missing something? It looks like you're calling two executes synchronously, so the testUpdate4 call shouldn't fire before the insert one finishes. — Kir, May 02 '12 at 20:23
Hi, I'm not sure you're experiencing deadlocks, your code is not complex enough to cause one. Can you please describe why you think you're deadlocking, errors you receive and most importantly the SQL flavor you're using? — itayw, May 02 '12 at 20:31

score 0 · Answer 1 · answered Aug 19 '12 at 23:08

If deadlocks are occurring, it is not from the code you've provided unless :

You have multiple instances of this test program running at the same time.
The above code was running asynchronously - but that does not appear to be the case.
There is other activity on the database when you are running these tests.

My money's on the last one.

You could find out why these deadlocks are happening by performing a SQL Trace and identifying exactly what is running and when.

Each to their own, but did you know you could declare your sql query like this? The @ allows the text to go onto multiple lines and also escapes special characters like the backslash.

string sbSql = @"
    INSERT INTO Test (id, note)
    VALUES ({0}, 'test')";

sbSql = string.Format(sbSql, id);

The WITH(ROWLOCK) is probably not needed either. 99.9% of the time SQL server will perform the relevant locking and you should only explicitly force certain locks for complex or extraordinary situations.

Getting deadlock when execute UPDATE at same time

1 Answers1