Lock mechanism between process on multiple machines

Question

I have a C# application with NHibernate that must do some operations over a SQL Server DB.

Our goal is to run this app multiple times in the same machine and also in other machines.

Actually app insert a row in a SQL Table and check if the first row with minimum date is the row with the correct PID and machine name so the app set the lock and do the work.

We have problems with this logic because app must also delete lock of other apps if the app that retain the lock is crashed.

Any hint about changing the lock architecture?

The problem is that app can run over multiple machines.

Answer 1

How about managing the lock in the DB? You can have a table/stored procedure, where each running instance registers to, gets a unique id, and uses the stored procedure to do lock&free. (Same technique as preventing multiple logins for a single user from several machines).

Answer 2

Either

• Have a (server) process that manages the locks - all clients registered and request locks from this server.
• Pessimistic offline locking

I've implemented it both ways.

The offline locks can work with databases, but there is the possibility that you can introduce thrashing if there are too many processes constantly waiting on the lock (although a decent db server should cache info). Also, make sure you use ReadCommited transaction isolation level when doing anything with that table, and be prepared for deadlocks