I’m trying to implement a simple application credit system, where users are assigned credits and then they consume them by performing actions in the application.
I have multiple database trigger workflows that fire whenever a user creates a chargeable thing, which tracks consumption in various database tables. Since users can create chargeable things in bulk, these workflows obviously fire asynchronously and many run at the same time.
The part I’m struggling with is that there doesn’t seem to be any way to perform an atomic increment/decrement on number fields in the database, and there is no DB locking mechanism - the only option to update a number field is to do
credit = credit - 1 which suffers from consistency issues and stale updates when multiple of these workflows run at the same time (ie, start with 100 credits, perform 5 bulk actions, end up with 98 credits rather than 95)
For now I am forced to keep a table of Consumption items, and then do a
search for all -> sum but that’s getting ridiculously slow as the table now has 100s of thousands of records…
Does anyone have a scalable way of tracking credit usage (ie not performing a sum over millions of records) or a way to atomically update a number field?