Transactions ensure your database operations are reliable and consistent.
ACID Properties#
- Atomicity: All operations succeed or all fail
- Consistency: Database moves from one valid state to another
- Isolation: Concurrent transactions don't interfere
- Durability: Committed changes persist
Basic Transaction with Prisma#
1// Transfer money between accounts
2async function transferMoney(
3 fromId: string,
4 toId: string,
5 amount: number
6) {
7 return prisma.$transaction(async (tx) => {
8 // Deduct from source
9 const from = await tx.account.update({
10 where: { id: fromId },
11 data: { balance: { decrement: amount } },
12 });
13
14 if (from.balance < 0) {
15 throw new Error('Insufficient funds');
16 }
17
18 // Add to destination
19 await tx.account.update({
20 where: { id: toId },
21 data: { balance: { increment: amount } },
22 });
23
24 // Create transfer record
25 return tx.transfer.create({
26 data: { fromId, toId, amount },
27 });
28 });
29}Isolation Levels#
1// Read Committed (default) - sees committed data
2await prisma.$transaction(
3 async (tx) => { /* ... */ },
4 { isolationLevel: 'ReadCommitted' }
5);
6
7// Serializable - strictest, prevents all anomalies
8await prisma.$transaction(
9 async (tx) => { /* ... */ },
10 { isolationLevel: 'Serializable' }
11);Handling Concurrent Updates#
1// Optimistic locking with version field
2async function updateProduct(id: string, data: ProductUpdate, version: number) {
3 const result = await prisma.product.updateMany({
4 where: { id, version },
5 data: { ...data, version: { increment: 1 } },
6 });
7
8 if (result.count === 0) {
9 throw new Error('Product was modified by another user');
10 }
11}
12
13// Pessimistic locking with SELECT FOR UPDATE
14async function reserveInventory(productId: string, quantity: number) {
15 return prisma.$transaction(async (tx) => {
16 // Lock the row
17 const [product] = await tx.$queryRaw`
18 SELECT * FROM products
19 WHERE id = ${productId}
20 FOR UPDATE
21 `;
22
23 if (product.stock < quantity) {
24 throw new Error('Insufficient stock');
25 }
26
27 await tx.product.update({
28 where: { id: productId },
29 data: { stock: { decrement: quantity } },
30 });
31 });
32}Saga Pattern for Distributed Transactions#
1// When you can't use database transactions across services
2async function createOrder(orderData: OrderInput) {
3 const compensations: (() => Promise<void>)[] = [];
4
5 try {
6 // Step 1: Reserve inventory
7 await inventoryService.reserve(orderData.items);
8 compensations.push(() => inventoryService.release(orderData.items));
9
10 // Step 2: Charge payment
11 const payment = await paymentService.charge(orderData.payment);
12 compensations.push(() => paymentService.refund(payment.id));
13
14 // Step 3: Create order
15 const order = await orderService.create(orderData);
16 compensations.push(() => orderService.cancel(order.id));
17
18 // Step 4: Send confirmation
19 await emailService.sendConfirmation(order);
20
21 return order;
22 } catch (error) {
23 // Compensate in reverse order
24 for (const compensate of compensations.reverse()) {
25 try {
26 await compensate();
27 } catch (e) {
28 logger.error('Compensation failed', e);
29 }
30 }
31 throw error;
32 }
33}Batch Operations#
1// Multiple operations in single transaction
2async function createUserWithProfile(userData: UserInput) {
3 return prisma.$transaction([
4 prisma.user.create({ data: userData }),
5 prisma.profile.create({
6 data: { userId: userData.id, bio: '' },
7 }),
8 prisma.settings.create({
9 data: { userId: userData.id, notifications: true },
10 }),
11 ]);
12}Deadlock Prevention#
1// Always acquire locks in consistent order
2async function transferBetweenAccounts(id1: string, id2: string, amount: number) {
3 // Sort IDs to ensure consistent lock order
4 const [firstId, secondId] = [id1, id2].sort();
5
6 return prisma.$transaction(async (tx) => {
7 await tx.$queryRaw`SELECT * FROM accounts WHERE id = ${firstId} FOR UPDATE`;
8 await tx.$queryRaw`SELECT * FROM accounts WHERE id = ${secondId} FOR UPDATE`;
9
10 // Now safely update both accounts
11 });
12}Retry Logic#
1async function withRetry<T>(
2 fn: () => Promise<T>,
3 maxAttempts = 3
4): Promise<T> {
5 for (let attempt = 1; attempt <= maxAttempts; attempt++) {
6 try {
7 return await fn();
8 } catch (error) {
9 if (
10 error.code === 'P2034' || // Transaction conflict
11 error.message.includes('deadlock')
12 ) {
13 if (attempt === maxAttempts) throw error;
14 await new Promise(r => setTimeout(r, Math.random() * 100));
15 continue;
16 }
17 throw error;
18 }
19 }
20 throw new Error('Max attempts reached');
21}
22
23// Usage
24await withRetry(() => transferMoney(from, to, 100));Use transactions for related operations, choose appropriate isolation levels, and handle concurrent access properly.