Synchronisation in Multi Server environment

[c0der2009]

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?

[Jimmanuel]

One way would be to create a separate table in the DB for locks (or just reuse the existing one). Before a process starts step 3 (or whatever the crucial step is) it checks that table for a well known value, if it exists then it knows that some other process has started business logic and it should wait. If the lock value isn't found then it can create it and continue on. The lock value that it created will signal other processes that it is currently executing business logic and they should wait. This will work if the DB column that you're using for a lock is properly setup with a primary key constraint. If two processes try to write the same value into a PK column at (nearly) the same time the first will succeed and the other will throw an exception. Another "Outside the Database" solution would be to make all of the processes communicate with each other via some other mechanism (sockets, MSMQ, File IO, etc) and work out that way which one should be doing what.

[c0der2009]

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?

[Jimmanuel]

Sure there are, look up "concurrency[^]", "transactions[^]", "inter-process communication[^]" (aka IPC) and "messaging systems[^]". I haven't looked for the others but I know that there's a decent IPC article on CP somewhere. If you have lots of time to read, this[^] book has a chapter about transactions and concurrency and this[^] is entirely about building applications and messaging protocols in distributed systems. :badger:

[c0der2009]

Thank you very much. Really helpful leads.

[Jimmanuel]

Glad to help :) :badger: