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