使用 SQL Server 2000 表作为工作队列时，有哪些方法可以编写可扩展的服务？
如果我有一个 SQL Server 2000 数据库表用作要执行的工作队列，那么以可扩展的方式完成该工作的最佳方法是什么？场景：有一个 Windows 服务将为表中的每一行执行一些可能长时间运行的工作。当只有一个服务正在运行时，很容易获取前 10 或 100 行，执行工作（即使以多线程方式），然后更新这 10 或 100 行以表示工作已完成。如果我想在两台服务器上运行相同的服务，我需要一个锁定机制，这样服务就不会获取相同的行。我最初的想法是在表上添加一个字段，该字段指示在执行工作时该行被锁定，因此该服务将更新此“锁定”字段，直到工作完成并且选择要处理的一组行时确保行没有被锁定。
这是处理此类工作的最佳方法还是可以使用数据库级行锁定来执行相同的操作？我知道更理想的解决方案可能是消息队列，但假设我想使用数据库表来进行工作队列。另外，有人建议这种类型的事情在 Oracle 世界中要简单得多，其中数据库级行锁定将是解决方案，我在 SQL Server 2000 上使用行锁定时运气不佳，但可能会遗漏一些东西。
更新：添加标签
If I have a SQL server 2000 database table I'm using as a queue of work to be performed, what are the best methods for doing that work in a scalable way? The scenario: There is a windows service which is going to perform some, potentially long running, work for each row in a table. When only one service is running it's easy to grab the top 10 or 100 rows, perform the work (even in a multi threaded manner) and then update those 10 or 100 rows to say the work is done. If I want to run that same service on 2 servers I need a locking mechanism so the services aren't grabbing the same rows. My initial thoughts would be to add a field on the table which indicates that row is locked while work is being performed, so the service would update this "locked" field until the work is done and when selecting a group of rows to work on would make sure the rows are not locked.
Is this the best way to handle this type of work or is it possible to use database level row locking to do the same? I'm aware that a more ideal solution would likely be message queueing, but let's assume I want to use a database table for the queue of work. Also it's been suggested this type of thing is much more straightforward in the Oracle world where database level row locking would be the solution, I've not had great luck with using row locking on SQL Server 2000, but may be missing something.
Update: Added tags
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您确实选择使用数据库表而不是消息队列框架，则可以使用以下模式：
将用于拥有工作人员的列添加到您的队列表中。当工作人员寻找新工作时，它可以查询队列表中工作人员列为空的下一个工作：
然后工作人员可以尝试通过执行更新语句来声明该工作的所有权：
如果没有行受到此更新语句的影响，该工人可以假设另一个工人击败了它，声称拥有所有权，从而继续进行下一个工作。如果工作人员成功更新工作人员列，则它可以完成工作。
您还应该跟踪崩溃或挂起的工作线程，并为挂起或崩溃的工作线程声明的作业重置工作列。这些工作随后将被其他工人接手。请注意，这意味着需要设计作业以便可以重试。跟踪挂起或崩溃的工作人员可以通过让工作人员定期 ping 工作人员表来完成。
If you do choose to go with a database table instead of a message queue framework, you could use the following pattern:
Add a column for owning worker to your queue table. When a worker looks for new work, it could query the queue table for next job in queue with worker column null:
Then the worker can try to claim ownership of the job by executing an update statement:
If no rows are affected by this update statement, the worker can assume that another worker beat it to claiming ownership and thus move on to the next job. If the worker succeeds in updating the worker column, it can do the work.
You should also keep track of crashing or hanging worker threads and reset the worker column for jobs claimed by a hanging or crashed worker. These jobs will then be picked up by other workers. Note that this implies that jobs need to be designed so that they can be retried. Keeping track of hanging or crashing workers can be accomplished with having workers ping a worker table at regular intervals.