I worked on a system that would send requests to a process based on a shard ID. The process was made unique using :global process (I know about netsplits). That system worked really well for us and we never hit any issues with that aspect of it. That system was doing external API requests, not database queries, so I didn’t have the ability to do anything fancy with the database. However, it was an optimization solution. It would be correct even if the cluster netsplit and 2 processes did the same work.

If it was possible, I would certainly try to solve this via the database and not have any fanciness on the Elixir application side. The reason is that you have a correctness problem here. If you mutate incorrectly, then your data is incorrect. This makes the problem of netsplits or general blips a much bigger concern.

My immediate thought process follows this order:

1. Atomic updates are preferable. No magic required anywhere
2. SELECT FOR UPDATE a single record to lock it during the write. Always perform modifications after the record is loaded to 100% guarantee you have the latest version
3. Advisory lock around a group of operations to ensure they don’t enter simultaneously
4. SGP (single global process) based on aggregate ID to perform the updates

The database operations are preferred for the reasons I mentioned above. If you go the SGP route, then it’s likely still okay performance-wise, because there’s a global process per domain aggregate ID.

This is a fairly deep question because it highly depends on your microservice architecture. I only ever did this within the cluster of a single application and would never have tried it cross microservice. There’s just too much that can go wrong. Likely, you’ll need to use whatever request/response API you prefer between the microservices (like HTTP, gRPC, etc).