In SQL Server, how can I lock a single row in a way similar to Oracle’s “SELECT FOR UPDATE WAIT”?

You’re probably looking forwith (updlock, holdlock). This will make a select grab an exclusive lock, which is required for updates, instead of a shared lock. The holdlock hint tells SQL Server to keep the lock until the transaction ends.

FROM TABLE_ITEM with (updlock, holdlock)