How does SQL Server handle parallel UPDATE transaction that prospectively would result into rows that would match the WHERE clause of 1st transaction?

Suppose I have the following case:

Transaction 1 does UPDATE using a WHERE clause and has not yet committed.

UPDATE TBL SET VAL1=10 WHERE VAL2=100

Transaction 2 tries to SELECT rows that are not part of the above WHERE clause. This query will work assuming there is an index and the database engine decides to take an IX lock on table, page and X lock on the rows that are part of the WHERE clause.

Now, say the Transaction 2 is an UPDATE query on rows that are not part of the SELECT query's WHERE clause.

UPDATE TBL SET VAL2=100 WHERE VAL1=500

When the above query runs, assuming same locks are held as mentioned above, it results in more rows satisfying the VAL2=100 criteria (which is also the WHERE clause in the 1st UPDATE). How does SQL handle this situation?

For example- when sql server sees the Transaction 2's UPDATE does it realize this and upgrade the transaction 1's lock to table lock and not allow Transaction 2's UPDATE from happening?

If you're asking specifically what might happen when T1 has completed but not committed, the general answer is that T1 is unaffected by any changes made by T2*. T2 might or might not block depending on the isolation level and which access method (index path) it uses to locate the rows it should update.

* The one exception to this would be if TBL is a memory-optimized table, and T1 is running under an isolation level that requires validation at commit time. In that specific scenario, a serialization or repeatable-read failure might occur, and T1 would rollback with an error. See the documentation.

If you're asking what happens when T1 and T2 execute concurrently:

Under serializable isolation, the effects of the two transactions on the persistent state of user data will be same as if the transactions had run serially (one after the other) in some order.

If the effects are as if T2 ran to completion first, transaction T1 will update rows affected by T2. Otherwise, the effects are as if T1 ran to completion first, before T2 started.

Either outcome is feasible. Which you would see in practice depends on timing. Notice the effects are "as if". The two transactions may still overlap in time.

At other isolation levels, a broader variety of outcomes are possible. None, some, or all of the rows affected by T2 may be encountered by transaction T1.

Under snapshot isolation, it is possible for transaction T1 to fail with an error if it encounters rows modified by T2 (or anyone else) since its transaction started.

StackExchange Database Administrators Q#299247, answer score: 9