0

I will explain my scenario with an example.

I have multiple rows in my table. I am picking those one by one for processing. I need to lock the row for processing.

Sample code looks like,

select top 1 * into #open_order from orders with (xlock) 
where status = 'open' order by order_time;

EDIT: Added order by clause in the query.

My requirement is to run this in parallel connections. My problem here is, I cannot run this code on multiple connection in parallel. The second one waits until the first one commit the transaction.

Is there any way to exclude already locked rows from this select query?

I have come across with(readpast). But I don't know whether it can be used together or not.

EDIT: Sample data and expectation.

Orders table data:

id, order_time, status, remark 
1, 2019-01-01 00:00:01, 'open', 'Sample 1' 
2, 2019-01-02 00:00:01, 'open', 'Sample 2' 
3, 2019-01-03 00:00:01, 'open', 'Sample 1'

If first row is locked, I am expecting to get the second row as result of the query.

Sayuj
  • 7,464
  • 13
  • 59
  • 76
  • 4
    I'd use an `UPDATE` to set `status` to `processing` (or another, dedicated column if `status` should only hold a business value) with `ROWLOCK, HOLDLOCK` and an `OUTPUT` clause. This way the row is not locked for longer than necessary and you don't have to worry about aligning your business transaction logic with the database transaction logic. Note also that `TOP` without an `ORDER BY` gives rows in nondeterministic order. This is rarely what you want, even if you "don't care" about the processing order -- some notion of "oldest first" is usually expected. – Jeroen Mostert Feb 04 '19 at 11:54
  • Yes, you can use the `READPAST` hint in addition to `XLOCK`. – Dan Guzman Feb 04 '19 at 11:56
  • @DanGuzman Can you give me an example please? – Sayuj Feb 04 '19 at 12:12
  • An example is `WITH (XLOCK, READPAST)`. Also, add `ORDER BY` as @JeroenMostert suggested. – Dan Guzman Feb 04 '19 at 12:18
  • @DanGuzman I am getting deadlock when using `WITH (XLOCK, READPAST)`. – Sayuj Feb 04 '19 at 12:22
  • @Sayuj, make sure you have an index on `status, order_time`. – Dan Guzman Feb 04 '19 at 12:25
  • Note that even with proper indexing the potential for deadlocks is always there if the table has more than one index, especially if this is not the only query locking rows, because statements are not required to take locks on indexes in a particular order. There's a reason Service Broker was added to SQL Server -- to have a queue implementation that works once and for all. – Jeroen Mostert Feb 04 '19 at 12:26
  • What does "lock the row for processing" mean? I suspect you don't need to get involved with database transactions or locking at all. You just need another field to mark it as "reserved" – Nick.Mc Feb 05 '19 at 10:00
  • @Nick.McDermaid I have multiple workers running in parallel to process the order. I want to restrict two of my workers picking same order for processing. – Sayuj Feb 05 '19 at 10:04
  • What I would do is not a select but an update. Inside a transaction, update a control field with the workers id (I assume it has one). That row is now 'locked' since subsequent updates will not affect it (because the update statement has `WHERE ControlField IS NULL` in it). Now you can select it back and finish it. This works perfectly for me for smaller amounts of work. Not sure how it would scale for lots of parallel workers. – Nick.Mc Feb 05 '19 at 10:07
  • `select * from orders with(xlock) where id in (select top 1 id from orders with (readpast) where status = 'open' order by status, order_time)` looks working for me now. I am not sure about the scalability of this, need to research. @Nick.McDermaid Your comment on this please. – Sayuj Feb 05 '19 at 10:14
  • All I can say is that if you _update_ first inside a transaction, you've reserved the row without introducing possible deadlock / lock escalation issues. Not only that, but you have something in the table to help troubleshoot if there are issues. These kinds of transaction isolation questions come up and this is always my response – Nick.Mc Feb 05 '19 at 10:15

0 Answers0