Spring Data JPA - flush (SQL Error 1062, SQLState 23000)

This post was migrated from Tistory. You can find the original here.

Example)

Vehicle table

id	bigint	Primary key (Vehicle ID)
license_plate	varchar(255)	Vehicle’s license plate number
brand	varchar(255)	Manufacturer of the vehicle
model	varchar(255)	Vehicle model
year	int	Year of manufacture

Engine table

id	bigint	Primary key (Engine ID)
engine_number	varchar(255)	Engine serial number
engine_model	varchar(255)	Engine model
status	enum(‘error’, ‘running’, ‘stopped’)	Current engine status
vehicle_id	bigint	Foreign key referencing Vehicle ID

Engine and Vehicle have a one-to-one relationship, and Engine owns the foreign key.

Say the Engine table has the following data.

id	engine_number	engine_model	status	vehicle_id
1	xxxx-xxxx	AQ	3	1
2	xxxx-xxxx	DW	2	2

Now, suppose we want to change Engine #1 (id 1) so its vehicle becomes Vehicle #2 (id 2).

Since Engine and Vehicle have a one-to-one relationship, the same vehicle_id can’t exist on more than one row.

So the order of operations needs to be:

1. Null out vehicle_id on Engine #2, which currently holds the relationship with Vehicle #2 (breaking the existing relationship first)

2. Update Engine #1’s vehicle_id to 2

That’s the plan.

Spring Data JPA - flush

The Problem

    @Transactional
    public void patchEngine(PatchVehicleDto dto) {
        Engine engine = engineRepository.findById(dto.getEngineId()).orElseThrow(() -> new IllegalArgumentException("Invalid engineId"));

        Vehicle vehicle = vehicleRepository.findById(dto.getVehicleId()).orElseThrow(() -> new IllegalArgumentException("Invalid vehicleId"));
        engineRepository.findByVehicle(vehicle).ifPresent((v) -> {
            v.setVehicle(null);
            engineRepository.save(v);
        });
        engine.setVehicle(vehicle);
        engineRepository.save(engine);
    }

Implementing the flow above with JPA produces this error: SQL Error: 1062, SQLState: 23000, Duplicate entry ‘2’ for key ~~.

The Fix

    @Transactional
    public void patchEngine(PatchVehicleDto dto) {
        Engine engine = engineRepository.findById(dto.getEngineId()).orElseThrow(() -> new IllegalArgumentException("Invalid engineId"));

        Vehicle vehicle = vehicleRepository.findById(dto.getVehicleId()).orElseThrow(() -> new IllegalArgumentException("Invalid vehicleId"));
        engineRepository.findByVehicle(vehicle).ifPresent((v) -> {
            v.setVehicle(null);
            engineRepository.saveAndFlush(v); // first update
        });
        engine.setVehicle(vehicle);
        engineRepository.save(engine); // second update
    }

Using saveAndFlush (or flush) like this makes it work as intended, without the error.

repository.flush() synchronizes the persistence context’s state with the DB, but nothing is finalized until the transaction actually ends.

In other words, it does not issue a COMMIT.

Investigating the question

Here’s how I approached the problem above.

Either the first update query wasn’t being sent at all,

or the first update query was sent but hadn’t been reflected in the DB yet when the second query went out.

    @Transactional
    public void test() {
        Engine engine = new Engine();
        Vehicle vehicle = new Vehicle();
        engine.setVehicle(vehicle);
        vehicleRepository.save(vehicle);  // #1
        engineRepository.save(engine);  // #2
    }

For example, take code like this.

If it runs in order #1 then #2, you get 2 inserts.

If it runs in order #2 then #1, you get 2 inserts and 1 update.

Either way, the vehicle insert query has to go out before the COMMIT can succeed.

Likewise, in the code from the problem above, the engine update query has to go out first for the COMMIT to succeed,

yet insert works fine without a flush, while update requires one.

What’s the difference?

Is it that updates on the same table simply don’t get applied without a flush?

References

2023.07.13 - [Spring/JPA] - Spring) JPA persistence / transactions / entity state (23-07-08)

I wrote this earlier while studying JPA persistence,

and it turns out that keeping good notes on the blog really does pay off in all sorts of ways.