deadlock scenario for locking update with sorted list

65 Views Asked by At

assume a tableX having following schema

id,colA,colB,colC,colD
composite index -> colA,colB,colC

there is list of object and each object has field ColB and ColC, the list is then sorted

list = list.sortBasedOnColBThenColC();

a query is created based on the sorted list

String query = "select * from tableX where colA=request.getColA()";
for (int i = 0; i < list.size(); i++) {
    query.append("(colB=list[i].getColB()");
    query.append("and")
    query.append("(colC=list[i].getColC())")
    query.append("or")
}
query += "for update"; // blocking read

now i, am executing the following transaction.

start transaction

execute(query)

for i in list
   int rowsUpdated = update tableX set colD+=1 where colA=request.getColA() and colB=list[i].getColB() and colC=list[i].getColC();
   if(rowsUpdated < 1){
      insert into tableX (colA,colB,colC)

commit

the above transaction is a part of an API, so it could be executed in parallel, but, since the list is sorted, is it possible that we may face a deadlock in this scenario? if yes, how can we prevent it in this scenario?

0

There are 0 best solutions below