Saving to Reactive Redis and Postgres in a single transaction

Question

I would like the saving to Postgres and Redis to be executed within a transaction, meaning if either of the saves encounters an error, the other save should be rolled back. How can this be achieved? I would like the saveLocationToDbAndRedis() method to occur within a single transaction.

private final ReactiveRedisOperations<String, RedisLocation> locationOps;

....

@Transactional
Mono<Location> saveLocationToPostgres(Location location) {
    return ioMono(() -> locationRepository.save(location));
}

@Transactional
public Mono<Location> saveLocationToDbAndRedis(Location location) {
    return saveLocationToPostgres(location)
            .zipWith(redisLocationHashOperations.saveAndUpdateLocationInRedis(location))
            .flatMap(locationBooleanTuple -> Mono.just(locationBooleanTuple.getT1()));
}

   
public Mono<Boolean> saveAndUpdateLocationInRedis(Location location) {
        return locationOps.opsForHash().put(
                REDIS_HASH_LOCATION_KEY,
                location.getId(),
                toRedisLocation(location)
        );
    }

Answer 1

Achieving this while using JPA under the hood is not trivial as you need fine control over the transaction management and at the same time you use @Transactional that manages the transaction automatically.

I recommend using plain JdbcTemplate (or new JdbcClient) and RedisTemplate.

You want to do the following in a single thread.

1. Start a transaction in the PostgreSQL.
2. Execute the SQL query to the PostgreSQL, do not commit anything!
3. Now decide: Is the PostgreSQL save successful?
   • If yes, perform a Redis save operation and commit/rollback the PostgreSQL transaction based on its result.
   • If not, roll back the PostgreSQL transaction.

I don't know whether you insert or update a record in the PostgreSQL database. In case of an UPDATE, you might want to ensure no other node updates the same row as another node, so I recommend locking the record using SELECT FOR UPDATE.