CODEWITHSUNDEEP
sqlsql-servert-sqltransactions
Rudra
Rudra

Reputation: 45

How to skip rows from getting selected in SQL

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.

Upvotes: 0

Views: 1770

Answers (4)

Rudra
Rudra

Reputation: 45

I achieved this with following code in second session.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READS
Select * from table WITH (Readpast)

Thank you all for your helps.

Upvotes: 1

marc_s
marc_s

Reputation: 754488

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!

Upvotes: 3

Charlieface
Charlieface

Reputation: 71579

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

Upvotes: 2

Sergey
Sergey

Reputation: 5217

I guess, you can try READPAST-hint

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

Upvotes: 0

Related Questions