CODEWITHSUNDEEP
sqlarchitecturemariadb
Bob
Bob

Reputation: 666

How to deal with concurrent processes working in MariaDB

We have an application that during the day will collect events and process them once or twice per day. But we have one database and two instances of the application so we are looking how to make sure that every event is processed once.

The events will contain the ID of the registration, and once or twice per day we want to process every registration that had an event. I believe that this means that we should store every event with the current timestamp. But I am stuck figuring out how to process them taking into account the duplicate instances.

One way is to add column processedTimestamp, an instance will SELECT a row where that column is NULL and UPDATE it to the current time. But I believe that this workflow will not definitively prevent the two instances from processing the same registration. They could both do the SELECT before the first UPDATE happens.

Another way I thought of was to start a transaction so that the all the events from a registration are locked and another instance can't get to them, but I feel like this isn't really what transactions are meant for.

This seems like a common problem to have when dealing with multiple instances, but what is the solution?

Upvotes: 1

Views: 837

Answers (1)

Rick James
Rick James

Reputation: 142518

FOR UPDATE may be a key component in your processing:

BEGIN;
SELECT (the rows you might process) FOR UPDATE;
do the processing
UPDATE (the rows you decided to process);
COMMIT;

This pattern tends to turn a conflict into a delay. That is, the rows selected will not be available to any other process until after the COMMIT. At that point, the other process would see that the rows have been processed and skip them.

You should not allow a BEGIN...COMMIT to run for more than a few seconds.

I don't see any need for a TIMESTAMP.

Upvotes: 2

Related Questions