选择行并更新相同的行进行锁定? [英] Select rows and Update same rows for locking?

问题描述

我需要编写一个过程，允许我选择 x 行，同时更新这些行，以便调用应用程序知道这些记录已被锁定并正在使用中.我在表中有一个名为锁定"的列.下次调用该过程时，它只会提取下 x 条没有选中锁定"列的记录.我已经阅读了一些关于 SQL 服务器的 OUTPUT 方法，但不确定这是否是我想要做的.

I need to write a procedure that will allow me to select x amount of rows and at the same time update those rows so the calling application will know those records are locked and in use. I have a column in the table named "locked". The next time the procedure is called it will only pull the next x amount of records that do not have the "locked" column checked. I have read a little about the OUTPUT method for SQL server, but not sure that is what I want to do.

推荐答案

使用 OUTPUT 投票支持 Cade Roux 的答案:

Vote for Cade Roux's answer, using OUTPUT:

UPDATE #tbl
   SET locked = 1
OUTPUT INSERTED.*
 WHERE id IN (SELECT TOP 1 id
                FROM #tbl
               WHERE locked = 0
            ORDER BY id)​

以前:

<小时>

这是我能想到使用临时表的少数几次之一:

Previously:

---

This is one of the few times I can think of using a temp table:

ALTER PROCEDURE temp_table_test
AS
BEGIN

   SELECT TOP 5000 * 
     INTO #temp_test
     FROM your_table
    WHERE locked != 1
 ORDER BY ?

   UPDATE your_table
      SET locked = 1
    WHERE id IN (SELECT id FROM #temp_test)

   SELECT *
     FROM #temp_test

   IF EXISTS (SELECT NULL
                FROM tempdb.dbo.sysobjects
               WHERE ID = OBJECT_ID(N'tempdb..#temp_test'))
   BEGIN
     DROP TABLE #temp_test
   END

END

这个:

1. 获取您想要的行，将它们填充到本地临时表中
2. 使用临时表更新要锁定"的行
3. 从临时表中选择以提供结果集输出
4. 删除临时表，因为它们存在于会话中

这篇关于选择行并更新相同的行进行锁定?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！