Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 219
Bryan Lewis Avatar asked Nov 16 '09 17:11

Bryan Lewis


People also ask

Can we use update and select together?

The subquery defines an internal query that can be used inside a SELECT, INSERT, UPDATE and DELETE statement. It is a straightforward method to update the existing table data from other tables. The above query uses a SELECT statement in the SET clause of the UPDATE statement.

Can we do an update from a select statement?

You can use a subquery in the WITH clause (called a CTE or Common Table Expression) to update data in one table from another table. WITH subquery AS ( SELECT account_id, account_number, person_id FROM account ) UPDATE person SET account_number = subquery.

What update query will return?

Executed update queries always return the number of rows matched by the query, including rows that didn't have to be updated because their values wouldn't have changed.

How can I update two rows in one query?

There are a couple of ways to do it. INSERT INTO students (id, score1, score2) VALUES (1, 5, 8), (2, 10, 8), (3, 8, 3), (4, 10, 7) ON DUPLICATE KEY UPDATE score1 = VALUES(score1), score2 = VALUES(score2);


1 Answers

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;
like image 54
Remus Rusanu Avatar answered Nov 14 '22 21:11

Remus Rusanu