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?