Designing fault-tolerant and idempotent APIs with HTTP requests mapped to database's transactions at 1:1 model

CreateNewUserController.java

/**
 * This Spring Boot controller was implemented as an example of a simple but robust idempotent REST API that
 * leverages the ACID properties of a relational database.
 */
@RestController
public class CreateNewUserController {

    @Autowired
    private UserRepository repository;
    @Autowired
    private EventsRepository eventsRepository; // a table that works as a queue for background jobs

    @Transactional(
        // we can use a strictier isolation level to prevent race conditions
        isolation = Isolation.SERIALIZABLE 
    )
    @PostMapping("/api/users")
    public ResponseEntity<?> create(@RequestBody @Valid NewUserRequest request) {
        
        // if user already exists, then we simplify the client-side returning status 200-OK
        if (repository.existsByEmail(request.getEmail())) {
            return ResponseEntity.ok("User exists"); // http 200
        });

        // create the new user
        User user = request.toModel();
        repository.save(user); // don't worry, we have an UNIQUE constraint here
      
        // enqueue a job to tell an external service that a new user's been created
        eventsRepository.save(
            new Event("user_created:send_welcome_email", Map.of("email", user.getEmail()))
        );

        // pass back a successful response
        URI location = ...;
        return ResponseEntity
                .created(location).build(); // http 201
    }
    
    
    /**
     * Controller Advice for the UNIQUE constraint error
     */
    @ExceptionHandler(ConstraintViolationException.class) // org.hibernate.exception.ConstraintViolationException
    public ResponseEntity<?> handleUniqueConstraintErrors(ConstraintViolationException e, WebRequest request) {

        Map<String, Object> body = Map.of(
                "timestamp", LocalDateTime.now(),
                "message", "User exists"
        );

        return ResponseEntity.ok(body); // http 200
    }
    
    /**
     * Controller Advice for Serializable isolation level errors
     */
    @ExceptionHandler({
        CannotAcquireLockException.class, // org.springframework.dao.CannotAcquireLockException
        LockAcquisitionException.class, // org.hibernate.exception.LockAcquisitionException (nested exception)
    })
    public ResponseEntity<?> handleSerializableErrors(RuntimeException e, WebRequest request) {

        Map<String, Object> body = Map.of(
                "timestamp", LocalDateTime.now(),
                "message", "Sorry, another request is being processed at this moment"
        );

        return ResponseEntity
                .status(HttpStatus.CONFLICT).body(body); // http 409
    }
    
}

Some fantastic articles on how to design and implement fault-tolerant idempotent APIs leveraging a relational database:

• Building Robust Systems with ACID and Constraints
• Using Atomic Transactions to Power an Idempotent API
• Implementing Stripe-like Idempotency Keys in Postgres
  • api.rb: The Ruby code of a fault-tolerant and idempotent API implemented in the article
  • Procfile: Background jobs needed to help the API with fault-tolerance implemented in the article
• Transactionally Staged Job Drains in Postgres

• My tweet on designing and implementing idempotent APIs (pt_BR)

• Making retries safe with idempotent APIs

An important consideration is that the process that combines recording the idempotent token and all mutating operations related to servicing the request must meet the properties for an atomic, consistent, isolated, and durable (ACID) operation. An ACID server-side operation needs to be an “all or nothing” or atomic process. This ensures that we avoid situations where we could potentially record the idempotent token and fail to create some resources or, alternatively, create the resources and fail to record the idempotent token.

Tweet by Raul Junco - "Idempotency is underrated and under-implemented."

Video: Reliable Messaging Without Distributed Transactions

Udi Dahan explains how you can still do reliable messaging even if you can't use (or don't want) distributed transactions (DTC).

Example of Spring Boot filter (OncePerRequestFilter) that implements idempotence with Redis:

• Spring Boot 3: build the efficient Idempotent API by Redis
  • Github: IdempotenceFilter.java

Although this article is about using idempotence in background jobs, the concept of re-entrancy is inherent in the Recoverability property:

• JobRunr's blog: Why is Idempotence Important in Java Job Scheduling?

Re-entrancy: A Companion to Idempotence

While idempotence ensures that a job produces the same result no matter how many times it is executed, re-entrancy ensures that the job can safely resume or restart after an interruption. Re-entrant jobs can handle retries, crashes, or system restarts without causing data corruption or inconsistency. Together, idempotence and re-entrancy form the foundation for reliable and fault-tolerant background jobs, as they address both correctness and resilience in the face of failures.

River's blog: Building an idempotent email API with River unique jobs