I found this method a few months ago while working with a client that needed to have control over an auto generated unique number for each of the products he sold. He sold raffle tickets as single items as well as part of a combo (three tickets on the same product, or five tickets on the same product) and each ticket sold needed to have a consecutive number as well as it needed to be unique so no two tickets sold would end up being the winning number 😉.
The details below explain how I was able to solve this issue using Optimistic Locking with Wix Data enabling my client to have a thriving online raffle business🎟🎫🎉.
Business Problem
So let’s summarize the business problem before jumping onto the technical solution:
There’s a Wix website selling raffle tickets using Wix Stores.
Each raffle ticket needs to have a unique number so that only one ticket can win the raffle.
Ticket numbers are auto-generated and consecutive (not user selected).
A store order may contain many tickets each with its own unique number.
There are three different products in the Wix Store.
Product A: This entitles the customer to only one unique ticket
Product B: This entitles the customer to three unique tickets
Product C: This entitles the customer to five unique tickets
Question: How many raffle tickets will be be generated for a store order that includes four products “A”, one product “B”, and three products “C” ?
Answer: 4*1 + 1*3 + 3*5 = 22 unique raffle tickets.
So Wix Stores processes the order and you proceed to insert the 22 unique ticket numbers in your custom database, right?
But! Since your Wix Store is so successful, you get hundreds of store orders per minute with many of them happening at the same time, and so… you start duplicating the ticket numbers, creating chaos in your backend and start getting contacted by angry customers that have paid for the same ticket number as other customers 🤬
Your solution? Optimistic Locking 😉.
What is Optimistic Locking?
In simple terms, it’s a concurrency control method used in relational databases to prevent duplicate records in a collection. This means that no two records in the database will have the same value.
In a nutshell, Optimistic Locking works as follows:
You get a “unique value” to insert into your collection.
You try to insert the “unique value” into your collection.
If the “unique value” already exists in your collection then the insert is aborted and the process is automatically restarted.
In Wix, the only way to achieve optimistic locking is by a combined approach of using the ID field within a supporting collection + using a beforeInsert hook to run the optimistic locking algorithm. Here are the steps you will need to follow:
Step 1: Database Creation
You will need to create two wix data collections. The first will be used to generate and store your unique values (Collection1) and the second will be used to include those unique values along with any other data you are capturing from your users (Collection2).
Collection1 Schema: ID | Title
Collection2 Schema: UniqueValueFromCollection1 | AnyAdditionalCollectionFields
Collection1:
Collection2:
Step 2: Managing Concurrent Inserts
For this step you will leverage the beforeInsert hook for your Collection2. The idea here is to try to complete the insert of your record in Collection2 while ensuring the uniqueness of your value from Collection1. To do so, you can follow this algorithm:
//’backend/data.js’
import { getUniqueValue } from 'backend/utils'; //you can come up with the way you get the unique values
import wixData from 'wix-data;
export async function Collection2_beforeInsert(item, context) {
//Optimistic locking algorithm
let ok = false;
//use a function to generate a uniqueValue
let uniqueValue = await getUniqueValue();
do {
try {
//Try to insert the unique value into Collection1. It will fail if another insert has entered the same value before
await wixData.insert(Collection1,{“_id”:uniqueValue,”title”:uniqueValue});
//If nothing fails, then your record is good to go to complete the insert
ok = true;
item = { "uniqueValue": uniqueValue, ...item };
} catch {
//If the insert fails, then get a NEW unique value and try again.
uniqueValue = await getUniqueValue();
}
}
while (!ok)
return item;
}
Step 3: Capturing the concurrent events
The last step is to capture the events that will trigger the insertion of user records into your database collections.
In this example, we are triggering the insert on every Wix Store Order Paid:
//’backend/events.js’
import wixData from 'wix-data;
export function wixStores_onOrderPaid(event) {
wixData.insert(Collection2, event);
}
Conclusion
And that’s it! Three simple steps can take you a long way in your pursue for uniqueness and concurrency 😎.
There are many different website events that could be used in conjunction with Optimistic Locking for various applications such as New Store Orders, New Member Sign Ups, New Bookings, among others.
You can find more about the types of events available from Wix in the Velo API Reference.
Until next time!
Written by Felipe Bustos - Director & Founder at DIAYTECH & Wix Velo Master.
