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
U2 sets it to
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).
- 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
Nmost 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.