The MODIFY statement requires an exclusive table lock. Other sessions, even those using READLOCK=NOLOCK, cannot access the table until the transaction containing the MODIFY statement is committed.

Two exceptions are the MODIFY TO TABLE_DEBUG variant, which takes a shared table lock, and the CONCURRENT_UPDATES option, which takes only a brief exclusive lock at the end of the modify operation.