CODEWITHSUNDEEP
databaseconcurrencydistributed-computing
KDX2
KDX2

Reputation: 1023

Concurrent writes to a shared network resource

Here is the context for the problem I am trying to solve. There are computers A and B, as well as a server S. Server S implements some backend which handles incoming requests in a RESTful manner.

The backend S has a shelf. The goal of users A and B is to make S create and place numbered boxes on that shelf. A unique constraint is that no two boxes can have the same number. Once a box is created, S should return that box (JSON, or xml...) back to A and B with its allocated number.

The problem boils down to concurrency, as A and B's POST ("create-numbered-box") transactions may arrive at the exact same time at the database - hence get cancelled (?). I remind, there is a unique constraint - no two boxes are allowed to have a same number.

What are possible ways to solve this problem? I wouldn't like to lock the database, so I am looking for alternatives of that. You are allowed to imagine that between the database and the backend layer calling the database we may have an extra layer of abstraction, e.g. a microservice, messaging queue... whatever or nothing at all - a direct backend - db exec. query call. If you think a postgres database is not a good choice to say a graph one, or document one, key-value one - feel free to substitute it. The goal is in the end given concurrent writes users A and B to get responses to their create (POST) requests and each of them have a box on that shared shelf with a unique number, with no "Oops, something went wrong. Please retry" type of server response.

I described a simple world with users A and B but that can in theory go up to 10 000 users writing, not just 2. As a secondary question, I'd like to ask, is there a way to test conflicting concurrent transactions in postgres?

I will go first. My idea is, let A and B send requests and fail. Once they fail, have retries with random timeouts in some interval. Let's say up to 3 retries. This way for A and B I will try to separate the requested writes to the db and this would allow for some degree of successful resolution of the scenario. However, I don't think this is a clean solution and I am looking for alternatives you can think of. Just, please keep in mind the constraints and freedoms I mentioned above.

Upvotes: 0

Views: 132

Answers (1)

YZats
YZats

Reputation: 61

Databases such as Posgres include capabilities to have a unique number generated by the database (see PostgreSQL - SERIAL - Generate IDs (Identity, Auto-increment)). So the logic for your backend service S could be:

  • lookup if user has a record in the database already
  • return the id if it does
  • otherwise, create a record and return the newly allocated id

To avoid creating multiple boxes for the same user you need to serialize the lookup/create logic based on user id. Approaches to that vary from merely handling one request at a time in your service S to, for example, having Kafka topics that partition requests to different instances of service S based on user ids -- all depends on the scale.

Upvotes: 0

Related Questions