Sometimes you need to acquire a limited resource in an atomically, concurrent way. This is what semaphores are for. But when this resource is in a MySQL row, apparently there is no straightforward way of doing it.

In my case, I had a row with two fields used to limit maximum resource usage: used and limit, both integer fields. Let’s complete it with an id, and then all the remaining information you need. To give you a more concrete example, I used this to limit the number of times you could possibly use a discount coupon.

So, while investigating the best way to do it, I considered the following options:

• Using table locking: the problem is that, if you use a MyISAM table, this locking is done at a table level. Also, you statement gets more complicated, because you have to lock, modify and unlock.
• Using transactions: this is not supported by MyISAM engine, and in my case I was adding this to an already existing table (not created by me, BTW…).

Finally, I came with the following method, which I think is pretty clear, clean and compatible. First of all, the query uses an atomically UPDATE statement, like the one you can found in this Stack Overflow question: Is incrementing a field in MySQL atomic?

$query = "UPDATE coupon SET used = used + 1 WHERE ID_coupon='" . $this->id . "' AND used < limit";

Now, notice the WHERE clause. It selects the row by two criteria: row id (as usual) and the condition that the used field is less than the limit field. So, what does mean? If the number of used coupons is less than the limit, the condition will be fulfilled and the row will be found by the database engine and updated, but if it is not it won’t be found, thus the update will not happen, effectively limiting the used value.

Finally, the other reason of doing this way is that MySQL returns the number of rows affected by the query. Usually, this will be 1, but if we’ve already reached the limit the condition will not be fulfilled and the number of affected rows will be 0. So, in PHP we can check this condition with something like this (in my case I was using PEAR DB, but you can find the equivalent statement to affectedRows() for MySQLi or PDO):

$res=@$dbhm->query($query);
// Error handling omitted for brevity
$rows = $dbhm->affectedRows();
if ($rows > 0) {
  // We where able to use one coupon :D
  return true;
} else {
  // All the coupons have already been used :(
  return false;
}

Notice that probably this technique works on other DBs, and also can be applied to other languages than PHP.