Geek asks and answers

Getting deadlock while doing UPDATE at the same time

I download the application and get a deadlock error. The script includes and updates the database at the same time by 10 different users. I looked online and still could not find a way to solve it. Here comes my sample deadlock code.

Can someone give me some tips to resolve the impasse? 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 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 JDBC Templates
    this.getSimpleJdbcTemplate().update(sbSql.toString());
}
+3
source share
1 answer

If deadlocks occur, this is not from the code you provided if:

  • You have several instances of this test program running at the same time.
  • The above code ran asynchronously - but that doesn't seem to be the case.
  • When performing these tests, there is another activity in the database.

.

, , SQL Trace , .

-, , SQL- ? @ , , .

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

sbSql = string.Format(sbSql, id);

, WITH(ROWLOCK). 99.9% SQL- , .

0

More articles:


All Articles