How To Build a High-Concurrency Ticket Booking System With Prisma

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

Comments

[Tony Do]

Great post, I am still a newbie to concurrency control but your post inspired me a lot. As a web developer, I see most of people only focus on single customer flow (ecommerce, booking, retailing, etc.), which is highly not applicable in real world

[Glenn Latomme]

@jiasheng What is the difference with your OCC and zenstack example? you mention that is "simplified" but it looks the same, except some values in the prisma schema are added :)

[JS]

Thanks for pointing it out! That's a silly copy/paste error I made.😂

There is an additional version filter you need to add in the occ version:

// 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,

[Lynn Mikami]

Wow, nice read!

[Vaibhav Pathak]

I think I can just write a simple pure SQL transactions without thinking too much about an extra column overhead and then using zenstack to manage it.

I don't understand why people are keep on creating multiple different overhead and things to manage if that thing is just simply possible. Still if you need types just use kysely and build a query with FOR UPDATE and that's it.

Second of all the all these RBDMs actually handles these kind of transactions pessimistically so I don't think you need to handle this again optimistically and still if you want to do you can mark the transaction as serializable and handle it as a retry.

[JS]

Thank you for sharing your perspective. Using row-level locking or serializable transactions, as you mentioned, is indeed a great solution. However, I also believe that OCC (Optimistic Concurrency Control) has its advantages in terms of performance and applicability. There is no one-size-fits-all answer in the software development world, and I think that's the beauty of the software development world

[Vaibhav Pathak]

Software development is great and I understand everything has it's own tradeoffs but still there are many points I always consider before deploying a solution (specially for database layer work) in my application layer.

First of all, I will think about: can my database directly handle this things, if yes, is it better, fast and easier or which database will provide me a better solution for this architecture.

The real beauty of software development world is that there are so many ways to implement an architecture that's what I love about it and as I said everything has it's own tradeoffs.

I think if you want Optimistic concurrency control I would prefer using something like MongoDB or Casandra, even I can go with MySQL or Postgres. Doesn't real matter here (other then MongoDB) because I don't really have to think about the management of this new dependency and don't even need to think about the internal implementations.

This is my opinion, probably your have different takes on this thing.

[JS]

While databases can handle tasks effectively, some individuals opt to include everything in their source code. It's similar to why fewer people are still using stored procedures nowadays, despite the benefits they offer. Additionally, I believe that is why our users prefer ZenStack over RLS of Postgres.

[Vaibhav Pathak]

Understandable

[Balaji]

Can we use userId= null condition instead of version?

[JS]

It won't work because the operation needs to be non-idempotent, like the increment for version, in order to differentiate between different calls. Therefore If use userId= null, it is impossible to determine if the operation has been called once or multiple times.