Atomic update in SQL with two queries

202 views Asked by At

Assume that I want to do atomic update for a column with two queries:

  1. SELECT num FROM table WHERE id = ? Put num inside variable x
  2. UPDATE table SET num = x + 1 WHERE id = ?

And I make these two queries inside a transaction with isolation level: repeatable read.

Is it possible to have a deadlock situation? Let's say there are two transactions running.

T1 reads the value num and acquires the share lock on this row

T2 reads the value num and acquires the share lock on this row

T1 now wants to get the exclusive lock to write but cannot because T2 holds a share lock

T2 now wants to get the exclusive lock to write but cannot because T1 holds a share lock

We have deadlock here. But I tried in the code with Spring data JPA, there is no deadlock. Instead I have race condition and the final incremented value is less than what is expected. If I move to isolation level serializable, then I will have deadlock, and there is no race condition. The final increment value is good.

I thought serializable only deals with the range lock. Why does the deadlock happen in serializable isolation level and not repeatable read? with the two queries above.

PS: Don't tell me to user just one single UPDATE statement, I am trying to learn how to manual do atomic update with two statement and learn more about transaction here.

0

There are 0 answers