HANDLING MULTIPLE INSERTS IN A MICROSERVICE

Question

I Have an inventory service which is responsible for all the possible operations that can be performed on an inventory. namely create, update, delete, read. The create endpoint is behind a queue to avoid data losses. While creating an inventory, i need to insert data into multiple tables. lets say stocks (primary table) StocksadditionalInfo (secondary table)

while creating the inventory i need to first insert it into the primary stocks table and then use its Id to insert into the secondary table StocksadditionalInfo. now lets say while insert the data got created in the primary table stocks but due to some db exception the additionalInfo data didnt get create.

since the endpoint is behind the queue, the inventory will be tried to be created again. but now since the inventory is already created in my primary table, it will give duplicate exception.

how do I avoid this.

one way would be to make a get operation before insert and if not present insert it. and if present update it.

other way could be i could have another endpoint for the additional data and that endpoint can be called from behind the queue. the problem with this is that as the no. of additional table increases i will have to add new queues, new endpoints

any suggestions/resource will be helpful

Answer 1

First I'd wrap the create/update for stocks and StocksadditionalInfo in a transaction, even its just two inserts. This simplifies the cases for a partial insert like you have and models the data consistency of your application better.

Secondly to facilitate the reprocessing maybe use either insert on duplicate key update or insert ignore to avoid your duplicate exceptions.