Use Prisma and PostgresDB in a prevent ticket oversold example

[Lomanu4]

This is a quick demo for using the PostgresDB’s isolationLevel to prevent ticket oversold. Full code can be found

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

We have this schema set up in schema.prisma:


model User {
id Int @id @default(autoincrement())
name String
tickets Ticket[]
}

model Event {
id Int @id @default(autoincrement())
title String
ticketClasses TicketClass[]
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}

model TicketClass {
id Int @id @default(autoincrement())
title String
limit Int
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
eventId Int
event Event @relation(fields: [eventId], references: [id])
tickets Ticket[]
}

model Ticket {
id Int @id @default(autoincrement())
ticketClassId Int
ticketClass TicketClass @relation(fields: [ticketClassId], references: [id])
userId Int
user User @relation(fields: [userId], references: [id])
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}

Each Event can have multiple TicketClasses, e.g. "one-day pass", "two-day pass". Each TicketClass has a limit field that indicate the maximum number of tickets this TicketClass is allowed to be sold. When a User purchase a ticket of a TicketClass, a Ticket record will be created and linked to that TicketClass. Before each purchase, we should count the number of Tickets by the TicketClassId, if it equals limit, we should prevent the purchase.

This is the purchaseTicket implementation:


export const purchaseTicket = async ({
userId,
ticketClassId,
slow = true,
}) => {
const ticketClass = await prisma.ticketClass.findUniqueOrThrow({
where: {
id: ticketClassId,
},
});

const soldTickets = await prisma.ticket.count({
where: {
ticketClassId,
},
});

// Introduce an artificial delay
await new Promise((resolve) => setTimeout(resolve, slow ? 100 : 0));

if (soldTickets >= ticketClass.limit) {
throw new Error(
`${ticketClass.title} is sold out, user ${userId} cannot purchase ticket`,
);
}

const ticket = await prisma.ticket.create({
data: {
userId,
ticketClassId,
},
});

return ticket;
};

We then seed the data:


import prisma from "./index.js";

const seed = async () => {
const event = await prisma.event.create({
data: {
title: "My Event",
},
});

const ticketClass = await prisma.ticketClass.create({
data: {
eventId: event.id,
title: "One-day Pass",
limit: 2,
},
});

await Promise.all(
["Sam", "Bob", "Jane", "May"].map((name) =>
prisma.user.create({
data: {
name,
},
}),
),
);
};

seed();

We create a race condition to purchase 4 tickets at the same time under the "one-day pass" TicketClass which only has 2 tickets available:


await Promise.all([
purchaseTicket({ userId: 1, ticketClassId: 1 }).catch(console.error),
purchaseTicket({ userId: 2, ticketClassId: 1 }).catch(console.error),
purchaseTicket({ userId: 3, ticketClassId: 1 }).catch(console.error),
purchaseTicket({ userId: 4, ticketClassId: 1 }).catch(console.error),
]);

We run npm run reset-db followed by node index.js. With current purchaseTicket function, the TicketClass will oversold:

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

Then we switch to using purchaseTicketTransaction. Here we use Prisma's $transaction() and provide a callback which has the same implementation as purchaseTicket, and set isolationLevel to Serializable:


export const purchaseTicketTransaction = async ({
userId,
ticketClassId,
slow = true,
}) => {
try {
const ticket = await prisma.$transaction(
async (tx) => {
const ticketClass = await tx.ticketClass.findUniqueOrThrow({
where: {
id: ticketClassId,
},
});

const soldTickets = await tx.ticket.count({
where: {
ticketClassId,
},
});

// Introduce an artificial delay
await new Promise((resolve) => setTimeout(resolve, slow ? 100 : 0));

if (soldTickets >= ticketClass.limit) {
throw new Error(
`${ticketClass.title} is sold out, user ${userId} cannot purchase ticket`,
);
}

const ticket = await tx.ticket.create({
data: {
userId,
ticketClassId,
},
});

return ticket;
},
{ isolationLevel: "Serializable" },
);
return ticket;
} catch (error) {
throw new Error(
`user ${userId} cannot purchase ticket, reason: ${error.message}`,
);
}
};

async function main() {
await Promise.all([
purchaseTicketTransaction({ userId: 1, ticketClassId: 1 }),
purchaseTicketTransaction({ userId: 2, ticketClassId: 1 }),
purchaseTicketTransaction({ userId: 3, ticketClassId: 1 }),
purchaseTicketTransaction({ userId: 4, ticketClassId: 1 }),
]);
}

We reset the db npm run reset-db and run node index.js again.

This time, only one purchase go through:

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

And the rest got error thrown:


Error: user 1 cannot purchase ticket, reason: Transaction failed due to a write conflict or a deadlock. Please retry your transaction
at purchaseTicketTransaction (file:///Users/sum/Codes/postgres-playground/handlers.js:81:11)
at async Promise.all (index 0)
at async main (file:///Users/sum/Codes/postgres-playground/index.js:4:3)
Error: user 2 cannot purchase ticket, reason: Transaction failed due to a write conflict or a deadlock. Please retry your transaction
at purchaseTicketTransaction (file:///Users/sum/Codes/postgres-playground/handlers.js:81:11)
at async Promise.all (index 1)
at async main (file:///Users/sum/Codes/postgres-playground/index.js:4:3)
Error: user 4 cannot purchase ticket, reason: Transaction failed due to a write conflict or a deadlock. Please retry your transaction
at purchaseTicketTransaction (file:///Users/sum/Codes/postgres-playground/handlers.js:81:11)
at async Promise.all (index 3)
at async main (file:///Users/sum/Codes/postgres-playground/index.js:4:3)

This is interesting because the one-day pass TickeClass has its limit set to 2. Presumably 2 purchases should go through. But due to the transaction and isolation level set to prevent oversold, we can only allow one purchase to go through at a specific time.

We need to balance oversold and user experience.

Learn more about Prisma's interactive transactions:

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

Example code:

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.