Sql-server – UPLOCK to avoid multiple reads

concurrencylockingsql servert-sql

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?

Best Answer

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)
Related Question