Why Your JPA Counter Column Keeps Resetting to Zero

This article was originally published on Jo4 Blog.

I'll cut to the chase: if you have a counter column, a @Modifying @Query that increments it, and a @Version field on the same entity, you almost certainly have a silent data-loss bug.

We had this exact bug. It cost us about two weeks of "huh, that referrer's click count looks low" before we figured out what was happening. Here's what we found and how we fixed it.

The Setup

We track referrer attribution. Every time someone clicks a referral short link, we increment a denormalized total_clicks counter on the referrer row. Doing this with a load-modify-save round-trip would be slow under fan-out, so we use a @Modifying @Query that increments at the SQL level:

@Modifying(clearAutomatically = true)
@Query("UPDATE ReferrerEntity r "
     + "SET r.totalClicks = COALESCE(r.totalClicks, 0) + 1, "
     + "    r.modifiedTime = :now "
     + "WHERE r.id = :referrerId AND r.deleted = false")
int incrementClickCount(@Param("referrerId") Long referrerId,
                        @Param("now") Long now);

Looks fine. Atomic at the DB level. Counts always go up. Until you also have this on the entity:

@Version
private Long version;

@Column(name = "total_clicks")
@Builder.Default
private Long totalClicks = 0L;

And somewhere else in the code path, an unrelated service does this:

ReferrerEntity referrer = referrerRepository.findById(id).orElseThrow();
referrer.setPayoutEmail(newEmail);
referrerRepository.save(referrer);  // <-- here be dragons

The Problem

Watch the timeline:

What does step 4 do? It writes back the entity Hibernate has in memory: totalClicks=42, version=7, payoutEmail=newEmail.

The @Version check passes — version is still 7, because our @Modifying query never bumped it. So Hibernate happily issues:

UPDATE referrer SET total_clicks = 42, payout_email = '...', version = 8 WHERE id = ? AND version = 7

Two clicks, gone. Silently. The version mechanism that's supposed to prevent exactly this lost-update did not prevent it, because the writes that bumped the counter never bumped the version.

The bug compounds under load. The hotter the counter, the more clicks get clobbered. Slow weeks the counter looks fine; viral campaigns it's wildly under-counted. Fun debugging.

The Two-Part Fix

Part 1: Mark counter columns updatable = false

The fundamental issue is that Hibernate's save() writes every column it tracks, including ones it has no business writing. A counter that's only ever incremented by @Modifying @Query is one of those columns. Tell Hibernate to leave it alone:

@Column(name = "total_clicks", updatable = false)
@Builder.Default
private Long totalClicks = 0L;

@Column(name = "total_conversions", updatable = false)
@Builder.Default
private Long totalConversions = 0L;

updatable = false tells Hibernate: this column is read-only from the entity's perspective. Don't include it in UPDATE statements. The only path that writes to it is the explicit SQL update. save() calls now leave the counter alone, no matter what value the in-memory entity has.

This is the rule we now apply across the codebase. Anywhere a column is mutated exclusively via @Modifying @Query — counters, denormalized stats, queue depths — that column gets updatable = false. Without exception.

Part 2: Bump the version inside the @Modifying @Query

updatable = false plugs the lost-update hole, but a different hole opens: now save() doesn't know the row changed, so optimistic-concurrency races against other fields silently overwrite each other.

Concretely: thread A bumps the counter via @Modifying. Thread B loads the entity (with old payoutEmail), updates it, saves. Without a version bump, B's save succeeds — but B was operating on a row that A had already mutated. If you care about A's mutation being a "real" change (and you should, for audit purposes), B should have hit OptimisticLockException and retried.

The fix is one extra clause in the SET:

@Modifying(clearAutomatically = true)
@Query("UPDATE ReferrerEntity r "
     + "SET r.totalClicks = COALESCE(r.totalClicks, 0) + 1, "
     + "    r.modifiedTime = :now, "
     + "    r.version = COALESCE(r.version, 0) + 1 "  // <-- this line
     + "WHERE r.id = :referrerId AND r.deleted = false")
int incrementClickCount(@Param("referrerId") Long referrerId,
                        @Param("now") Long now);

Apply this to every @Modifying @Query UPDATE on every entity that has a @Version field. We did the audit and bumped versions in around fifteen queries across ReferrerRepository, TeamRepository, UrlVariantRepository, and others. Easy fix, big footprint.

The Test That Would Have Caught It

We added a guard test that walks repository methods reflectively, finds every @Modifying @Query UPDATE, checks the JPQL string for version = in the SET clause, and fails if any are missing:

@Test
void everyModifyingQueryBumpsVersionWhenEntityHasVersion() {
    List<Method> queries = findAllModifyingQueriesOn(ReferrerRepository.class);
    for (Method m : queries) {
        Query q = m.getAnnotation(Query.class);
        String jpql = q.value();
        if (!isUpdate(jpql)) continue;
        if (entityHasVersion(targetEntity(m))) {
            assertTrue(
                jpql.replaceAll("\\s+", " ").contains("version = COALESCE("),
                m.getName() + " is missing version bump"
            );
        }
    }
}

This is the kind of test that will save you the next time someone adds a @Modifying @Query six months from now and forgets the version bump.

Lessons Learned

• save() writes every column it tracks, including ones you wish it wouldn't. If a column is only mutated by direct SQL, mark it updatable = false to keep save() from silently overwriting it.
• @Version only protects what the JPA layer knows changed. A @Modifying @Query that doesn't bump the version is invisible to the optimistic-lock check. Subsequent save() calls will overwrite and the version check will pass.
• Add r.version = COALESCE(r.version, 0) + 1 to every @Modifying @Query UPDATE on a versioned entity. No exceptions.
• Reflection-based guard tests are cheap insurance. A single test that walks repository methods catches the next regression before it merges. Worth the 100 lines.
• Counter columns are a DSL of their own. They have one valid mutation pattern (atomic SQL increment) and one valid read pattern (load and trust). Anything else is a bug waiting for traffic.

Have you been bitten by JPA's "helpful" save behavior? What was the symptom that led you to it? Drop the war story in the comments.

Building jo4.io — a URL shortener whose click counters add up the same way every time.