Insert statements while an update statement is running - will the inserted rows be affected when NOT EXISTS is used?

Question

In the statement below, if my update statement is running and at the same time new rows are inserted. Is there a possibility of the newly inserted rows being affected by the update statement?

  UPDATE    x 
  SET     x.OpenCloseStatus = 'CLOSED'
  FROM  dbo.OrderWOrkflow x
  WHERE 1=1

  AND NOT EXISTS
        (
        SELECT  MAX(z.OrderWorkflowID)
        FROM    dbo.OrderWorkflow  z
        GROUP BY
                z.HaulRequestOrderID
        HAVING 
                MAX(z.OrderWorkflowID) = x.OrderWorkflowID
        )

If the "SELECT MAX(..." subquery runs first and then then main update happens. If there are rows inserted in between the subquery and the update, will they be inadvertently marked as "CLOSED" by the update?

I'm hoping that if rows are inserted in the middle of the update, they will not be affected.

Answer 1

In the scenario you described, where an UPDATE statement is running simultaneously with the insertion of new rows, the new rows inserted will not be affected by the UPDATE statement.

The reason is that each transaction in the database operates in isolation from other transactions. When the UPDATE statement starts, it creates a transaction that locks the rows it needs to update. This prevents other transactions from modifying those rows until the UPDATE transaction is complete and committed.

On the other hand, the new rows inserted will be part of separate transactions. They will not be locked by the UPDATE transaction and will not be affected by changes made by the UPDATE statement.

Therefore, if rows are inserted in the middle of the UPDATE statement, they will not be accidentally marked as "CLOSED" by the UPDATE statement. The UPDATE statement will only affect rows that existed before the UPDATE transaction began.