SQL: Advisory locking to prevent concurrent script execution

Tweet 0 Shares 0 Tweets 0 Comments

To automate various tasks on the web server, we use CRON scripts which run pre-set commands at a daily, weekly or custom interval. If the interval is short then there's a chance that scripts can overlap and cause problems.

This article explores a couple of ways to avoid a CRON-initiated script running into an older version of itself. And we're not looking at shell scripts, but scripts calling PHP and/or PostgreSQL.

Using a file lock (flock)

A typical approach on Linux is to create a 'lock file' which serves no purpose other than to indicate that a certain resource is locked or in use.

In this case the CRON script creates a lock file before running further commands, but will exit or wait for the lock to clear if there is already a lock file present.

Sample PHP code would look something like this:

<?PHP
  $fp = fopen("/tmp/lock.txt", "r+");

  if(flock($fp, LOCK_EX)) {
    // file lock acquired
    // execute script...
    // ...release file lock
    flock($fp, LOCK_UN);
  } else {
    // failed to get a file lock
  }

  fclose($fp);
?>

The first thing to note is that flock accepts a file handler/resource as the first argument and not an actual file name. And importantly, the acquired lock only applies to PHP and only to scripts that explicitly check for a file lock using the same methods.

Some Windows platforms use mandatory rather than advisory locking in which case a PHP file lock will apply system-wide.

In the above example, if the file is already locked by another script, further scripts requesting a lock will hang at the first flock statement until the file is unlocked by the owning process.

If instead you want the script to proceed directly to the else you need to change the flock command as follows:

<?PHP
  ...
  if(flock($fp, LOCK_EX | LOCK_NB)) {
    ...
  } else {
    // was unable to get a file lock
  }
  ...
?>

Now, instead of hanging, the script will jump directly to the else stanza, which previously would only be reached if flock got tired of waiting and timed out. Don't forget to always call flock with LOCK_UN (unlock) as soon as it's safe to do so.

A danger of a file-based system is that following a crash you may find that the lock remains active even though the script is no longer running. In that case the lock file needs to be manually deleted. PHP should be able to handle this in most cases.

Database locking

When your CRON scripts are working with database records, it's especially important that they don't overrun each other as that can lead to double-processing of data and other unintended consequences.

Consider a script that runs every few minutes to upload new orders from the website to an inventory management system:

SELECT all new orders;
Process each order, marking it as not new;
Exit;

This looks simple enough, but if there are a larger number of orders, or the receiving API is slow to respond, a second instance of the script may start before the first one has finished.

SELECT all new orders;
Process and upload order #101;
Process and upload order #102;
Process and upload order #103;
Exit;

SELECT all new orders;
Process and upload order #103;
Process and upload order #104;
Process and upload order #105;
Exit;

Hopefully you can see from above the potential problem. In this case order #103 is going to be processed twice because when the second script kicks in, that order is still seen as 'new'.

SELECT ... FOR UPDATE

One possible approach is to modify your SELECT query by appending FOR UPDATE. <?PHP # BEGIN; # SELECT * FROM tablename FOR UPDATE; # // other commands # COMMIT; ?>

The selected rows are locked database-wide so any PHP or other scripts running an UPDATE will be prevented from doing so and forced to wait until the relevant transaction (BEGIN ... COMMIT) completes.

While this definitely works, it blocks out any other script which might want to update the same rows, forcing them to continually wait for a window of opportunity. There's also no way from PHP to find out if the script is blocked.

Database explicit locking

It is also possible to wrap a transaction around these scripts with a LOCK TABLE command:

<?PHP
# BEGIN;
# LOCK TABLE tablename IN SHARE ROW EXCLUSIVE MODE;
// other commands
# COMMIT;
?>

But this has similar consequences to the above when there are multiple scripts operating on the same data at the same time.

Database advisory locking

In our specific case the answer was to implement advisory locking in the database - which operates along similar lines to flock, only everything is managed internally by the database.

Using a mixture of PHP and SQL, our script now runs as follows:

<?PHP
# BEGIN;
# SELECT pg_try_advisory_xact_lock(ID);
  if("t") {
#   SELECT * FROM tablename WHERE new;
    // other commands
#   COMMIT;
  } else {
#   ROLLBACK;
  }
?>

Note that pg_try_advisory_xact_lock is equivalent to LOCK_EX | LOCK_NB in that it immediately returns either true or false (or equivalent values). If, on the other hand, you want the script to wait until it does acquire the lock you can use pg_advisory_xact_lock which is equivalent to LOCK_EX.

Both of these are 'transaction' level locks meaning that they expire immediately at the end of the transaction (BEGIN ... COMMIT). There are also options for 'session' level locks, as well as shared locks. See under References below for a resource link.

What to use for the ID value?

Each of the Postgres advisory locking functions takes an integer argument. Normal practice is to use id values from the selected rows, in which case each row has a separate advisory lock applied. See the manual (below) for examples.

In our case, however, we wanted a single lock for our script which we can check before execution. Because a bigint is required it's not possible to use the script name, so we've simply used a hashing function to convert the script name into a 32-bit integer:

<?PHP
  $hash = crc32($_SERVER['PHP_SELF']);
# SELECT pg_try_advisory_xact_lock($hash)";
  ...
?>

Barring an extreme case where two scripts generate the same hash, we've now created a situation where our CRON script can never run more than one instance concurrently.

SKIP LOCKED in PostgreSQL 9.5

If you're already running PostgreSQL 9.5 you can modify your script to only process rows that are not locked using SKIP LOCKED:

# SELECT * FROM tablename FOR UPDATE SKIP LOCKED;

This would allow scripts to work concurrently on the same data table without conflict. In earlier versions a similar result requires the programmer to try for an advisory lock on each row.

In our case this could work if we moved fields that needed to be updated by CRON to a separate table for each script.

CRON Script #1:: SELECT orders.id, a, b, c FROM orders_extra_1 WHERE orders.new FOR UPDATE SKIP LOCKED;
CRON Script #2:: SELECT orders.id, d, e, f FROM orders_extra_2 WHERE orders.new FOR UPDATE SKIP LOCKED;

Now it doesn't matter how many instances of a CRON script are running as they can only ever access rows not in use by another instance.

References

PostgreSQL: PostgreSQL: Advisory Lock Functions
PostgreSQL: PostgreSQL: Advisory Locks

< SQL

Post your comment or question