Fix race condition resulting in duplicate payments

[sushilshinde]

Root cause

The issue is in Legacy Payment Processor

What happens in Legacy Payment Processor

Read the “challenge.notification.update” events.

Check if the challenge is a V5 Task and is completed.

Query Database to see if a payment record exists for the challenge

If no, create a payment

If yes, do nothing.

Problem

Sometimes 2 “challenge.notification.events” are generated for the challenge in quick succession.

Both execute the Steps 2 and Step 3 execute in parallel, leading the code to find no existing payment causing the same challenge being processed twice.

Solution

Wrap that read/write in a transaction. That way if one process reads, sees the payment has not been made, then writes it would be guaranteed to succeed. If another process comes in and tries to start the same transaction it would block until the first finished and see the payment was already made.

The create payment logic is already in a transaction. We need to bring in the select statement in the same transaction too.
legacy-payment-processor/processorService.js at develop · topcoder-platform/legacy-payment-processor
Additionally, we may consider adding a unique constraint in db for user_id, challenge_id, and payment_type_id condition.

Note, if the legacy payment processor isn’t issuing direct queries to the DB and is instead going through an API, we could add an API specifically to issue a payment and it would use a transaction like I described above.

[SathyaJayabal]

Verified a v5 task on dev. Payments are getting created only once.

Unable to verify a challenge payment in dev due to this issue:
topcoder-platform/legacy-challenge-resource-processor#30

To verify payments in prod, we will access to a project with an active billing account.

[SathyaJayabal]

This issue is observed in production again.
https://topcoder.atlassian.net/browse/PLAT-147?focusedCommentId=16571

[SathyaJayabal]

This is verified on prod.
Only one payment was added even though multiple events were generated.

[SathyaJayabal]

verified on prod