Database consistency for a distributed system

Question

I'm working on a phone call monitoring project. The aim is to have one row in the calls table for each ongoing call. Each call instance may be updated by many different servers/threads as more information is gathered about the call. Each call instance (a row) is mapped to a Java object. When a server/thread needs to update a call, it will read the call, apply the update, and then write the call back to the database.

This obviously introduces consistency problems, if a separate update U2 writes in between the read and write of another update U1, then when U1 is written, it writes stale data.

My current idea to fix this is to capture the changes for an update. When an update happens, it will read a "last updated" time. Then, the changes are applied and written back to the database conditionally (only written if last updated is equivalent to what was read). If the "last updated" time has changed, then a retry is executed, re-reading the data, applying changes, and attempting another write. This will retry the write 3-5 times before giving up.

This has some drawbacks in regards to performance, but is the best I was able to come up with. There is also the rare yet possible problem of retries executing out of order (across different updates). I'm hoping to make all changes independent of the order they are executed in, and I know that order shouldn't be relied on. A good example of this is call status. Say update U1 sets a call status to ongoing and U2 sets it to finished. If U1 fails, U2 completes successfully, and then U1 completes on a retry, the status will be incorrectly set to ongoing. One option is only allow ongoing to be set when the call status isn't finished, but this isn't always clear of all fields and feels a bit messy. There are always options to try to update columns individually, come up with some sort of merging policy, or something else.

My primary goals are consistency and performance. While these criteria are somewhat contradictory, NoSQL seems like a decent tradeoff for my goal (my decision is described more below).

My questions:

• Is there a better way to achieve this goal? It feels sloppy to have every change get recorded as deltas and executed in a retry loop, but I'm not sure if there's a better way to do it.
• I'm currently using NoSQL. Data loss is tolerable, so replication is disabled. I am using conditional transactions to implement updates. Is this the right technology for the job? With replication disabled, I expect that to help considerably in addressing the performance hits introduced by transactions. Scylla/Cassandra's ring hashing seems to be an efficient way to distribute the data, especially with the added benefit of handling call data close to its geographic location. I've also considered Redis, but decided against it as the added database i/o isn't significant, as well as Redis's (somewhat?) poor distributed performance.

It seems like locks would work well in this scenario, but I'm not sure if NoSQL/Scylla supports locks. Should I switch to something that does?

Appreciate the help, will edit with any information if needed. Thanks!

Edit: Kafka seems like a compelling choice. There are a few queries I'm not sure how I'd make work with Kafka though.

Here are the particular queries I'd like to make efficient:

• Update an existing (ongoing) call by the from number, to number, and status, creating the call if it doesn't exist.
• Update an existing call by id. (Each call is assigned a unique UUID.)
• Query the N most recent calls from a particular number
• Query a call by its from number, to number, and status.

I was thinking to make a new topic for each new instance of a call. Another potential route would be to have one topic for every phone number, and call events are written to this topic for all calls originating from that number.

Answer 1

Why are you looking at NoSQL? More traditional databases that guarantee ACID will make your life easier. Transactions that fail don't leave data, they can be logged and only the failures you care about can be retried instead of everything. Locking is complicated to implement on you own, and all relational databases come with advanced locking patterns. NoSQL isn't really guaranteed to be better performance compared to a SQL database that is well designed. Especially if some data loss is acceptable you can tweak values in relational databases so the overhead of transactions won't bottleneck you, so you get 99% of their benefit without the drawback.

Another option that probably better matches your requirements is an event focused system. Something like Kafka. With this approach you can send all updates through topics and then decide as you consume them if the update is properly in order or should be discarded. This can all happen in a very quick time frame and in the case of Kafka is also distributed. the event streaming approach seems to closely match your use case. you could probably even reduce the need to do a read before a write and simply send what should get updated to Kafka, then decide if that update should be applied to the "current state" on a per field or as a whole basis. Because all your activity is stored as a log you could even have a mostly right version of data with up to the ms updates and a totally right up to the second updates of your data if that becomes desirable.