If you ever visit England and want to experience something unique, I recommend watching a Premier League game. You'll get to experience the distinctive passion and culture of traditional football.

However, obtaining a ticket requires some effort, as they are currently hard to come by. The most reliable method is to book through the team's official website. Occasionally, you may be fortunate enough to find available tickets, such as the ones shown below:

But as soon as you go to click it, it is gone 😂

I believe you may have encountered a similar case with some ticket booking systems. Let's see how we can implement one with minimal effort.

You can find all the used code in the below GitHub repro:

https://github.com/jiashengguo/ticket-booking-prisma-example

Model with Prisma

Prisma is a modern Typescript ORM that takes a schema-first approach and generates a fully type-safe client for your database operations.

The booking system can be simplified using the below two models:

model Seat {
    id Int @id @default(autoincrement())
    userId Int?
    claimedBy User? @relation(fields: [userId], references: [id])
}

model User {
    id Int @id @default(autoincrement())
    name String?
    seat Seat[]
}

Let’s assume we have 1k seats and 1200 users. You can seed the database with the below command:

npm run seed

Simple Approach

The intuitive way to do it is to find the first available seat and assign that seat to a user. The code for doing so is shown below:

async function bookSeat(userId: number) {
    // Find the first available seat
    const availableSeat = await client.seat.findFirst({
        where: {
            claimedBy: null,
        },
        orderBy: [{ id: "asc" }],
    });

    if (!availableSeat) {
        throw new Error(`Oh no! all seats are booked.`);
    }
    // Claim the seat
    await client.seat.update({
        data: {
            userId,
        },
        where: {
            id: availableSeat.id,
        },
    });
}

However, it has a major flaw. Let's assume that after the booking starts, all 1200 people immediately try to book their seats within 10 seconds. This is a common scenario for big Premier football games like Arsenal vs. Man United. To simulate this, add the following code at the beginning of the bookSeat function:

// Stimulate random booking time between 10s
await new Promise((resolve) => setTimeout(resolve, Math.random() * 10000));

Then after all the book requests are finished, let’s query the database to see how many seats are actually being claimed:

async function demonstrateLostUpdate() {
    let updatedCount = 0;
    let promises = [];
    for (let i = 0; i < 1200; i++) {
        promises.push(bookSeat(i));
    }

        await Promise.allSettled(promises)
        .then((values) => {
           updatedCount = values.filter((x) => x.status === "fulfilled").length;
        })
        .catch((err) => {
            console.error(err.message);
        });

    // Detect lost-updates
    const actualCount = await client.seat.count({
        where: {
            NOT: { claimedBy: null },
        },
    });
    console.log({
        successUpdatedCall: updatedCount,
        actualUpdatedCount: actualCount,
    });
    process.exit();
}

You can run it by npm run simple, and you will see the result like below:

{ successUpdatedCallCount: 1200, actualUpdatedCount: 863 }

💡If using sqlite, the success updated call might be less than 1200 due to some request timeout.

The results are obviously wrong:

1. There are only 1,000 seats, but all 1,200 request calls were successful.
2. The actual count of updates does not match the number of successful update calls.

It is because the code sufferers from the "double-booking problem" - it is possible for two people to book the same seats:

1. Seat 3A returned to Sorcha (findFirst)
2. Seat 3A returned to Ellen (findFirst)
3. Seat 3A claimed by Sorcha (update)
4. Seat 3A claimed by Ellen (update - overwrites Sorcha's claim)

Even though Sorcha has successfully booked the seat, the system ultimately stores Ellen's claim.

In essence, it’s read-modify-write problem in the database for the concurrency. The most straightforward way to resolve this issue is to utilize the database lock. However, while locking is not inherently bad, it can lead to unintended consequences in high-concurrency environments, even if you are only locking individual rows for a short amount of time.

On the other hand, avoiding locks in an application with a high number of concurrent requests makes the application more resilient to load and more scalable overall.

Let’s see how we can achieve that.

Optimistic Concurrency Control(OCC)

If it is possible to detect that a record has changed between being read and written, we can throw an error and fail the current request. This is known as the Optimistic Concurrency Control (OCC) model for handling concurrent operations on a single entity without relying on locking.

In order to achieve so, we need to add a concurrency token (a timestamp or version field). Let’s add a Version field in the Seat model:

model Seat {
    id Int @id @default(autoincrement())
    userId Int?
    claimedBy User? @relation(fields: [userId], references: [id])
    version Int
}

Next, let’s check if the version field before updating:

// Only mark the seat as claimed if the availableSeat.version
// matches the version we're updating. Additionally, increment the
// version when we perform this update so all other clients trying
// to book this same seat will have an outdated version.
await client.seat.update({
    data: {
        userId: userId,
        version: {
            increment: 1,
        },
    },
    where: {
        id: availableSeat.id,
        // This version field is the key
        // only claim seat if in-memory version matches 
        // database version, indicating that the field has not 
        // been updated
        version: availableSeat.version,
    },
});

It is now impossible for two people to book the same seat:

1. Seat 3A returned to Sorcha (version is 0)
2. Seat 3A returned to Ellen (version is 0)
3. Seat 3A claimed by Sorcha (version is incremented to 1, booking succeeds)
4. Seat 3A claimed by Ellen (in-memory version (0) does not match database version (1) - booking does not succeed)

You can verify it by running the modified version npm run occ

{ successUpdatedCallCount: 824, actualUpdatedCount: 824 }

The result indicates that only 824 seats were claimed. This means that 376 (1200-824) people will see the "ticket gone" page as shown in the beginning. While this may not be a big deal, affected individuals could refresh the page and choose another ticket, hoping not to see the "ticket gone" page again. 😂

Actually, this approach is specified in Prisma's official documentation. You can find more details there.

https://www.prisma.io/docs/guides/performance-and-optimization/prisma-client-transactions-guide#optimistic-concurrency-control

ZenStack Approach

Although the approach is already neat, it is important to always remember to add the version check in the where filter. Fortunately, the access policy of ZenStack can make this easier. You can add an update policy rule in the Seat model:

model Seat {
    id Int @id @default(autoincrement())
    userId Int?
    claimedBy User? @relation(fields: [userId], references: [id])
    version Int

    @@allow("read", true)
    @@allow("update", future().version == 1)
}

Then the code could then be simplified to:

await client.seat.update({
        data: {
            userId: userId,
            version: {
                increment: 1,
            },
        },
        where: {
            id: availableSeat.id,
        },
});

You can verify the result by running npm run zen :

{ updatedCountByUpdateMany: 587, actualUpdatedCount: 587 }

If you are interested in how it works under the hood, check out our GitHub repro:

https://github.com/zenstackhq/zenstack