How To Mutex Across a Network?

I have a desktop application that runs on a network and every instance connects to the same database.

So, in this situation, how can I implement a mutex that works across all running instances that are connected to the same database?

In other words, I don't wan't that two+ instances to run the same function at the same time. If one is already running the function, the other instances shouldn't have access to it.

PS: Database transaction won't solve, because the function I wan't to mutex doesn't use the database. I've mentioned the database开发者_高级运维 just because it can be used to exchange information across the running instances.

PS2: The function takes about ~30 minutes to complete, so if a second instance tries to run the same function I would like to display a nice message that it can't be performed right now because computer 'X' is already running that function.

PS3: The function has to be processed on the client machine, so I can't use stored procedures.

I think you're looking for a database transaction. A transaction will isolate your changes from all other clients.

Update: You mentioned that the function doesn't currently write to the database. If you want to mutex this function, there will have to be some central location to store the current mutex holder. The database can work for this -- just add a new table that includes the computername of the current holder. Check that table before starting your function.

I think your question may be confusion though. Mutexes should be about protecting resources. If your function is not accessing the database, then what shared resource are you protecting?

put the code inside a transaction either - in the app, or better -inside a stored procedure, and call the stored procedure. the transaction mechanism will isolate the code between the callers.

Conversely consider a message queue. As mentioned, the DB should manage all of this for you either in transactions or serial access to tables (ala MyISAM).

In the past I have done the following:

1. Create a table that basically has two fields, function_name and is_running

2. I don't know what RDBMS you are using, but most have a way to lock individual records for update. Here is some pseduocode based on Oracle:

   BEGIN TRANS

   SELECT FOR UPDATE is_running FROM function_table WHERE function_name='foo';

   -- Check here to see if it is running, if not, you can set running to 'true'

   UPDATE function_table set is_running='Y' where function_name='foo';

   COMMIT TRANS

Now I don't have the Oracle PSQL docs with me, but you get the idea. The 'FOR UPDATE' clause locks there record after the read until the commit, so other processes will block on that SELECT statement until the current process commits.

You can use Terracotta to implement such functionality, if you've got a Java stack.

Even if your function does not currently use the database, you could still solve the problem with a specific table for the purpose of synchronizing this function. The specifics would depend on your DB and how it handles isolation levels and locking. For example, with SQL Server you would set the transaction isolation to repeatable read, read a value from your locking row and update it inside a transaction. Don't commit the transaction until your function is done. You can also use explicit table locks in a transaction on most databases which might be simpler. This is probably the simplest solution given you are already using a database.

If you do not want to rely on the database for whatever reason you could write a simple service that would accept TCP connections from your client. Each client would request permission to run and would return a response when done. The server would be able to ensure only one client gets permission to run at a time. Dead clients would eventually drop the TCP connection and be detected as long as you have the correct keep alive setting.

The message queue solution suggested by Xepoch would also work. You could use something like MSMQ or Java Message Queue and have a single message that would act as a run token. All your clients would request the message and then repost it when done. You risk a deadlock if a client dies before reposting so you would need to devise some logic to detect this and it might get complicated.