Follow these 10 "foolproof" steps to guarantee your database never suffers a duplicate row again. Trust me, these are enterprise-grade, battle-tested strategies that will definitely not backfire.
1. Skip the Unique Constraint, Trust the Service Layer
@Entity
public class Driver {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
// No unique constraint here, the service layer checks for duplicates... probably
private String licenseNumber;
}
Why This Works: The database is just storage. Real validation happens in your service layer, the one class everyone swears is correct.
Reality Check: Two requests land within the same few milliseconds, both check "does this license number exist," and both hear "no." Now you have two drivers sharing a license number, and someone from the data team is asking pointed questions in standup.
2. @Transactional Is Just a Suggestion
public void createBooking(BookingRequest request) {
Booking existing = bookingRepository.findByReferenceId(request.getReferenceId());
if (existing == null) {
bookingRepository.save(new Booking(request));
}
// @Transactional felt like overkill for something this "simple"
}
Why This Works: Adding @Transactional means admitting your code might run more than once at the same time. Denial is simpler.
Reality Check: Without a transaction boundary, two threads both find "no existing booking" and both save one. Now finance is reconciling a customer who got charged twice for a trip he booked once, and "it worked on my machine" will not save you here.
3. Check Then Insert, the Timeless Classic
if (userRepository.findByEmail(email).isEmpty()) {
userRepository.save(new User(email));
}
// Checked first, so this is basically thread safe
Why This Works: You checked first. That basically counts as thread safety.
Reality Check: This is the textbook check-then-act race condition, alive and well in production. Postgres does not care that you checked, it only cares about the order the inserts actually arrive in, and it will happily accept every one of them.
4. INSERT and Pray, Who Needs ON CONFLICT
@Modifying
@Query(value = "INSERT INTO trip_logs (trip_id, driver_id, started_at) " +
"VALUES (:tripId, :driverId, :startedAt)", nativeQuery = true)
void logTripStart(@Param("tripId") Long tripId,
@Param("driverId") Long driverId,
@Param("startedAt") Instant startedAt);
// Who needs ON CONFLICT DO NOTHING when you have optimism
Why This Works: Postgres gave you a perfectly good upsert clause, and you decided a plain INSERT builds more character.
Reality Check: Every retried request becomes a brand-new row instead of a no-op. Your trip_logs table grows like it gets paid per row, and eventually someone asks why a single trip has fourteen identical log entries.
5. Retry the Request, Never Ask Why It Failed
@Retryable(maxAttempts = 5)
public void submitExpenseReport(ExpenseDto dto) {
expenseRepository.save(mapToEntity(dto));
}
// No idempotency key, but five retries really shows we care
Why This Works: Retries mean resilience, resilience is good, so more retries must be more good.
Reality Check: Nobody added an idempotency key, so one flaky network blip turns into five identical expense reports for the same trip. One employee gets reimbursed five times, finance loves you for about an hour, and then finance never loves you again.
6. Cascade Everything, Question Nothing
@OneToMany(mappedBy = "invoice", cascade = CascadeType.ALL)
private List<LineItem> lineItems = new ArrayList<>();
// orphanRemoval? We like to keep our options open
Why This Works: CascadeType.ALL sounds thorough, and thorough sounds like good engineering.
Reality Check: Load an invoice, let a mapper rebuild the line item list without preserving the original IDs, then call save(). Hibernate does not recognize the "new" line items, so it inserts them fresh right next to the ones already sitting in the table. Every invoice update now doubles as a line item breeding program.
7. equals() and hashCode(), Strictly Optional
public class Vehicle {
private Long id;
private String plateNumber;
// equals() and hashCode() felt unnecessary, the IDE strongly disagreed
}
Why This Works: Object identity is a philosophical question best left to Java's default implementation.
Reality Check: Your Set is now a List wearing a disguise. Every "add if not already present" check quietly does nothing useful, and the same vehicle gets added to the fleet three times before lunch.
8. Skip @version, Optimism Is Free
@Entity
public class Wallet {
@Id
private Long id;
private BigDecimal balance;
// @Version is for people who expect two requests to arrive at once
}
Why This Works: Version columns are for pessimists who expect two people to update the same row at the same time.
Reality Check: Two requests read the same wallet balance, both add their own credit, and both write back. One update quietly disappears, and support spends the afternoon explaining to a very confused customer why their refund never actually showed up.
9. Trust Kafka to Deliver Exactly Once (It Will Not)
@KafkaListener(topics = "trip-completed")
public void handleTripCompleted(TripEvent event) {
tripRepository.save(mapToTrip(event));
}
// At least once delivery, so it obviously only arrives once
Why This Works: At-least-once delivery sounds generous, so surely it is generous enough to only deliver once.
Reality Check: That means at least once, not exactly once. Skip the dedupe check on event ID, and a single redelivered message duplicates the trip record. The dashboard now shows the fleet completing twice the trips it actually drove, and nobody in the Monday review can explain the sudden productivity miracle.
10. Run That Migration Again, For Good Measure
<changeSet id="backfill-missing-drivers" author="you" runOnChange="true">
<sql>
INSERT INTO drivers (name, license_number)
SELECT name, license_number FROM staging_drivers;
</sql>
<!-- runOnChange felt safer than runOnce, more flexible that way -->
</changeSet>
Why This Works: Idempotency is a big word for a small changeset that probably only needs to run once.
Reality Check: Someone tweaks a comment in the changeset during a hotfix, Liquibase sees a checksum mismatch, and runOnChange happily replays the whole insert. Now you get to spend your afternoon writing a cleanup script to deduplicate the exact table you were trying to fix in the first place.
Conclusion: Welcome to Duplicate Row Hell
Congratulations. By faithfully following these ten steps, you have turned a clean relational database into a table full of echoes. Every driver has a twin, every invoice has a shadow, and the analytics dashboard reports numbers that make sense to absolutely nobody, including the person who built it.
But look at the bright side. A database full of duplicates is never boring. Nobody trusts a single row anymore, and that keeps the whole team refreshingly humble.
Pro Tip: If you actually want your data to behave, add unique constraints where uniqueness matters, wrap concurrent writes in real transactions, reach for ON CONFLICT instead of hoping for the best, attach idempotency keys to anything that can be retried, override equals and hashCode properly, and let @version handle the locking so you do not have to guess who won the race. Postgres already built the guardrails. Use them.
Duplicate data is never a one-time bug you fix and forget. It is a decision you make quietly, every single time you skip one of these.
May your constraints be unique, and your inserts land exactly once.