在SQL Server中为C＃.NET中的多个Windows服务锁定select命令 [英] Locking a select command in SQL server for multiple windows services in C# .NET

问题描述

I am using 3 windows services for sending bulk mail using SMTP in C#.
I am selecting one row at a time using 
"select top 1 * from EmailMaster with (updlock)  WHERE STATUS='P' ORDER BY MAILID"
and then update the status to I when there is a record using 
Update EmailMaster  set status='I' where MAILID= "12345" - service 1.
Update EmailMaster  set status='I' where MAILID= "12345" - service 2.
All the three windows services are same and are doing same work.In logs I can see that two or more services are picking the same record and updating it to I for a particular mailID and email is sent twice even after using UPDLOCK. I also tried exclusive lock like "select top 1 * from EmailMaster  with ( XLOCK, ROWLOCK)  WHERE STATUS='P' ORDER BY MAILID" but results the same.
I want to lock the row for only one service.
Any help would be appreciated.

我尝试过：



我尝试使用updlock和exclusive lock for select但结果相同

What I have tried:

I have tried using updlock and exclusive locks for select but results the same

推荐答案

UPDATE语句中的WHERE子句应该也包括状态：

The WHERE clause in your UPDATE statement should include the status as well:

UPDATE EmailMaster SET status = 'I' WHERE mailid = 12345' AND status = 'P'

如果更新失败，你的代码就会知道另一个服务已经检出了记录。



但是，说真的，我有一些应用程序正在运行是一名经理，返回要处理的服务的记录。这些服务无法直接访问数据库。这个管理器所做的就是从数据库中获取记录，跟踪哪个服务检出了哪个记录，然后在服务完成后释放记录。这也可以让您设置超时。如果服务由于某种原因崩溃或被关闭，则可以将记录返回到池中，以便另一个服务实例可以检出它。

If the update fails, your code will know that another service has checked out the record.

But, seriously, I'd have some app running that is a manager, returning records for the services to work on. The services wouldn't have direct access to the database. All this manager would do is grab records from the database, track which service checked out which record and then release the records when the service is done with them. This can also give you the ability to set a "timeout". If a service crashes or is shutdown for some reason, the record can be returned to the pool so another service instance can check it out.

这篇关于在SQL Server中为C＃.NET中的多个Windows服务锁定select命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！