Introduction

Incrementing a database field by 1 looks trivial, but the safe solution is not "read the value in code, add one, then write it back." In concurrent systems, that pattern loses updates. The correct approach is to let the database perform the increment atomically in a single update statement.

The Core SQL Pattern

In SQL, the normal pattern is:

UPDATE counters
SET value = value + 1
WHERE id = 42;

This is the important idea: the database updates the column based on its current stored value. That makes the operation atomic at the row-update level under normal transactional behavior.

For counters, scores, view counts, or retry attempts, this is almost always the right baseline.

Why Read-Modify-Write Is Dangerous

This pattern is fragile:

current = get_value_from_db()
new_value = current + 1
save_value_to_db(new_value)

If two requests do that at the same time, both may read the same old value and both may write the same incremented value. One increment is lost.

That race condition is exactly why the increment should happen inside the database engine.

SQL Example With a Real Table

1CREATE TABLE article_stats (
2    article_id INTEGER PRIMARY KEY,
3    view_count INTEGER NOT NULL DEFAULT 0
4);
5
6UPDATE article_stats
7SET view_count = view_count + 1
8WHERE article_id = 1001;

This is portable across major relational databases with only minor syntax differences elsewhere in the surrounding query structure.

Returning the New Value

Sometimes you need the incremented value immediately. Some databases support returning it directly.

PostgreSQL example:

1UPDATE article_stats
2SET view_count = view_count + 1
3WHERE article_id = 1001
4RETURNING view_count;

This avoids doing a second query just to see the updated number.

ORM Examples

Many ORMs expose a field-expression mechanism so the increment still happens in SQL rather than in application memory.

Django:

1from django.db.models import F
2from myapp.models import ArticleStat
3
4ArticleStat.objects.filter(article_id=1001).update(
5    view_count=F("view_count") + 1
6)

SQLAlchemy:

1from sqlalchemy import update
2
3stmt = (
4    update(article_stats)
5    .where(article_stats.c.article_id == 1001)
6    .values(view_count=article_stats.c.view_count + 1)
7)
8session.execute(stmt)
9session.commit()

The important property is the same: the increment happens in the database, not in Python.

What About NULL Values

If the field can be NULL, value = value + 1 may stay NULL depending on the database rules. For counters, the better design is usually:

• make the column NOT NULL
• give it a default such as 0

If you must handle nullable fields, use a null-safe expression:

UPDATE counters
SET value = COALESCE(value, 0) + 1
WHERE id = 42;

That keeps the counter usable even if old rows contain nulls.

Transactions and Related Updates

If incrementing one field is part of a larger business action, wrap the related updates in a transaction. The increment statement itself is atomic, but your whole workflow may still need transactional boundaries.

Example:

1BEGIN;
2
3UPDATE inventory
4SET reserved = reserved + 1
5WHERE item_id = 10;
6
7UPDATE orders
8SET status = 'reserved'
9WHERE id = 77;
10
11COMMIT;

The key distinction is:

• one SQL increment protects the field update
• a transaction protects the larger unit of work

Performance Notes

Database-side increments are usually efficient, but hot counters can still become contention points under very high write volume. If a single row is updated constantly, you may need a higher-level scaling strategy such as sharded counters or event aggregation.

That is a scaling concern, not a reason to fall back to unsafe application-side math.

Common Pitfalls

• Reading the value into application code and writing it back after adding one.
• Forgetting to handle NULL in fields that were not designed as proper counters.
• Assuming an ORM increment is safe when it actually reads and writes in two steps.
• Ignoring transactions when the increment is part of a larger state change.
• Treating lock contention and lost updates as the same problem.

Summary

• The safe way to increment a database field is SET field = field + 1 in SQL.
• Let the database perform the increment atomically.
• Use ORM field expressions such as Django F() or SQLAlchemy column expressions when working above raw SQL.
• Prefer NOT NULL counters with a default of 0.
• Use transactions when the increment is part of a bigger multi-step update.