Assume that I want to do atomic update for a column with two queries:
SELECT num FROM table WHERE id = ?
Putnum
inside variablex
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.