如何同时对表中的行进行操作

Lets say I have a table called Task:

假设我有一个名为Task的表:

| ID | TASK    | Other columns... |
|  1 | ...     | ...              |
|  2 | ...     | ...              |
|  3 | ...     | ...              |

and I have a stored procedure that:

我有一个存储过程:

Selects all available rows from the Task table

从Task表中选择所有可用行

Uses this data to do some work, such as updating other tables

使用此数据执行某些操作,例如更新其他表

Deletes these rows from Task

从Task中删除这些行

This stored procedure will be invoked concurrently by a number of client applications. How can I have the stored procedure work on mutually exclusive rows? E.g.

许多客户端应用程序将同时调用此存储过程。如何让存储过程在互斥行上工作?例如。

Tasks 1 and 2 are in the Task table

任务1和2位于任务表中

Stored procedure is invoked and selects tasks 1 and 2 and begins process them

调用存储过程并选择任务1和2并开始处理它们

Task 3 is added (stored procedure above is still running)

添加了任务3(上面的存储过程仍在运行)

Stored procedure is invoked again concurrently (previous stored procedure is not yet complete). This time the stored procedure should only select task 3.

同时再次调用存储过程(先前的存储过程尚未完成)。这次存储过程应该只选择任务3。

When either stored proecdure ends, it should delete the tasks it has worked on from the Task table

当任一存储的proecdure结束时,它应该从Task表中删除它所处理的任务

1 个解决方案

#1

I would rather suggest to go with optimistic concurrency control approach. This is implemented by using timestamp column in sql server. Everytime a record is updated, timestamp value gets incremented automatically.

我宁愿建议采用乐观的并发控制方法。这是通过在sql server中使用timestamp列实现的。每次更新记录时,时间戳值都会自动增加。

Whenever you are firing a select query you have to select Primary key column as well as timestamp column. Whenever you are updating this record your query should look like

无论何时触发选择查询,都必须选择主键列和时间戳列。每当您更新此记录时,您的查询应如下所示

UPDATE t SET Name = 'XYZ' from Employee t WHERE t.PrimaryKeyColumn = @Col1 
AND t.TimeStamp = @Timestamp

IF @@RowCount = 0
BEGIN
RAISEERROR('Record has been updated by another user',16, 1)
END

Edit:

Going with your question requirements

满足您的问题要求

I can think of creating a global temporary table having additional column called SPID(connectionID) with default value as NULL

我可以考虑创建一个全局临时表,其中包含名为SPID(connectionID)的附加列,其默认值为NULL

select *, NULL AS spId into ##TaskList

Key here is this global temp table will be available until the scope of process who created it. So you can use one permanent table for this task or keep the creator of temp table alive.

这里的关键是这个全局临时表将可用,直到创建它的进程范围。因此,您可以使用一个永久表来完成此任务,或者让临时表的创建者保持活动状态。

Since every stored proc will be running under different database connection hence all of them will be having unique SPID.

由于每个存储过程将在不同的数据库连接下运行,因此它们都将具有唯一的SPID。

Any proc working on T1, T3 has to update it's SPID into this global temp table before starting its process on T1, T3.

任何处理T1,T3的proc都必须在T1,T3上开始进程之前将其SPID更新为此全局临时表。

Similary remaining proc will query ##TaskList to lock Tasks with SPID = NULL on which they want to work upon.

类似的剩余proc将查询## TaskList以锁定他们想要处理的SPID = NULL的任务。

SELECT * FROM ##TaskList where SPID IS NULL
UPDATE SPID = @@SPID FROM ##TaskList where Task IN (T2, T4)

By this approach all the task will become mutually exclusive to stored procedure.

通过这种方法,所有任务将变得与存储过程互斥。

#1