title: Synchronising transactions between database and Kafka producer
body: <p>We have a micro-services architecture, with Kafka used as the communication mechanism between the services.  Some of the services have their own databases.  Say the user makes a call to Service A, which should result in a record (or set of records) being created in that service’s database.  Additionally, this event should be reported to other services, as an item on a Kafka topic.  What is the best way of ensuring that the database record(s) are only written if the Kafka topic is successfully updated (essentially creating a distributed transaction around the database update and the Kafka update)?</p>
  
  <p>We are thinking of using <a href="https://spring.io/projects/spring-kafka" rel="noreferrer">spring-kafka</a> (in a Spring Boot WebFlux service), and I can see that it has a <a href="https://docs.spring.io/spring-kafka/api/org/springframework/kafka/transaction/KafkaTransactionManager.html" rel="noreferrer">KafkaTransactionManager</a>, but from what I understand this is more about Kafka transactions themselves (ensuring consistency across the Kafka producers and consumers), rather than synchronising transactions across two systems (see <a href="https://stackoverflow.com/questions/47354521/transaction-synchronization-in-spring-kafka">here</a>: “Kafka doesn't support XA and you have to deal with the possibility that the DB tx might commit while the Kafka tx rolls back.”).  Additionally, I think this class relies on Spring’s transaction framework which, at least as far as I currently understand, is thread-bound, and won’t work if using a reactive approach (e.g. WebFlux) where different parts of an operation may execute on different threads.  (We are using <a href="https://github.com/reactiverse/reactive-pg-client" rel="noreferrer">reactive-pg-client</a>, so are manually handling transactions, rather than using Spring’s framework.)</p>
  
  <p>Some options I can think of:</p>
  
  <ol>
  <li>Don’t write the data to the database: only write it to Kafka.  Then use a consumer (in Service A) to update the database.  This seems like it might not be the most efficient, and will have problems in that the service which the user called cannot immediately see the database changes it should have just created.</li>
  <li>Don’t write directly to Kafka: write to the database only, and use something like <a href="https://debezium.io/" rel="noreferrer">Debezium</a> to report the change to Kafka.  The problem here is that the changes are based on individual database records, whereas the business significant event to store in Kafka might involve a combination of data from multiple tables.</li>
  <li>Write to the database first (if that fails, do nothing and just throw the exception).  Then, when writing to Kafka, assume that the write might fail.  Use the built-in auto-retry functionality to get it to keep trying for a while.  If that eventually completely fails, try to write to a dead letter queue and create some sort of manual mechanism for admins to sort it out.  And if writing to the DLQ fails (i.e. Kafka is completely down), just log it some other way (e.g. to the database), and again create some sort of manual mechanism for admins to sort it out.</li>
  </ol>
  
  <p>Anyone got any thoughts or advice on the above, or able to correct any mistakes in my assumptions above?</p>
  
  <p>Thanks in advance!</p>