In short, Is UPDATE-WHERE-EXISTS able to handle concurrency? If not, then is there any way to get that done?

I too think this is the way to go. Step 1: First you have to make sure the connection string is correct. Try this. string str = ("SELECT username,password FROM dbo.Login "); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(str, conn); conn.Open(); DataTable dt = new DataTable(); System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd); da.Fill(dt); then debug and see if the da has all details from the database table. Step 2: If step 1 was successful, use your program, debug and see what is the value of str just before the query is fired. Execute that query directly from Query Analyzer and verify the results.

This is a SQL Server query (I am using SQL Server 2008). I have an application which runs jobs. I have a JOB table with columns JOB_ID, JOB_STEP etc. When a job is started, an entry is made in this table. When each step is completed, this entry is updated to reflect the next step in JOB_STEP column. The application is a multi processing application, ie, there are multiple instances of the same application running on multiple servers. All of them read, insert and update into the same database table. (There is only one instance of the DB). Now, I have a business requirement that no two jobs should be on step 20 (or step 100) together. Ie, if any one job is already on step 20 or 100, all other jobs should wait till that job step is completed. I have a stored procedure which the application calls to update the step info. I tried something like update JOB set JOB_STEP = @nextStep where ( (@nextStep <> '20' AND @nextStep <> '100') OR ( (@nextStep = '20' OR @nextStep = '100') AND NOT EXISTS ( select * from LAUNCH_STATISTICS where (JOB_STEP = '20' OR JOB_STEP = '100') and JOB_ID <> @jobId ) ) ) and JOB_ID = @jobId; But it didn’t work. If jobs try to change steps one after the other, this works. But if I call this SP from two different processes at the same time, both of them manage to end up with Step 20 (or 100) sometimes. Please help me in finding out what I am missing. I tried with different TRANSACTION_ISOLATION levels but it didn't help.

Thank you very much. Really helpful leads.

Thanks Jimmanuel for the reply. It gives some good pointers. I am more interested in the "Outside the Database" solutions. I suppose there are no design patterns as such which cover this scenario. Right?

I have a process which can be run from any number of servers. All of them access the same database. How do I synchronize the severs data access? Here are the details. My common database server has a table T1. It has columns C1, C2 and C3. My process has the following steps. 1. Read T1 table. 2. If there is any row with value V1 for C1, wait in a loop and go to step-1. 3. If there are no such rows, insert a row with V1 for C1 and appropriate values for the rest of the columns. 4. Start my business logic. 5. Once the business logic is complete, delete the row entered in step-3. Now the same process runs from different servers. So there is a chance that 2 or more processes will do steps 1 and 2 simultaneously and might end up creating 2 or more rows with V1 value for C1. I don't want that to happen. How will I make sure only once process can perform steps 1 and 2 at a time? I can not use lock and monitor to synchronize the access as I am running from different process spaces. How do I do it then?