Kafka and mysql operations, how to ensure the sequence

problem description

In the

A project, there is a method that first saves the data in mysql, and then sends the id of the data returned by the insert operation of mysql as the message body through kafka. (this method has been @ Transactional, so inserting data and sending messages should)

After receiving the message, the

B project queries the MySQL for the details of the message and performs subsequent actions.

when deployed online, mysql is the remote database server, while the kafka,A project and Project B are deployed on the same server. One problem is that project B has received the message sent by An and started to query the database according to the id in the message, but at this time, the insert operation of the database has not been completed, resulting in empty query results.

in this case, how to control the database insert operation and kafka sending messages to ensure that when B receives the message, the database insert operation has been completed, and when the kafka message fails, the correct fallback operation can be performed.

at present, my approach is to manually control, remove the @ Transactional annotation, and manually delete the data added in the previous database insertion operation when there is an exception in message delivery.

is there a more elegant solution?

Kafka java
Jun.14,2022

first analyze why the order problem occurs:
plus @ Transactional transactions that belong to the database. The pseudo code is as follows: 

 

 @ TransactionalEventListener 


 or take the way of local event table, write the message to the local event table, and the polling thread handles the delivery problem

Previous: The problem of parameter reference of PHP call_user_func function

Next: Workerman startup has been reporting an error process terminated

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1beb773-39e39.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1beb773-39e39.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?