How to safely increment database values with limits in PHP and MySQL

Other Questions MySQL
1

I’m working with two database tables and need to increment a counter safely. Here’s my setup:

Table: records

+----+-------+--------+
| id | count | cat_id |
+----+-------+--------+
|  1 |    15 |      2 |
+----+-------+--------+

Table: categories

+---------+-------+
| cat_id  | limit |
+---------+-------+
|    2    |   100 |
+---------+-------+

I want to increment the count field when a user clicks on a record, but only if it hasn’t reached the limit from the categories table (joined by cat_id).

My current approach has a race condition:

$query = $database->query("SELECT records.count, categories.limit 
                          FROM records 
                          JOIN categories ON records.cat_id = categories.cat_id 
                          WHERE records.id = " . $record_id);
$data = $query->fetch_assoc();

if ($data["count"] < $data["limit"]) {
  $database->query("UPDATE records 
                   SET count = count + 1 
                   WHERE id = " . $record_id);
} else {
  echo "Maximum limit reached!";
}

The problem is that another user might update the count between my SELECT and UPDATE queries. What’s the best way to make this operation atomic or combine both queries into one safe operation?

2

Database transactions handle this perfectly - no external dependencies needed. Just wrap everything in START TRANSACTION and COMMIT blocks. The trick is using SELECT FOR UPDATE to lock the row while you’re checking:

$database->query("START TRANSACTION");
$query = $database->query("SELECT records.count, categories.limit FROM records JOIN categories ON records.cat_id = categories.cat_id WHERE records.id = $record_id FOR UPDATE");
$data = $query->fetch_assoc();

if ($data["count"] < $data["limit"]) {
    $database->query("UPDATE records SET count = count + 1 WHERE id = $record_id");
    $database->query("COMMIT");
} else {
    $database->query("ROLLBACK");
    echo "Maximum limit reached!";
}

FOR UPDATE stops other transactions from touching that row until you commit or rollback. I’ve used this for inventory systems and vote counters - handles heavy concurrent traffic like a champ. Don’t forget to add proper rollback cleanup if connections fail.

3

JOIN works but gets messy with multiple conditions or tracking failed attempts. I’ve run similar counter systems in production - the real pain starts when you scale up.

Better approach: automate the whole thing. Set up a webhook for user clicks, then run a workflow that handles the database check and increment atomically.

I built this for a feature tracker. The workflow checks count vs limits, increments if valid, and queues notifications when you hit limits. No race conditions and way more flexible than raw SQL.

You get retry logic, logging, and error handling without messy PHP code. Runs independently so your app stays fast.

4

Skip the transactions and complex joins - just use UPDATE with ROW_COUNT() to check if it worked. Your single UPDATE with the WHERE condition is already atomic, you just need to know if it actually incremented or hit the limit.

$database->query("UPDATE records r JOIN categories c ON r.cat_id = c.cat_id SET r.count = r.count + 1 WHERE r.id = $record_id AND r.count < c.limit");

if ($database->affected_rows == 0) {
    echo "Maximum limit reached or invalid record!";
} else {
    echo "Counter incremented successfully";
}

You get atomic operations without the locking overhead from FOR UPDATE. I’ve used this same approach for rate limiting API calls - handles load well and keeps things simple. The affected_rows check instantly tells you if the increment worked or got blocked by the limit.

5

use a single UPDATE with a WHERE clause that checks both conditions. try UPDATE records r JOIN categories c ON r.cat_id = c.cat_id SET r.count = r.count + 1 WHERE r.id = ? AND r.count < c.limit. mysql handles the atomicity and you won’t get race conditions.