TSQL SELECT then UPDATE in one transaction, then return SELECT

Question

I am really having trouble with a query in my ColdFusion application (backended to MS SQL 2008). I keep getting DB deadlock errors on this transaction:

<code>
<cftransaction>
   <cfquery name="selectQuery">
      SELECT TOP 20 item_id, field2, field3
      FROM Table1
      WHERE subject_id = #subject_ID# AND lock_field IS NULL AND
            NOT EXISTS (SELECT * FROM Table2 WHERE Table2.user_ID = #user_ID# Table1.item_id = Table2.item_id)
   </cfquery>

   <cfquery name="updateQuery">
      UPDATE Table1
      SET lock_field = 1, locked_by = #user_ID#
      WHERE Table1.item_id IN (#ValueList(selectQuery.item_id#)
   </cfquery>
</cftransaction>
</code>

Bascially, I have a table (Table1) which represents a large queue of waiting items. Users "checkout" items to give them a score. Only one user can have an item checked out at a time. I need to request a block of 20 items at a time for a given user. Those items cannot already be checked out and the user cannot have already scored them before (hence the lock_field IS NULL and NOT EXISTS statement in the SELECT). Once I have determined the list of 20 item_ids, I need to then update the queue table to mark them as locked so no one else checks them out at the same time. I also need to return that list of item_ids.

I was thinking it might work better if I moved this from a cftransaction to a stored proc on the SQL Server side. I am just not sure if the cftransaction locking is interfering somehow. I am not a TSQL guru, so some help would be appreciated.

Answer 1

Use a common table expression to select the data, then update the CTE and output from the UPDATE statement. This way everything is one single operation:

with cte as (
 SELECT TOP 20 item_id, field2, field3 
 FROM Table1 WITH (ROWLOCK, UPDLOCK)
 WHERE subject_id = #subject_ID# 
 AND lock_field IS NULL 
 AND NOT EXISTS (
   SELECT * FROM Table2 
   WHERE Table2.user_ID = #user_ID# AND Table1.item_id = Table2.item_id))
update cte   
 SET lock_field = 1, locked_by = #user_ID# 
 output inserted.item_id;

Answer 2

Not nothing much (read: anything) about PHP, but having some experience with TSQL you may want to consider changing your query to something like this:

update TABLE1 set LOCK_FIELD = 1
output inserted.item_id, inserted.OtherInterestingColumnsGoHere
from (select top 20 item_id from TABLE1(holdlock) ) as a
where a.item_id = table1.item_id

This should ensure that the items your select will be locked until the update is complete.

edit: added an output clause as the original question also wanted to know which rows were updated.