-1

I am working on design of a distributed system which will process some events. For simplicity lets say, multiple instance of same service will be consuming from same queue, every message will have a id for which the event has generated and it will read the current value of it from the DB and increment it by 1 and update in DB. The service has a SQL with transaction; Pseudo code:

.
.
.
start transaction
   state <- read the state from db for the said id
   count <- read the count from db for the said id
   if no record found
       state <- 'QUEUED'
       count <- 0
   if state == 'QUEUED' and count == 5
       state <- 'INPROGRESS'
   if state == 'QUEUED' and count < 5
       count <- count + 1
   update the DB for the said id
commit
.
.
.

Consider, current DB state at 9:59:00:00 am

-------------------
id | count | state 
-------------------
0  | 4     | QUEUED
-------------------

There are 2 instances of the service are available and there is one event with id = 1 at 9:59:20:00 am; If the Service(instance-1) takes 3 second to make the updation in DB then at 9:59:23:00 am DB state will be

-------------------
id | count | state 
-------------------
0  | 4     | QUEUED
1  | 1     | QUEUED
-------------------

Now there are two events with id = 2 at the same time or very close interval. Say,

Event-1 at 10:00:00:00 am  
Event-2 at 10:00:00:50 am

Service instance-1 takes 3 second to process Event-1 and Service instance-2 takes 2 second to process Event-2.

Will this produce any inconsistency, or very time in such scenario it will produce same result (At 10:00:06:00 am for id=2, value=2, state=QUEUED)

-------------------
id | count | state 
-------------------
0  | 4     | QUEUED
1  | 1     | QUEUED
2  | 2     | QUEUED
-------------------

To handle this kind of race condition any other good design pattern available, like locking

Thanks in advance for considering this problem statement

Improve this question
7
  • I'm not following your example scenario: "Now there are two events with id = 2" is particularly puzzling. Why are there two events with the same Id? Commented May 18, 2023 at 19:23
  • Hi @JimmyJames, so there are publisher based on some logic it will generate events. Now those events can have new id or existing id. Now my example for id = 2 two events are generated Commented May 18, 2023 at 19:41
  • OK, I think events are about something with id = 2. When I first read this, I thought you were saying the event's id was 2. You might want to clarify that in the question. I'm now unclear how you end up with 2 as the value. Wouldn't both instances find 0 rows in the db? Commented May 18, 2023 at 19:42
  • Thank you for pointing out, I have updated it Commented May 18, 2023 at 19:43
  • Or is 2 the desired answer in that scenario? Commented May 18, 2023 at 19:44

3 Answers 3

Reset to default
1

There's a nice pattern for this kind of problem that uses an optimistic locking approach. You don't need anything special to implement it. Just a uniqueness constraint on the id column and a where clause on your update SQL.

So first, by putting a uniqueness constraint on the id column, you prevent two rows from being inserted with the same id. So in your scenario, both event processors see no rows. Each will then attempt to insert one. Due to the uniqueness constraint, only one can succeed. The handler with the failed insert will need to catch the error and retry.

The second part of this approach is that when you do find an existing row, you should add the current count to the where clause. For example, if you find a row and the count is one, your update should be something like:

UPDATE status
SET count = <count + 1>
WHERE id = <id>
  AND count = <count>

The AND clause is the trick. If another processor has incremented the count, the update won't fail but no rows will be changed. You simply check the updated row count after you execute the statement. If 0 rows are updated, you retry the update after getting the new count.

I like this approach because it avoids the complexity and costs of pessimistic locking. It's also fairly easy to understand, IMO.

Addendum:

There's another approach I would also consider in your situation. Instead updating the table in place, you instead insert a new record for each transaction. You then no longer store a count column but rather use a count() query to determine whether you have 5 transactions. Note that in this case, it will be difficult to prevent more than 5 transactions from being processed and written to the DB.

The advantage with this is that there is no locking required and the sequence and timing of the processing can be easily observed from the entries.

