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?
FOR UPDATElasts untilCOMMIT.