Take the 2-minute tour ×
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.

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
    
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

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.