A user is doing multiple transaction considering opening balance and closing balance of wallet. Using API he is doing more than thousands of transaction in a minute. The problem arises is 3 transaction starts at same time considering millisecond (ms) also and ends at the same. So each transaction has considered same opening balance which result errors in calculation. How can i handle this situation? I hope you can understand the situation. If you have any solution please share.
Hi, @sid sir, any suggestion?
This is called a “race condition”, I documented this behaviour here:
As you can see, currently Wappler has no built-in protection for this. You’d need to wrap your queries in a database transaction or perform the calculation directly in the SQL query - either way through a Database Custom Query
If you’re using MySQL/MariaDB you can ping @ Hyperbytes, he might be able to help you
If you’re using PostgreSQL try checking this:
Thanks @Apple, I’m using postgresql, let me try this. One problem might arise that if the transaction becomes serialised then transaction processing time will increase, which might slow down the process. What do you think about it?
As Apple has mentioned in the Feature Request post, using transactions is the only reliable way to handle this.
Until Wappler supports this natively, you can convert everything to custom queries, and use transactions to apply the changes in DB.
A very rough and not recommended work around would be to just add a random delay/wait before the insert/update step. Which could ensure some breather for concurrent transactions.
If you are using MySQL you could look at row locking as a possible solution, you will need to use a custom query but any record from a SELECT can be locked at row level by using the FOR UPDATE clause. Lots of useful info re this via google. Not a feature I have used before to be honest, tend to avoid finance stuff other than basic ecommerce.