Transactions ensure data integrity when multiple operations must succeed or fail together. Understanding ACID properties and isolation levels is essential for reliable applications.
ACID Properties#
Atomicity
- All operations succeed or all fail
- No partial updates
Consistency
- Database moves from valid state to valid state
- Constraints are maintained
Isolation
- Concurrent transactions don't interfere
- Each transaction sees consistent data
Durability
- Committed changes persist
- Survives system failures
Basic Transactions#
1// Prisma transactions
2async function transferFunds(
3 fromAccountId: string,
4 toAccountId: string,
5 amount: number
6) {
7 return await prisma.$transaction(async (tx) => {
8 // Deduct from source account
9 const source = await tx.account.update({
10 where: { id: fromAccountId },
11 data: { balance: { decrement: amount } },
12 });
13
14 if (source.balance < 0) {
15 throw new Error('Insufficient funds');
16 }
17
18 // Add to destination account
19 await tx.account.update({
20 where: { id: toAccountId },
21 data: { balance: { increment: amount } },
22 });
23
24 // Create transfer record
25 return await tx.transfer.create({
26 data: {
27 fromAccountId,
28 toAccountId,
29 amount,
30 status: 'completed',
31 },
32 });
33 });
34}1// Raw SQL with pg
2async function transferFundsRaw(
3 fromId: string,
4 toId: string,
5 amount: number
6) {
7 const client = await pool.connect();
8
9 try {
10 await client.query('BEGIN');
11
12 // Lock rows to prevent concurrent updates
13 const { rows } = await client.query(
14 'SELECT * FROM accounts WHERE id = $1 FOR UPDATE',
15 [fromId]
16 );
17
18 if (rows[0].balance < amount) {
19 throw new Error('Insufficient funds');
20 }
21
22 await client.query(
23 'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
24 [amount, fromId]
25 );
26
27 await client.query(
28 'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
29 [amount, toId]
30 );
31
32 await client.query('COMMIT');
33 } catch (error) {
34 await client.query('ROLLBACK');
35 throw error;
36 } finally {
37 client.release();
38 }
39}Isolation Levels#
1-- Read Uncommitted (lowest isolation)
2-- Can see uncommitted changes from other transactions
3SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4
5-- Read Committed (PostgreSQL default)
6-- Only sees committed changes
7SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
8
9-- Repeatable Read
10-- Same query returns same results within transaction
11SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
12
13-- Serializable (highest isolation)
14-- Transactions execute as if serial
15SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;1// Prisma with isolation level
2await prisma.$transaction(
3 async (tx) => {
4 // Operations here
5 },
6 {
7 isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
8 maxWait: 5000,
9 timeout: 10000,
10 }
11);Isolation Level Problems#
Dirty Read:
- Reading uncommitted changes
- Prevented by: Read Committed and above
Non-Repeatable Read:
- Same query returns different results
- Prevented by: Repeatable Read and above
Phantom Read:
- New rows appear between queries
- Prevented by: Serializable
| Level | Dirty Read | Non-Repeatable | Phantom |
|-----------------|------------|----------------|---------|
| Read Uncommitted| Yes | Yes | Yes |
| Read Committed | No | Yes | Yes |
| Repeatable Read | No | No | Yes |
| Serializable | No | No | No |
Optimistic vs Pessimistic Locking#
1// Optimistic locking with version field
2async function updateProductOptimistic(
3 id: string,
4 data: ProductUpdate,
5 expectedVersion: number
6) {
7 const result = await prisma.product.updateMany({
8 where: {
9 id,
10 version: expectedVersion,
11 },
12 data: {
13 ...data,
14 version: { increment: 1 },
15 },
16 });
17
18 if (result.count === 0) {
19 throw new Error('Concurrent modification detected');
20 }
21
22 return await prisma.product.findUnique({ where: { id } });
23}
24
25// Usage with retry
26async function updateWithRetry(
27 id: string,
28 updateFn: (product: Product) => ProductUpdate,
29 maxRetries = 3
30) {
31 for (let attempt = 0; attempt < maxRetries; attempt++) {
32 const product = await prisma.product.findUnique({ where: { id } });
33 if (!product) throw new Error('Product not found');
34
35 try {
36 return await updateProductOptimistic(
37 id,
38 updateFn(product),
39 product.version
40 );
41 } catch (error) {
42 if (attempt === maxRetries - 1) throw error;
43 // Retry on conflict
44 }
45 }
46}1// Pessimistic locking with SELECT FOR UPDATE
2async function updateProductPessimistic(
3 id: string,
4 data: ProductUpdate
5) {
6 return await prisma.$transaction(async (tx) => {
7 // Lock the row
8 const [product] = await tx.$queryRaw`
9 SELECT * FROM products WHERE id = ${id} FOR UPDATE
10 `;
11
12 if (!product) throw new Error('Product not found');
13
14 return await tx.product.update({
15 where: { id },
16 data,
17 });
18 });
19}Deadlock Prevention#
1// ❌ Prone to deadlock
2async function badTransfer(fromId: string, toId: string, amount: number) {
3 await prisma.$transaction(async (tx) => {
4 await tx.account.update({ where: { id: fromId }, data: { /* ... */ } });
5 await tx.account.update({ where: { id: toId }, data: { /* ... */ } });
6 });
7}
8// If two transfers happen: A→B and B→A, deadlock possible
9
10// ✅ Order locks consistently
11async function goodTransfer(fromId: string, toId: string, amount: number) {
12 // Always lock in consistent order (e.g., by ID)
13 const [firstId, secondId] = [fromId, toId].sort();
14
15 await prisma.$transaction(async (tx) => {
16 await tx.$queryRaw`
17 SELECT * FROM accounts WHERE id IN (${firstId}, ${secondId})
18 ORDER BY id FOR UPDATE
19 `;
20
21 await tx.account.update({ where: { id: fromId }, data: { balance: { decrement: amount } } });
22 await tx.account.update({ where: { id: toId }, data: { balance: { increment: amount } } });
23 });
24}Savepoints#
1// Partial rollback with savepoints
2async function complexOperation() {
3 const client = await pool.connect();
4
5 try {
6 await client.query('BEGIN');
7
8 // Main operation
9 await client.query('INSERT INTO orders ...');
10
11 // Create savepoint before risky operation
12 await client.query('SAVEPOINT before_email');
13
14 try {
15 await sendOrderEmail();
16 await client.query('INSERT INTO email_log ...');
17 } catch (error) {
18 // Rollback only email part, keep order
19 await client.query('ROLLBACK TO SAVEPOINT before_email');
20 console.log('Email failed, continuing without');
21 }
22
23 await client.query('COMMIT');
24 } catch (error) {
25 await client.query('ROLLBACK');
26 throw error;
27 } finally {
28 client.release();
29 }
30}Distributed Transactions#
1// Saga pattern for distributed transactions
2class TransferSaga {
3 private steps: SagaStep[] = [];
4
5 async execute() {
6 const completedSteps: SagaStep[] = [];
7
8 try {
9 for (const step of this.steps) {
10 await step.execute();
11 completedSteps.push(step);
12 }
13 } catch (error) {
14 // Compensate in reverse order
15 for (const step of completedSteps.reverse()) {
16 await step.compensate();
17 }
18 throw error;
19 }
20 }
21}
22
23// Usage
24const saga = new TransferSaga();
25
26saga.addStep({
27 execute: () => accountService.debit(fromAccount, amount),
28 compensate: () => accountService.credit(fromAccount, amount),
29});
30
31saga.addStep({
32 execute: () => accountService.credit(toAccount, amount),
33 compensate: () => accountService.debit(toAccount, amount),
34});
35
36saga.addStep({
37 execute: () => notificationService.send(notification),
38 compensate: () => {}, // Notifications can't be unsent
39});
40
41await saga.execute();Best Practices#
DO:
✓ Keep transactions short
✓ Lock in consistent order
✓ Use appropriate isolation level
✓ Handle deadlocks with retry
✓ Use optimistic locking for low-contention
DON'T:
✗ Hold transactions during external calls
✗ Use serializable for everything
✗ Ignore transaction timeouts
✗ Nest transactions unnecessarily
Conclusion#
Transactions are your safety net for data integrity. Choose the right isolation level, prevent deadlocks through consistent ordering, and keep transactions as short as possible.
For distributed systems, consider saga patterns instead of distributed transactions.