I'm pretty new to database locking and had a basic question. My understanding is that any UPDATE statement locks the row in the table and hence, no other transaction can read/write that row. If so, consider a scenario:
Initial table: user_id = 1, count = 10
Transaction 1:
A: UPDATE table SET count = 20 WHERE user_id = 1
Transaction 2:
B. variable b = SELECT count FROM seats WHERE user_id = 1
C. UPDATE seats SET count = {b} + 1 WHERE user_id = 1
If the order of execution is: 2B - 1A - 2C, what will be result of count in the table, will it be 11 or 21?
If my understanding is correct, when 1A statement is executed, the DB puts a write lock on that row which blocks 2C's execution. Now my question is, once 1A is done,
will transaction 2 re-evaluate all queries(2B & 2C) in T2 related to that locked row - (which would re-evaluate the value of variable b to 20)
OR
only re-evaluates its blocked query(2B) - (which means variable b will still have its old value of 10)?
EDIT:
What if 2B and 2C where nested queries? Something like:
UPDATE seats SET count = (SELECT count FROM seats WHERE user_id = 1) + 1 WHERE user_id = 1
Let's put the steps in the order you describe:
Transaction 2:
B. variable b = SELECT count FROM seats WHERE user_id = 1
Suppose b now has value 10.
Transaction 1:
A: UPDATE table SET count = 20 WHERE user_id = 1
This locks the row until Transaction 1 finishes.
Transaction 2:
C. UPDATE seats SET count = {b} + 1 WHERE user_id = 1
This also wants the lock that Transaction 1 holds, so Transaction 2 must wait.
Transaction 1:
D: COMMIT
Transaction 1 releases its lock when it commits.
Transaction 2:
C. UPDATE seats SET count = {b} + 1 WHERE user_id = 1
It can now go ahead, because the lock it wants is free.
The row now has value 20, since Transaction 1 committed. But the client for Transaction 2 still has value 10 in its variable b, so it updates the row to 10 + 1, or 11.
This overwrites the value set by Transaction 1. A third transaction may query after Transaction 1 committed and before Transaction 2 commits. Then it will read the value 20. But it will soon be overwritten as soon as Transaction 2 commits.