I have a database with accounts and each account has a field for balance (virtual money/coins). I have a button which calls the following code that transfers 10 coins of the balance from one user to the other.
// ...
wixData.get("Accounts", user1_id).then((user1) => {
wixData.get("Accounts", user2_id).then((user2) => {
user1.balance -= 10;
user2.balance += 10;
wixData.bulkUpdate("Accounts", [user1, user2]).then(() => {
console.log("success!");
});
});
});
This works all fine, however there’s a problem. If the user has the page open twice (2 tabs) and clicks the button on each page very quickly (almost at the same time), then the balance of the users is only affected once. Basically, the second time the user clicked the button, the code from the first click hasn’t finished yet. I think this is a common problem with databases (outside wix), but I wanted to know what you recomme me doing on Wix, to solve this problem.
Not a specific answer as such: Just out of interest, how long does it take to run the button click function from start to end? use console.time()
Is your code running in the front or back-end?
Someone could tell you more than me, but I believe some browsers run as a single process, so multiple tabs can end up being blocked which is I think what you are seeing. Databases (unknown as to WIX) are designed to handle multiple concurrent requests to the same record.
Put a little debug in the code - is the click process being blocked or the actual updates?
@pptgames you might want to look at optimistic locking as a solution
But since there are 2 records to be modified at once I don’t think this solution will be bulletproof (one record can be updated and the second might failed)
To work around that, I suggest working with an event-based approach where you record all transactions in one collection (source of truth) with a single document (that either pass or fail) then based on the transactions you can rebuild your balance collection.
This ensures that all transactions are recorded correctly and that at any time you can replay the history(transaction) to rebuild your balance collection.
I suggest you look at event sourcing pattern for more info
As @plomteuxquentin said. The problem might surface that, when calculating the balance over and over again from the individual transaction, queries become slow. You have 2 options:
- de-normalization : you log every transaction AND you write a total balance somewhere else. The problem with this is the risk of data-inconsistency: you need 2 writes into 2 different collections. If one fails and the other one succeeds, the balance (from the transactions) and the balance as a total might become inconsistent
2)something halfway is used by some banking systems: you still record every transaction but you write the balance inside that same row. SO now we have only 1 write action AND fetching the balance is a quick query of only the most recent row. If you use the system createdDate, you also get an index for free.
Good luck.
The function I have on my site is actually bigger and does a little more operations, but after measuring the time with console.time() it returned 2.5 seconds. The code is running in the back-end.
Suppose I implement option 2. To write the balance inside the row I have to get the balance from the account and then add or subtract. But what if the same user has bought something else at the same time?
For example the user has a balance of 20 coins and buys something that costs 10 coins and at the same time (or almost at the same time) something else that costs 15 coins. The first purchase would get the current balance (20 coins), but before it changes the balance, the second purchase also gets the current balance (20 coins). The first purchase subtracts 10 coins and saves the balance to 10 (20 - 10 = 10). The second purchase subtracts 15 coins and saves the balance to 5 coins (20 - 15 = 5). See the problem?
@pptgames that’s where optimistic locking gets in place. It’s an easy way to prevent 2 actions to update the same record (version) at the same time.
But because the second update will fail, you need to have a fallback mechanism in place to retry the operation with the newer version automatically.
That being said, it solves the problem of updating a single record twice, but not the problem of updating 2 records at the same time
@plomteuxquentin Ok, so I’ve written some code that I think does what optmistic locking is.
export function makeTransaction(accountId, value) {
return new Promise(async (resolve, reject) => {
let res = { updated: true };
// retry operation until no new version is available
while (res.updated == true) {
res = await attemptTransaction(accountId, value);
}
if (res.updated == false) {
wixData.update("Accounts", res.acc, { suppressAuth: true }).then(() => {
resolve();
});
}
});
}
function attemptTransaction(accountId, value) {
return new Promise((resolve, reject) => {
wixData.get("Accounts", accountId, { suppressAuth: true }).then((acc1) => {
acc1.balance += value;
wixData.get("Accounts", accountId, { suppressAuth: true }).then((acc2) => {
// updated: whether or not this is the latest version
// acc: the modified account
resolve({ updated: acc1._updatedDate.getTime() != acc2._updatedDate.getTime(), acc: acc1 });
});
});
});
}
I works, however it doesn’t solve the problem. I think it’s because checking for the latest version takes time and in the mean time a change could have happened. I think this isn’t the right way to do it, it should be done at a lower level, which I (as a wix user) don’t have access to. Wix should implement this to the database system. I’m not a database expert though.
@pptgames Where is the optimistic lock happening in your code sample? I don’t see any version checking in your code?
On top of that, you need to use beforeUpdate hooks to do the check https://www.wix.com/velo/reference/wix-data/hooks/beforeupdate
That’s how you access that lower level.
Wix won’t implement that has it will slow down all query even those who don’t need such a mechanism.
Finally, as I explained before, Optimistic lock works when 1 record is updated 2 times but won’t have any impact on 2 records being updated at once. For that issue, you need to use another technique ( transactions ) which Wix does not provide indeed.
If it is the transactions you are after, you are better off changing the database system and host your data in an external system such MySQL or Postgres.
@pptgames I’m afraid Quentin is right: you are trying to do something that wixData does not support (transactions). There is no way you can work around it, and you do need it to make it a reliable system (which you need for financial transactions).
In short: you cannot do this in a reliable way with wixData, you will need another db that does support transactions. Wix is never going to support it, we (Masters) raised the issue a while ago and got a “no”. Sorry for not having any better news.
Just out of interest, how long does it take to run the button click function from start to end? use console.time()
Is your code running in the front or back-end?
Just out of interest, how long does it take to run the button click function from start to end? use console.time()
toilet overflowing michigan