Does SQL write lock makes other transactions re-evaluate all queries in the tranasction-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

The answer is... it depends. Since you tagged MySQL I assume you are using that DBMS. But which engine? Let's assume InnoDB.

Are you explicitly starting transactions? If you don't, InnoDB by default will be in autocommit mode (unless you reset it). This means that each statement will be a transaction in itself, so the sequence 2B - 1A - 2C will show the "wrong" behaviour.

If you explicitly include 2B and 2C in a transaction, it still depends on the isolation level. The default isolation level of InnoDB, REPEATABLE READ, will prevent the "wrong" behaviour, and the same holds true for SERIALIZABLE (the most conservative level); but the two more optimistic levels, READ COMMITTED and READ UNCOMMITTED, won't.