Prisma Transactions — Atomic Database Operations
In this tutorial, you will learn about Prisma Transactions. We cover key concepts, practical examples, and best practices to help you master this topic.
Prisma transactions enable atomic execution of multiple database operations, ensuring that either all changes are committed or all changes are rolled back, maintaining data consistency.
What You'll Learn
By the end of this lesson you will use the $transaction API for interactive and batched transactions, handle transaction errors with rollback, implement nested writes atomically, and use Isolation Levels.
Why It Matters
Many business operations require multiple database changes that must either all succeed or all fail. Transferring money, creating orders, or processing jobs without transactions can leave data in an inconsistent state.
Real-World Use
DodaZIP uses a transaction when processing a file: it updates the file status to PROCESSING, creates a processing_job record, and deducts the user's processing quota. If any step fails, all changes are rolled back.
Interactive Transactions
Execute multiple operations atomically.
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
async function transferFunds(fromId: number, toId: number, amount: number) {
const result = await prisma.$transaction(async (tx) => {
// All operations use `tx` instead of `prisma`
const sender = await tx.account.findUniqueOrThrow({
where: { id: fromId },
});
if (sender.balance < amount) {
throw new Error('Insufficient funds');
}
await tx.account.update({
where: { id: fromId },
data: { balance: { decrement: amount } },
});
await tx.account.update({
where: { id: toId },
data: { balance: { increment: amount } },
});
const receiver = await tx.account.findUnique({
where: { id: toId },
});
return { from: sender.balance - amount, to: receiver!.balance };
});
return result;
}
// Usage
const balances = await transferFunds(1, 2, 100);
console.log('Transfer complete:', balances);
# interactive_tx.py
# Interactive transaction patterns
def interactive_tx():
print("Interactive Transaction:")
print()
print("prisma.$transaction(async (tx) => {")
print(" const sender = await tx.account.findUnique(...)")
print(" await tx.account.update(...) // debit")
print(" await tx.account.update(...) // credit")
print("})")
print()
print("Key points:")
print(" - Use 'tx' (transaction client) for all operations")
print(" - Throw an error to abort and rollback")
print(" - Return a value from the callback")
print(" - All operations commit or all rollback")
interactive_tx()
Batch Transactions
Execute independent operations atomically.
// Batch transaction (optimized for independent operations)
const [user, post, count] = await prisma.$transaction([
prisma.user.create({
data: { email: 'new@test.com', name: 'New' }
}),
prisma.post.create({
data: { title: 'Auto Post', content: 'Content' },
}),
prisma.user.count(),
]);
console.log('User created:', user.id);
console.log('Post created:', post.id);
console.log('Total users:', count);
// Batch with error handling
try {
const results = await prisma.$transaction(
updates.map((update) =>
prisma.file.update({
where: { id: update.id },
data: { status: update.status },
})
)
);
console.log(`Updated ${results.length} files`);
} catch (error) {
console.error('Transaction failed:', error);
// All changes are rolled back
}
# batch_tx.py
# Batch transaction patterns
def batch_tx():
print("Batch Transaction:")
print()
print("Use for independent operations:")
print(" prisma.$transaction([")
print(" prisma.user.create(...),")
print(" prisma.post.create(...),")
print(" prisma.user.count()")
print(" ])")
print()
print("Use for batch updates:")
print(" prisma.$transaction(")
print(" updates.map(u => prisma.file.update({ where: { id: u.id }, data: u }))")
print(" )")
print()
print("Limitations:")
print(" - Each operation is independent")
print(" - Cannot use results from one operation in another")
print(" - For dependencies, use interactive transaction")
batch_tx()
Nested Writes
Create or update related data atomically.
// Nested writes are handled atomically by Prisma
const user = await prisma.user.create({
data: {
email: 'alice@test.com',
name: 'Alice',
profile: {
create: { bio: 'Developer' },
},
posts: {
createMany: {
data: [
{ title: 'Post 1', content: 'First post' },
{ title: 'Post 2', content: 'Second post' },
],
},
},
},
include: {
profile: true,
posts: true,
},
});
// Nested update
const updatedUser = await prisma.user.update({
where: { id: 1 },
data: {
name: 'Alice Updated',
posts: {
updateMany: {
where: { published: false },
data: { published: true },
},
},
},
});
# nested_writes.py
# Nested write atomicity
def nested_writes():
print("Nested Writes (Atomic):")
print()
print("Create with relations:")
print(" prisma.user.create({")
print(" data: {")
print(" email: 'test@test.com',")
print(" profile: { create: { bio: 'Dev' } },")
print(" posts: { create: [{ title: 'Post' }] }")
print(" }")
print(" })")
print()
print("Nested update:")
print(" prisma.user.update({")
print(" where: { id: 1 },")
print(" data: {")
print(" posts: {")
print(" updateMany: {")
print(" where: { published: false },")
print(" data: { published: true }")
print(" }")
print(" }")
print(" }")
print(" })")
nested_writes()
Error Handling
Handle transaction failures and rollbacks.
async function safeTransaction() {
try {
const result = await prisma.$transaction(async (tx) => {
const account = await tx.account.findUnique({
where: { id: accountId },
});
if (!account) {
throw new Error('Account not found');
}
if (account.balance < amount) {
throw new Error('Insufficient funds');
}
await tx.account.update({
where: { id: accountId },
data: { balance: { decrement: amount } },
});
return { success: true, newBalance: account.balance - amount };
});
return result;
} catch (error) {
if (error instanceof Prisma.PrismaClientKnownRequestError) {
if (error.code === 'P2025') {
console.error('Record not found during transaction');
}
}
console.error('Transaction failed:', error);
return { success: false, error: error.message };
}
}
# tx_errors.py
# Transaction error handling
def tx_error_handling():
print("Transaction Error Handling:")
print()
print("Common error scenarios:")
print(" 1. Record not found (P2025)")
print(" 2. Unique constraint violation (P2002)")
print(" 3. Foreign key constraint (P2003)")
print(" 4. Transaction timeout")
print(" 5. Deadlock detection")
print()
print("Pattern:")
print(" try {")
print(" await prisma.$transaction(async (tx) => { ... })")
print(" } catch (error) {")
print(" // Transaction already rolled back")
print(" // Handle error, log, retry if appropriate")
print(" }")
print()
print("Note: Prisma automatically rolls back on error")
print("No manual rollback needed")
tx_error_handling()
Common Mistakes
Using prisma instead of tx inside transactions: Inside interactive transactions, always use the tx client. Using prisma bypasses the transaction boundary.
Not handling errors in transactions: An unhandled error inside the transaction callback causes a rollback. Always wrap in try/catch.
Making too many operations in one transaction: Long-running transactions hold database locks. Keep transactions short and focused.
Forgetting that batch transactions are independent: In batch transactions, each operation cannot depend on the result of another. Use interactive transactions for dependencies.
Not considering isolation levels: Default isolation level (PostgreSQL: Read Committed) may not be sufficient for all use cases. Configure isolation levels as needed.
Practice Questions
What is the difference between interactive and batch transactions? Interactive uses a callback with a transaction client. Batch runs independent operations atomically.
What happens when an error is thrown inside an interactive transaction? Prisma automatically rolls back all changes made within that transaction.
How do you access transaction results? Return a value from the callback (interactive) or destructure the results array (batch).
Why should you use tx instead of prisma inside a transaction? Using prisma creates separate connections that are not part of the transaction.
Challenge: Implement an order creation service that atomically creates an order, decrements product inventory, charges the customer, and handles failures at each step.
FAQ
Mini Project
Create an order processing service that uses transactions to atomically create order records, update inventory counts, Process payment status, and generate order confirmation, with proper error handling and rollback.
def order_tx_flow():
print("Order Transaction Flow:")
print()
print("prisma.$transaction(async (tx) => {")
print(" 1. tx.order.create({ data: orderData })")
print(" 2. tx.inventory.update({ where: { productId }, data: { stock: decrement(1) } })")
print(" 3. tx.payment.create({ data: paymentData })")
print(" 4. tx.notification.create({ data: notificationData })")
print(" return { orderId, paymentId }")
print("})")
print()
print("If any step fails, all changes are rolled back.")
print("No partial orders, no phantom inventory changes.")
order_tx_flow()
What's Next
Next: Prisma Raw Queries for custom SQL.
Built by the developers of DodaTech
Doda Browser, DodaZIP & Durga Antivirus Pro