Hello!
I'm having trouble with handling concurrency in my API endpoint. Here's the situation:
I receive thousands of webhooks per minute, and sometimes experience big short spikes in traffic. To manage this, I want to spread out the load by implementing batch processing. I've created a webhook controller function that processes records by performing the following steps:
Retrieve the current state record.
Perform various calculations and manipulate variables.
Edit the record.
Add the record to a corresponding log table.
The structure of my processing logic is as follows:
{ "type": "Messages", "batchtriggerrate": 50, "currentrate": 11, "processing": "realtime", "currentrecord": 0, "currentratebatchsize": 1, "currentbatchid": "7a5f95ec-197d-49f7-90f0-76a0f7a1f02b", "islastmark": false, "lastid": null }
When processing is in batch mode, currentratebatchsize increases based on currentrate (webhooks per minute), and currentrecord increments by 1 each time, resetting when it reaches currentratebatchsize. Each reset of currentrecord also resets currentbatchid. This setup works fine in testing with synchronous API calls in Python, but in async load testing scenarios, records are not being written to the database as expected. For example, currentrecord 10 might be written before currentrecord 6. This disrupts the functionallity of triggering that batch is complete.
Here's what I have tried to address the issue:
Added a timestamp on writing immediately.
Used PostgreSQL advisory locks to lock the writing query.
Despite these attempts, I'm still encountering issues. I’m seeking advice on handling concurrent API processing with time sensitive data.
Any Xano Expert that could give me an advice?
I'm open for all kind of variations, Lambda JS, Direct Queries...
Thank you!