0

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?

5
  • 1
    Nope, a single statement cannot generate a deadlock, only a lock wait timeout issue if locked rows are not released quickly Commented Jan 5, 2024 at 10:36
  • 1
    Please provide the rest of the transaction. The FOR UPDATE lasts until COMMIT. Commented Jan 6, 2024 at 21:38
  • @Shadow the someSortedList can have different elements, but the list would always be sorted. Commented Jan 7, 2024 at 18:48
  • @RajatAggarwal I'm sorry, but I do not understand hiw your comment is relevant to my comment. Commented Jan 7, 2024 at 19:16
  • added complete details. Commented Jan 8, 2024 at 14:53

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.