Improve this answer
18
  • Thank you, it is fairly a simple, easy to understand and effective solution. Commented May 18, 2023 at 20:02
  • Do you have anything else in mind that I can explore for the same Commented May 18, 2023 at 20:03
  • This is generally how I would do it. The only issue that can come up with this is if you have a very high volume of transactions hitting the same id, you could have a starvation situation. But given 5 max updates, you are done, that's not really an issue, unless there's some other constraint that you haven't mentioned. There are other possible solutions, but I find they can be difficult to get right and often come with performance challenges. Commented May 18, 2023 at 20:06
  • Mostly this will solve the issue, but if you spell out others I will have a look and learn something. One more thing is there any workaround if I failed to meet the uniqueness criteria, like planning to follow SCD type-3, so the primary key will be in combination of timestamp and retrieve based on sorting of ts. Commented May 18, 2023 at 20:12
  • 1
    @mentallurg I understand your line of thinking here but I think the Ids are coming from somewhere else. The problem, as I understand it, isn't a race condition on the id generation but rather the updates to the count. Commented May 18, 2023 at 20:35
0

There are different approaches, depending on what database you use, and depending on how much implementation efforts you can afford.

  1. You can use sequences. Databases like Oracle, MS SQL Server and PostgreSQL support them. Every time you call nextval, it gives you a next number. This guaranties that every time you will get a new ID. To improve performance, you can configure sequence to generate IDs in block of let say 100 elements. Thus on the 1st call one services will get ID=1 (and can use IDs 1..100 without requesting the sequence again), the 2nd service will get ID=101 (and can use IDs 101..200 without requesting the sequence again). The IDs in database will then not necessarily correspond to the insert time and may look like 1, 2, 101, 3, 102, 103, 4, 5, 104, 105. But the purpose of ID is to identify the record, not to give any meaning like creation time. If you need creation time, just add a column that means creation time and sort by it when you need it.

  2. You can use autoincrement column type, if you use databases MySQL or MariaDB.

Improve this answer
7
  • "nextval" or autoincrement -->wont applicable here as this is a simplified version of the problem. actual problem has some complex task. Idea is to have consistency. Commented May 18, 2023 at 17:33
  • @SiddharthaSadhukhan: You asked about race condition. Sequences solve it. What you are asking now, is a different thing. Post another question an describe what your actual goal is. If it is idempotency, again explain why idempotency, because may be you have another reason, and to reach that another reason you think idempotency can help. Commented May 18, 2023 at 17:37
  • Please find the update. Operation could be anything but it should provide consistency Commented May 18, 2023 at 17:56
  • 1) @SiddharthaSadhukhan: "Idea is to have consistency." - You still have not defined, what do you mean by consistency. I don't see any inconsistency on using sequences. Commented May 18, 2023 at 20:16
  • 2) @SiddharthaSadhukhan: If you real goal that you don't name is to have IDs in some particular order depending on messages in the queue, you will not reach it by a simple approach. Why not? Because if service instance A receives a message earlier than service instance B, A can hang for some time (because OS is swapping, or because a CPU switched to another thread or another process for some time and A is being not executed for some time, because CPU has high load and A receives relatively small part of its power, etc.)... Commented May 18, 2023 at 20:21
0

In this case, you can leverage the RDBMS to do the needful. Essentially you are leveraging the atomicity property of the ACID properties of the RDBMS.
Note: The following is pseudo-SQL.

For

if no record found
   state <- 'QUEUED'
   count <- 0

Do an insert like

Insert in Table ...
id = ..., state = ..., cout = ....
Where Id = ...

If there is no row in the table for the Id, it will be successful, and you are done.

For

if state == 'QUEUED' and count == 5
   state <- 'INPROGRESS'

Do an update like

Update Table ... state = 'INPROGESS'
Where Id = ... and count = 5

Again success is what tells you are done

For

if state == 'QUEUED' and count < 5
   count <- count + 1

Do an update like

Update Table 
SET count = count + 1
where count < 5 and Id = ...

Again success ...

This is the easy part. However, the more difficult question is, how to deal with the situation when a message is not deleted or times out and shows up to be processed again.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.