In a micro-services architecture paradigm, we have an exchange of messages between two services, each one writing the results into their respective database.

In a more descriptive manner, the communication is as follows:

Service A stores a message in its PostgreSQL database, communicates via gRPC with Service B, which in turn performs a number of operations, stores the result in its own PostgreSQL database, and responds back to Service A with the result. Upon receiving the result Service A updates its database with said result and uses that result to proceed with its remaining work. Once this work is done, we report the response back to the user.

The queries to PostgreSQL are being done by using JOOQ (this will be important later). The entire process is also supported by Spring Transactions. Each database operation starts its own transaction (keep that in mind). Additionally, Service A stores an array in one of the columns in the database (also keep this in mind).

So when during a routine check we've identified some data missing while the system reported processes to be completed successfully, we were understandably baffled and quite concerned.

Glossary

The issue

After some digging through the logs, we've realised, the following scenario had happened:

The questions

At the time of the issue we had three "unknowns" to identify:

  1. Why did the actual store operation fail?
  2. Why, while the store failed, spring didn't roll back, or propagated the exception?
  3. How did the process in service A manage to find a response to continue the operations?

Question 2 was easy to answer.

Short answer to question 2: There was no exception after the failure of the store operation, so  spring did not propagate anything.

However, when we checked the logs we realised that the insert had actually worked fine on the server side. So, while the store was successful, something caused it to roll back.

Finding out what happened

To answer questions 1 and 3, we had to combine a number of different events.

Our monitoring tool captured a query timeout, on a select operation, that was a good starting point, but we had to identify what caused this timeout, which at the time we assumed it was the cause of the issue.

The AHA! moment

To understand the issue you need to know that JOOQ provides a convenient clause called returning(). This clause will return a full Record corresponding to the table that the query is performed on.

So, if you have the following query type:

ctx
  .insertInto(TABLE)
  .set(records)
  .onConflict(PRIMARY_KEY)
  .doNothing()
  .returning()
  .fetchSingle()

This will return a valid record in the fetchSingle() operation. However, the array initialisation during the returning() clause can also "swallow" exceptions via the use of its default bindings, when materialising the Record.

Simplified the internal pattern of the default bindings for the array is as follows:

// Simplified look at the internal pattern in DefaultBinding.java
try {
    return pgFromString(ctx, string);
}
catch (Exception e) {
    log.error("Cannot parse array : " + string, e);
    return (T[]) Array.newInstance(type.getComponentType(), 0); // Returns an empty default array
}

Which means that even in case of an exception during the array initialisation, jooq will return a valid Record to the application layer (this won't be the correct result of the query though). Knowing this, and combining it with the fact that:

We could piece together the scenario that triggered this edge case.

What actually happened

The main trigger of this rare edge case was the new transaction. In a new spring transaction, Spring borrows a connection from the connection pool (usually HikariCP), and creates a fresh transaction boundary. So the chain of events is as follows:

How to fix this

There are a number of ways that can be applied to avoid the issue described above.

Conclusion

This incident highlights a subtle and dangerous edge case at the intersection of application frameworks, ORM defaults, and database connection handling.

Key Takeaways:

By implementing schema metadata pre-warming during boot and enforcing strict non-null checks in our repository mappers, we eliminate the silent failure mode ensuring that if an operation fails at the database level, it halts execution immediately and surfaces a true rollback to the application.