Click here to Skip to main content
14,388,492 members
Rate this:
Please Sign up or sign in to vote.
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.


What I have tried:

I have tried using updlock and exclusive locks for select but results the same
Posted
Updated 26-May-16 7:30am
Comments
ZurdoDev 26-May-16 12:25pm
   
The obvious question is why do you have 3 separate services all doing the same thing?

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
   
Comments
Sascha Lefèvre 26-May-16 12:32pm
   
+5
maverick1991 26-May-16 12:36pm
   
Thanks for the prompt reply.
Tried...but this doesn't work.
Dave Kreskowiak 26-May-16 12:39pm
   
If done properly, yes it does.

You have to do have a status for each "phase of a record, which includes "just checked out but not processed yet".

You grab the record and immediately try to change it's status to "flag it checked out". If that fails, then some other service has the record and this service has to abandon it and go get another one.
maverick1991 26-May-16 13:49pm
   
ok...got u
That means I have to explicitly do the locking in my .net code too for that particular record. Then what's the purpose of locking in sql server query?
Question remains the same.
Thanks!!!
Dave Kreskowiak 26-May-16 13:59pm
   
Locking is there to piss off your DBA's. The record is locked for the extent of the query but is VERY difficult to manage properly. Think about this. You app locks a record and then crashes. The lock doesn't reset itself. It's stuck until someone manually unlocks the record or some other piece of code recognizes the problem and unlocks it.

The way you seem to try to use the locks is wrong. The lock only applies for as long as a SQL Transaction is active. You don't appear to be using one so the lock really isn't doing anything.
George Jonsson 26-May-16 21:17pm
   
+5 Very good point with the manger app.
Gives only one entrance point into the database.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100