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?