How to skip rows from getting selected in SQL-CodePudding

How to skip rows from getting selected which are holding locks ?

Begin tran

Select * 
From table with(holdlock) 
Where id = 2

In second session, when query gets executed, the row which is has id value of 2 should be skipped in the result.

CodePudding user response：

The (holdlock) holds the lock until the end of the transaction - but this is a shared lock - e.g. other readers aren't blocked by that lock ...

If you really must do this, you need to establish (and hold on to) an exclusive lock

Begin tran

Select * 
From table with (updlock, holdlock) 
Where id = 2

and use the WITH (READPAST) clause in the second session, not to be stopped by the exclusive lock.

PS: updated to use updlock, based on @charlieface's recommendation - thanks!

CodePudding user response：

I guess, you can try READPAST-hint

https://www.sqlshack.com/explore-the-sql-query-table-hint-readpast/

CodePudding user response：

READPAST will skip existing locks. But you need another lock hint, otherwise SELECT will not keep the lock.

XLOCK, HOLDLOCK (aka SERIALIZABLE) is an option, but can be restrictive.

You can use UPDLOCK to keep a lock until the end of the transaction. UPDLOCK generates a U lock, which blocks other U or X (exclusive writer) locks, but still allows S reader locks. So the data can still be read by a read-only process, but another process executing this same code would still be blocked.

Begin tran

Select * 
From table WITH (UPDLOCK, READPAST) 
Where id = 2

-- etc