Atomic Operations in SQL: Incrementing Counters the Right Way

During a recent project, I had the opportunity to work on a custom plugin I created as part of a technical test for a company. In this blog post, I want to focus on a specific part of that project—a small yet essential SQL operation that highlights the importance of atomicity in database transactions.

While working on this functionality, I encountered a scenario that required incrementing a counter stored in the database. At first, this might seem like just another simple task. However, the true power of SQL’s atomic operations, specifically the SET column = column + 1 construct, came into play and demonstrated why databases are so great at handling such operations.

In this post, I will walk you through that small fragment of code to show how simple yet critical it is to increment counters directly in SQL. More importantly, I’ll also explain why this approach ensures safety, efficiency, and reliability.

The Code: Incrementing Counters in SQL

Here’s the relevant part of the code:

$result = $this->wpdb->query(
    $this->wpdb->prepare(
        "UPDATE $this->table_name
         SET click_count = click_count + 1,
             ip_address = %s,
             user_agent = %s,
             referer_data = %s,
             last_clicked = %s
         WHERE id = %d",
        $data['ip_address'],    // User's IP address
        $data['user_agent'],    // User agent string
        $data['referer'],       // Referrer URL
        $data['last_clicked'],  // Timestamp of the click
        $data['id']             // Link ID
    )
);

I have admit that we fully understand the code above but let’s break in small parts:

Query Execution

$result = $this->wpdb->query(
    $this->wpdb->prepare(

$this->wpdb: This refers to the global $wpdb database handler in WordPress, which is used to interact with the database.
$this->wpdb->query: This method executes an SQL query and will return the number of rows affected in the case of UPDATE, DELETE, or INSERT statements.
$this->wpdb->prepare: This is a WordPress method to safely format and prepare SQL queries to prevent SQL injection. Placeholders (e.g., %s, %d) are replaced with sanitized values.

SQL Query

"UPDATE $this->table_name
 SET click_count = click_count + 1,
     ip_address = %s,
     user_agent = %s,
     referer_data = %s,
     last_clicked = %s
 WHERE id = %d",

This is the SQL query executed by the function:
- It performs an UPDATE operation on the table stored in $this->table_name.
- Columns:
  - click_count = click_count + 1: Increments the click_count value for the specific row.
  - ip_address, user_agent, referer_data, and last_clicked: Updates these columns with values provided in the $data array.
- WHERE id = %d: Identifies the row to update using the provided id.

Although the query updates several fields, for this discussion, let’s focus solely on the part that increments the click_count column:

"SET click_count = click_count + 1"

Breaking It Down: Incrementing the Counter

The operation SET click_count = click_count + 1 is the core of this query and is handled directly by the database. Here’s why this approach is the “right way” to handle such an operation:

Atomic Operation:
- The database ensures the increment operation (click_count + 1) is handled as a single, indivisible action.
- Even if multiple users trigger this query simultaneously, the database will ensure the counter is incremented correctly without any conflicts or race conditions.
Efficiency:
- Instead of fetching the current value of click_count into your application, modifying it, and writing it back, the database performs the calculation internally. This eliminates unnecessary data transfer and processing on the application side.
Concurrency-Safe:
- In high-traffic systems, there’s a risk of two queries reading the same value of click_count and then overwriting each other’s updates. By letting the database handle the increment directly, this problem is avoided.
Simplicity:
- The entire operation is done in a single line of SQL, making the code cleaner and easier to understand without sacrificing performance or reliability.

Why It Matters

Inserting or updating counters is a common pattern in applications that track user actions, such as link clicks, page views, or other events. While there are alternative ways to achieve this, relying on database atomic operations ensures the process is reliable, even as the application scales to handle more users.

Tracking counters is a common pattern in many applications. For example:

Recording how many times a link is clicked.
Tracking page views or impressions.
Counting login attempts, likes, or other interactions.

While there are alternatives, relying on atomic operations in SQL ensures that these counters are updated in a way that is both reliable and scalable as your application grows.

By using SET column = column + 1, we avoid potential nightmares like:

Incorrect counter values: caused by simultaneous queries modifying the same data.
Increased overhead: from repeated application-level logic that is better handled by the database.
Data corruption issues: typically caused by race conditions.

Conclusion: Let the Database Do the Heavy Lifting

When building applications where counters are frequently updated, it’s essential to design for both data accuracy and scalability. Letting the database handle these operations through atomic updates like SET click_count = click_count + 1 ensures that your counters will always be correct, no matter how many users your application serves.

In the example code, this principle is used to great effect to keep the counter safe and reliable. So the next time you’re tasked with tracking clicks, views, or other interactions, remember to put the database to work for you—it’s designed to handle tasks like this far better than the application layer.

All my plugin code here: https://github.com/josanua/WP-Link-Shortener