MySQL UPSERT vs SELECT–UPDATE–INSERT

Race Conditions, Transactions, and updatedAt Gotchas (MySQL 5.7 & 8.0)

Background

Modern SQL has introduced cleaner and safer patterns to handle common database operations. UPSERT is one of them, allowing inserts and updates to be performed atomically in a single statement.

This article explains why UPSERT exists, what problems it solves, and what trade-offs and side effects developers should understand when using it.

Executive Summary

When inserting or updating records in MySQL, developers often choose between:

1. UPSERT

INSERT ... ON DUPLICATE KEY UPDATE

2. Manual logic

SELECT → UPDATE or INSERT

Although both appear to achieve the same goal, they differ dramatically in correctness, concurrency safety, and side effects.

This article explains:

• why race conditions occur
• why BEGIN/COMMIT alone does not fix them
• why UPSERT is concurrency-safe
• why updatedAt changes even when data doesn’t
• what MySQL 8 row alias does and does not solve

1. The Core Problem: Race Conditions

The classic pattern (unsafe)

SELECT id FROM users WHERE id = 1;

-- if no row
INSERT INTO users (id, name) VALUES (1, 'Alice');

What goes wrong

With two connections running concurrently:

This happens because:

• The decision is made outside the database
• Another connection can change data between statements

This is a race condition.

2. Why BEGIN / COMMIT Alone Does NOT Fix It

A common misconception:

“If I wrap it in a transaction, it should be safe.”

Reality

BEGIN;
SELECT ...
INSERT ...
COMMIT;

Transactions:

• guarantee atomicity within one connection
• do not block other connections by default

If no locks are taken:

• other connections can still read/write the same rows
• the race condition remains

Key takeaway

BEGIN/COMMIT ≠ concurrency safety Locks (or single-statement writes) are required

3. Why UPSERT Is Safe (Even Across Connections)

Example

INSERT INTO users (id, name)
VALUES (1, 'Alice')
ON DUPLICATE KEY UPDATE
  name = VALUES(name);

Two connections, same time

• Both attempt INSERT
• InnoDB locks the unique index
• One INSERT succeeds
• The other automatically runs UPDATE

This is:

• atomic
• race-free
• enforced at the index level

Important distinction

UPSERT is one statement, so:

• no “decision window”
• no application-level guessing
• MySQL resolves the conflict internally

4. Lost Updates vs Last-Write-Wins

Safe example (no lost update)

INSERT INTO counters (id, val)
VALUES (1, 1)
ON DUPLICATE KEY UPDATE
  val = val + 1;

Two concurrent executions result in:

val = 2 ✅

InnoDB serializes the updates using row locks.

Last-write-wins (expected behavior)

UPSERT col = 'A'
UPSERT col = 'B'

Final value depends on commit order.

This is not a race condition — it’s normal database behavior.

5. The updatedAt Surprise

The problem

If you have:

updatedAt TIMESTAMP
  DEFAULT CURRENT_TIMESTAMP
  ON UPDATE CURRENT_TIMESTAMP

Then any UPDATE will refresh updatedAt.

Even when data is identical

INSERT INTO my_table (id, col1)
VALUES (1, 'A')
ON DUPLICATE KEY UPDATE
  col1 = col1;

Result:

• col1 unchanged
• updatedAt still updated

Why?

• MySQL chooses the UPDATE path first
• triggers and auto-update columns fire
• MySQL does not optimize this away

6. Does MySQL 8 Row Alias Fix This?

MySQL 8 syntax

INSERT INTO my_table AS t (id, col1)
VALUES (?, ?)
ON DUPLICATE KEY UPDATE
  col1 = IF(t.col1 IS DISTINCT FROM new.col1, new.col1, t.col1);

What it improves

• VALUES() is deprecated → alias is cleaner
• Better readability
• Proper NULL-safe comparison (IS DISTINCT FROM)
• Avoids unnecessary data changes

What it does NOT fix

• UPDATE still runs
• UPDATE triggers still fire
• updatedAt still changes
• binlog still records UPDATE

Row alias improves correctness of assignments, not UPDATE semantics

7. How to Prevent False updatedAt Updates

Control updatedAt manually

Remove auto-update behavior and update conditionally through code or SQL:

updatedAt =
  IF(
    t.col1 IS DISTINCT FROM new.col1,
    NOW(),
    t.updatedAt
  );

This works for:

• UPSERT
• UPDATE
• future queries

8. Are Concurrent UPSERTs a Race Condition?

No.

Even with:

• two different connections
• running at almost the same time

UPSERT is safe because:

• unique index locks serialize access
• InnoDB guarantees correctness

What can happen:

• triggers fire multiple times
• timestamps update multiple times
• last-write-wins behavior

But:

• no duplicates
• no lost updates
• no corrupted data

9. Practical Guidelines

Use UPSERT when:

• concurrency matters
• logic is simple
• performance matters
• batch imports or sync jobs

Avoid relying on:

• ON UPDATE CURRENT_TIMESTAMP
• SELECT → INSERT without locks
• application-level existence checks

Remember this rule

Single-statement writes are safer than multi-step logic.

Final Takeaway

• Race conditions come from decision gaps
• Transactions alone do not fix concurrency
• UPSERT is safe because MySQL resolves conflicts internally
• MySQL 8 row alias improves clarity, not behavior
• updatedAt must be handled explicitly with UPSERT

If you follow these principles, your MySQL writes will be:

• faster
• safer
• and far more predictable