Pmax Pmax - 1 year ago 49
MySQL Question

How to prevent multiples instances of a script?

I have php scripts that I have to run on linux as well as on Windows servers.
I want to use the same scripts without doing any modifications for those 2 environments.

Theses scripts will be scheduled with cron (on linux) and with the windows scheduler (or other, I don't care for now) for my Windows environment.

However, some of theses scripts might take several minutes to complete. I just want to prevent the same script from being launched by the scheduler (cron or the windows's one) before it has finished the last time it was launched.

I'm not sure how to do that..
I want to be sure that the "lock" is released if something goes wrong during the execution, so it is launched again the next time without human intervention.

Maybe with a flock on a dummy file would do the trick, but i'm not sure how to do that.

I also have a MySQL database on theses servers. I thought maybe using a lock on the database side.

1- Start a transaction
2- Insert script name in a table.
3- execution of the script.
4- If successful then delete the row and commit the transaction or simply rollback;

If the script name is in the table, I then could prevent it from running.
If the script execution fails, then Mysql will automatically rollback the transaction so the row does not appear the next time the script is called.

But, in a transaction, is there a way for other connections see the uncommited data? if yes, how?

I also thought using a lock on the row if it is impossible to use the rollback thing..

1- Insert script name in a table if it doesn't already exists.
2- Start a transaction.
2- Select * from Table where script_name FOR UPDATE.
3- execution of the script.
4- If successful then release the lock (rollback or commit).

But my major problem here is with Mysql. Select FOR UPDATE hang until the previous lock is released or if the 50 seconds timeout(innodb_lock_wait_timeout variable) is elapsed. I would like Mysql to tell me right on the spot that my row is locked without affecting the whole Database. That is because the innodb_lock_wait_timeout variable is a global one (not a session one).
Is there another variable that mimic the NO_WAIT clause that is available in Oracle?

Or should I let the script hang 50 seconds without any problem?

What is the best approach on that as I am a php newbie and I don't want to cause any problems on the server.

Maybe I have another option that I didn't see..

Answer Source

I solved this problem using... sockets. It you can enable php_sockets extention then try it. Here is code sample:

$socket = socket_create(AF_INET, SOCK_STREAM, SOL_TCP);
if (false === $socket) {
    throw new Exception("can't create socket: ".socket_last_error($socket));
## set $port to something like 10000
## hide warning, because error will be checked manually
if (false === @socket_bind($socket, '', $port)) {
    ## some instanse of the script is running
    return false;
} else {
    ## let's do your job
    return $socket;

Bind socket on specific $port is safe operation for concurent execution. Operation system will make sure that there is no other process which bound socket to same port. You just need to check return value.

If script crashes then operation system will unbind the port automatically.

This also can be used in any language. I've tested it wide on perl and php based projects. It stoped parallel execution even when we've added script twice in crontab by mistake.