I'm currently writing some microservices, some of them communicating using RabbitMQ, RedisDB, Kafka and other communication streams.

When any of those connections drop, I can't know for sure if a query already executed.

For example, if I insert a new key into a database, and the connection drops, two scenarios can happen:

  1. Key was inserted and only then the connection was dropped. In this case I don't need to insert again.
  2. Key wasn't inserted, in which case I need to re-insert.

Always retrying the query can cause duplicate keys to be inserted.

  • Is there any general pattern to handle connection drops, that avoids this issue altogether?
  • What do I do with the user during this time? I bet large companies like google don't return 500 every time one of their servers go offline.
  • Is the concern for a system that will be eventually consistent, or for an atomic update? The typical approach for ensuring all data related to something updated at once is a transaction--which can work if it is completely within your microservice boundaries. However, I can think of a few cases where transactions are not the answer. Commented Aug 10, 2020 at 12:04
  • Also, is the key generated by the database or outside of the database? (example: GUID/UUID) Commented Aug 10, 2020 at 12:06
  • Eventually, it needs to be consistent. Transaction would work if I'd have a few operations together, but if I have a single operation, which timed out right after completion, it would look like a disconnection while the operation succeeded. For the purpose of the question, the key is generated by the database, meaning I don't know it. The point is that I can't always query the database and check if the insertion works (for example, I can't put an object into a Kafka broker and query Kafka to check if it inserted correctly).
    – Bharel
    Commented Aug 10, 2020 at 12:14

3 Answers 3


This is an area where you don't have canned patterns. So let's look at what your stated needs are:

  • You need to know if am insert was successful
  • The update could come from any of 3 different sources

Ideally, we would need a means of generating a unique key that is derived from the data you are receiving in some way.

If we only had one source of information, we would be able to use the message Id to identify if the record was inserted or not. Another option would be to codify the source and the message id together. Example: source is codified as 1,2 or 3, so you append the message Id to the 1, 2, or 3 prefix. It can work, assuming every message Id is unique. That may or may not be true.

Another option is to have a creation date, trace ID and trace source in the table you are writing to. This allows you to query before writing. In this case I would have a transaction:

  • Query to see if there is a record written since the message was authored that came from the same source and has the same message id.
    • WHERE creationDate > ? AND messageSource = ? AND messageId = ? where the ? marks parameters for the query.
  • If nothing is found, write the update (including the source and trace id)--otherwise it has already been written
  • Complete the transaction

On the topic of connection drops

If you are having a connection dropped intermittently, but often enough where this is a real problem, then something is wrong. It could be that your configuration is set for tolerances that are unreasonable. It could also be that you need to change your approach. For example, a timeouts would be a symptom where you need to step back and take a stock of the larger picture.

  • Don't request a connection until you are ready to do something with the database
  • If it's going to be a while until you do the next thing, release the connection when you are done
  • Determine if the timeout is network related, record related, or due to some other resource contention

When you are getting timeouts due to a network something is very wrong. I was on a program where actions that were taking milliseconds suddenly started taking minutes. It turned out that the infrastructure team moved the DNS server in a way where our servers were not updated. In self defense we put entries in our HOSTS file so our servers could always find the other servers we deployed to, as well as fixing the IP address of the DNS server.

Sometimes it's not the network layer, and your database is suffering from severe record locking problems. This can happen if your database silently promotes record locking to page locking, or worse, page locking (here's looking at you MS SQL Server). Your options here are to offload queries from your database or ensure that queries are for snapshots of data (i.e. does not have to wait for transactions to resolve). In this case, make use of Redis when reading individual records, and ElasticSearch (or equivalent) when performing complex queries. The idea is that the database serves as gold master and everything else is a slave to that data. The more you can relieve contention from the database, the faster your system will feel.

Finally, there can be other types of resource contention. Examples include disk access during a security update, network bandwidth due to very chatty communications, etc.

It's always good to have a solution to ensure a write once semantic, but when you are constantly dealing with something that should not be a problem, sometimes you need to take a look at what's causing the issue. That's a pain, but the general process is the same:

  • Look for correlations (i.e. events happening at the same time)
  • Go through a process of elimination until you find the cause
  • It doesn't happen frequently, but on a distributed system with lots of services, it can happen frequently enough to justify the need for a solution. Your statement saying there is no canned pattern, is exactly what I was looking for. Message IDs are not always feasible but are the only solution I guess. Thank you!
    – Bharel
    Commented Aug 10, 2020 at 14:29
  • Right. You need to be able to trace if your message was handled or not, and the only consistent identifier for that purpose would be the message id. The solution outlined above allows for message ids to be reused, but scopes the query well enough that if there is any implicit locking, it remains a record lock. Commented Aug 10, 2020 at 16:21
  • @Bharel: the approach presented here has a name: it is called "making write operations idempotent". If one calls this a "canned pattern" or not is pretty irrelevant.
    – Doc Brown
    Commented Aug 10, 2020 at 17:10

There's no perfect solution to exactly once messaging. But the impossiblity of the solution relies on the possibility of missing multiple messages, distributed processing and bad actors.

For normal senarios you can reduce the probablity to virtually zero.

  1. Generate an id before you send, query it afterwards and store it to prevent duplicates.

  2. Hold a sequential count, error and request resend if you receieve an out of sequence message

Generally these things are handled by the communication protocol and you dont need to worry about them, but with high volume and/or distributed systems you want to build in immutablity to everything and have a way to pickup errors after the fact so they can be repaired.

So in your example where the commit command errors on the client but the transaction has completed on the db, you have been super unlucky multiple times.

It should be such an infrequent occurance that simply writing the error and transaction to the log and having a human check the db manually in the morning is acceptable.

If you are designing something like the TCP protcol however, missed packets are common thing, you'll want to include acknowledgement and anti duplication methodologies


It sounds like you are using some sort of sequential key in your tables (like an identity column). If you change to a Universal Unique Identifier (UUID) which is generated by the sender then you can retry as many times as you'd like (as you will be able to check, if the UUID already exists in the database).

(You can also use a hybrid, if there is a reason for your sequental identifier)

  • Unfortunately, if for example I use a Kafka stream and I insert a new object to it, I'm not sure if I'm able to query Kafka and check if it was inserted or not.
    – Bharel
    Commented Aug 10, 2020 at 12:39
  • Wait a minute... is it your connection to Kafka that gets dropped or your connection to the database? Why would you query Kafka for something happening in your database? Commented Aug 10, 2020 at 12:52
  • @Bharel I am not sure I am following? I assume you are using Kafka as a message queue. I would wait in my program, until I recieved a response from Kafka, that the message was added to the queue. After that, assuming the use of UUID, the program would be able to move on, since the handler can now run it mulitple times, if e.g. the database connection drops. Commented Aug 10, 2020 at 13:01
  • @JakobBuskSørensen If you wait on a single Kafka connection, and you lose that connection, you won't receive the response if the message was added or not.
    – Bharel
    Commented Aug 10, 2020 at 13:16
  • 1
    Apparently the term I was looking for is "Exactly-once semantics". And by the looks of it there's no general way to solve it, while attempting to disregard the underlying technology .
    – Bharel
    Commented Aug 10, 2020 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.