Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Given the following table structure...

Orders
======
OrderId int PK

OrderEvent
===========
OrderEventId int PK
OrderId int FK
EventTypeId int FK

I've got multiple concurrent processes SELECT-ing orders but each order should only be read once overall. So upon an order being retrieved I need to add a row in the OrderEvent table to mark that an order has been read (EventTypeId = 1).

So my idea is to have the following query for the read...

SELECT * 
FROM Orders o WITH (UPDLOCK) 
WHERE NOT EXISTS (SELECT * 
                  FROM OrderEvent oe 
                  WHERE oe.OrderId = o.OrderID 
                    AND EventTypeId = 1)

This runs within a transaction and after the read the order event row is added to mark the order as having been read.

So the question is, will this guarantee that each order can only be retrieved once?

What I'm not sure about is when the lock is put on the order row? Is it before or after the where clause has been evaluated?

share|improve this question
2  
You want to use a queue for this. See Using tables as queues. – Remus Rusanu Aug 25 '13 at 11:18
    
The row I'm selecting (the order) isn't the same row that's being inserted (the event). So how can I use OUTPUT to do both steps within a single statement? – Ian Warburton Aug 25 '13 at 14:50
    
I guess that would work but holding a lock on a table during the whole process defeats having multiple concurrent process. – Paparazzi May 11 at 16:26

The UPDLOCK will prevent a subsequent call from reading an order that has already been picked up, but won't allow that subsequent call from reading others because it will wait to read all the rows that fit the criteria and you have explicitly told the engine that you intend to update them in this transaction.

If you add a READPAST hint as well, it will allow subsequent calls to grab any available rows that have not been picked up previously.

SELECT * 
FROM Orders o WITH (UPDLOCK,READPAST) 
WHERE NOT EXISTS (SELECT * 
              FROM OrderEvent oe 
              WHERE oe.OrderId = o.OrderID 
                AND EventTypeId = 1)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.