I have app with paymemt/subscription service, pretty much how the payment works is:

Initial payment

1. User initiate payment on the web ( click on “subscribe”)
2. Back-End send init request to the gateway – it returns payment id ( also used for subscribe payments )
3. Back-End send process request to the gateway
4. Gateway returns url to redirect
5. User is redirected to the gateway, fills credit card etc and pays
6. Gateway redirects back to the back end

In 6th step, after gateway redirects back to the back-end with POST request, i match returned payment id with payment id stored in DB, mark it as done and add membership to the user.

For membership i use the bable

create table membership
(
    id         bigserial primary key,
    start_date date not null,
    end_date   date,
    resub_date date,
    client_id  bigserial
    active     boolean default false
);

Now my resub mechanism is following, i have task that executes every midnight and does:

1. Retrieves all memberships that have resub_date for today and are active
2. init payment
3. process payment

I am using ThreadPool for steps 2 and 3, to in, so each thread init and process payment for membership, then takes another etc etc.

My question/problem is with “unlucky” timing during canceling membership and subscribing again

Imagine following scenario

1. Midnight of 1.10.2024
2. Periodic task executes
3. Task retrieves all user that should be resubscribed – including user A
4. Task starts to process membership of user A – check if he is subscribed again – yes and inits payment
5. User A on web cancel membership
6. User A on web resubscribe again – thus leading to **Initial payment** flow
7. Task is finished with inits payment and start process payment

In this case the user would pay twice.

In both init and process i can check if the user should be resubscrbed again ( but this would mean 2 additional database hits per resubscribe ), but with some unlucky timing the payment could still be processed twice.

What are the best ways to prevent these types of scenarios? Some kind of locking on row at db level, or some kind of custom lock that would lock on user id – in this case the performance? Somehow integrate redis into this? I know the chances for this are very slim but it is still problem.

I am using java so i am tagging this question as java but the answer doesnt need to be java specific.

Thanks for the help!