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); // 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 itupdatable = false to keepsave() 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. Subsequentsave() 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. Top comments (0)