TSQL SELECT then UPDATE in one transaction, then return SELECT
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.
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;
Not knowing 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.
精彩评论