使用SQL Server作为资源锁定机制 [英] Using SQL Server as resource locking mechanism

问题描述

给出一个逻辑资源标识符表(每行一个)，对于任意数量的数据库客户端而言，执行以下操作的最佳方法是什么:

Given a table of logical resource identifiers (one per row), what is the best way for an arbitrary number of database clients to perform the following operations:

• 要求对特定资源的访问权(如果尚未声明)
• 选择下一个可用资源并声明它的资源(与上面类似)
• 释放先前声明的资源

(该表将有一个声明者"列，在未声明的行中为NULL.)

(The table would have a "claimant" column, which would be NULL in unclaimed rows.)

我坚持这些操作的原子性:在我为请求的/下一个可用资源进行SELECT之前，我需要一个全表锁吗?做到这一点的方法?我现在没有大量数据，但是我希望保持表格尽可能地可访问.

I'm stuck on the atomicity of these operations: would I need a whole-table lock before I SELECT for the requested / next available resource (and afterwards UPDATE, assuming the claim was successful), or is there some more granular way to do this? I don't have a huge volume of data now, but I'd prefer to keep the table as accessible as possible.

推荐答案

您基本上是在描述基于经典队列的工作流，并且应该考虑使用真正的队列.

You are basically describing a classical queue based workflow, and you should consider using a real queue.

为了便于讨论，以下是您如何实现自己的期望:

For the sake of discussion, here is how you achieve what you wish:

• 声明特定资源:SELECT ... FROM resources WITH (UPDLOCK, ROWLOCK) WHERE key = @key.如果资源已被声明，将阻塞.如果资源已被声明，请使用锁定超时来返回异常. key 必须被索引并且唯一.
• 下一个可用资源:SELECT ... FROM resources WITH (UPDLOCK, ROWLOCK, READPAST) ORDER BY <accessorder>.您必须通过定义顺序来表达资源的偏好(最旧，最高优先级等)
• 释放声明的资源:COMMIT您的交易.

• claim specific resource: SELECT ... FROM resources WITH (UPDLOCK, ROWLOCK) WHERE key = @key. Will block if resource is already claimed. Use lock timeouts to return exception if resource already claimed. key must be indexed and unique.
• next available resource: SELECT ... FROM resources WITH (UPDLOCK, ROWLOCK, READPAST) ORDER BY <accessorder>. You must define a order by to express the preference of resources (oldest, highest priority etc)
• release a claimed resource: COMMIT your transaction.

问题的要点是使用正确的锁定提示，而这种问题确实需要显式的锁定提示才能解决. UPDLOCK将充当声明"锁. ROWLOCK创建正确的粒度，防止服务器优化"到页面锁定. READPAST允许您跳过声明的资源.将UPDLOCK放置在行上将锁定该行，并允许您稍后对其进行更新，但将阻止其他操作，例如将在锁定的行上阻塞的普通读取提交的SELECTs.想法是，尽管如此，您将无论如何都要更新该行，这将放置一个不可避免的X锁.如果您想让表更可用，则可以使用应用锁相反，但是要正确完成该任务要困难得多.您将需要在资源的字符串描述符上请求应用锁定，例如键值或键的CHECKSUM，或者它是

The gist of the problem is using the right lock hints, and this kind of problem does require explicit lock hints to solve. UPDLOCK will act as a 'claim' lock. ROWLOCK creates the right granularity preventing the server from 'optimizing' to a page lock. READPAST allows you to skip claimed resources. Placing UPDLOCK on the rows will lock the row and allow you to update it later, but will prevent other operations like ordinary read-committed SELECTs that will block on the locked row. The idea is though that your are going to UPDATE the row anyway, which will place an unavoidable X lock. If you want to keep the table more available you can use app locks instead, but is significantly harder to pull off correctly. You will need to request an app lock on a string descriptor o the resource, like the key value, or a CHECKSUM of the key or it's %%LOCKRES%% value. App locks allow you to separate the scope of the 'claim' from a transaction by requesting the app lock at the 'session' scope, but then you have to release the claim manually ('transaction' scoped app locks are released at commit time). Heads up though, there are a thousand ways to shoot yourself in the foot with app locks.

这篇关于使用SQL Server作为资源锁定机制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！