SQL Server Exlusive Row Lock Causing Table Select Blocking

Question

I have a table that represents incoming records, which has an ISPROCESSED column. I have multiple threads that select all rows where ISPROCESSED=0 with an exclusive row lock (XLOCK,ROWLOCK) and then perform a relatively long-running transaction. At the end they update the ISPROCESSED row to 1.

If when one thread in the pool is running this transaction, other threads are blocked from processing any new records that may come into the table during the first thread's transaction, and must wait for it to complete.

Theoretically, another thread should be able to process any new unprocessed records that come in (and place its own row locks on them). Is there any way to allow the select run by the other threads to not block, and simply select the rows on which there is no current exclusive lock? In other words, to see the table as though the currently locked rows don't even exist.

It seems you are using a relational database table as a queue. You might take a look at SQL Server Service Broker for this task and just use the table to maintain state. — Dan Guzman
– Dan Guzman, Commented Sep 21, 2014 at 15:30
@MartinSmith, there are pros and cons of both approaches as Remus mentioned. I suggested the original poster take a look at SB as that could reduce latency without polling constantly. — Dan Guzman
– Dan Guzman, Commented Sep 21, 2014 at 16:39

marc_s · Accepted Answer · 2014-09-21 15:03:37Z

Yes - use the SELECT (list-of-columns) FROM dbo.YourTable WITH READPAST.

This just skips over rows that are currently locked and thus inaccessible for the reading connection - as if those rows aren't there.

TomTom · Accepted Answer · 2014-09-21 15:16:36Z

Is there any way to allow the select run by the other threads to not block,

There is a way to tell a select to skip locked records. Le me check... google for "sql server skip locked rows" has some good references.

Look for READPAST table hint ;)

Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows.

Looks to me like the documentation has a good explanation and that this hint is what you look for.

Collectives™ on Stack Overflow

SQL Server Exlusive Row Lock Causing Table Select Blocking

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related