Transactions & Concurrency Fundamentals

A beginner-friendly guide to keeping your data safe and consistent when multiple users access your database simultaneously.

---

Table of Contents

1. What Are Transactions?
2. Basic Transaction Usage
3. Common Concurrency Problems
4. Preventing Race Conditions
5. Avoiding Deadlocks
6. Real-World Example
7. Quick Reference

---

1. What Are Transactions?

A transaction is a group of database operations that either all succeed or all fail together. Think of it as an "all-or-nothing" deal.

Why Use Transactions?

Without Transaction (Dangerous!):

// Transfer money between accounts
await knex('accounts').where('id', 1).decrement('balance', 100);
// 💥 App crashes here!
await knex('accounts').where('id', 2).increment('balance', 100);
// Never happens - money disappears!

With Transaction (Safe!):

// Either both happen or neither happens
await knex.transaction(async (trx) => {
  await trx('accounts').where('id', 1).decrement('balance', 100);
  // If crash happens, both operations rollback
  await trx('accounts').where('id', 2).increment('balance', 100);
});

The ACID Guarantee

Transactions provide ACID properties:

• Atomic - All operations succeed or all fail (no partial updates)
• Consistent - Database stays in valid state
• Isolated - Transactions don't interfere with each other
• Durable - Committed data is permanently saved

---

2. Basic Transaction Usage

Simple Transaction Syntax

// Method 1: Callback (Recommended - Auto-commit/rollback)
async function transferMoney(fromId, toId, amount) {
  try {
    const result = await knex.transaction(async (trx) => {
      // All queries use 'trx' instead of 'knex'
      await trx('accounts').where('id', fromId).decrement('balance', amount);
      await trx('accounts').where('id', toId).increment('balance', amount);
      
      return { success: true };
      // ✅ Auto-commits if no error thrown
    });
    
    console.log('Transfer successful:', result);
    
  } catch (error) {
    // ❌ Auto-rollback on any error
    console.error('Transfer failed:', error.message);
  }
}

Manual Transaction Control

// Method 2: Manual (More control but more verbose)
async function transferMoneyManual(fromId, toId, amount) {
  const trx = await knex.transaction();
  
  try {
    await trx('accounts').where('id', fromId).decrement('balance', amount);
    await trx('accounts').where('id', toId).increment('balance', amount);
    
    await trx.commit(); // ✅ Manually commit
    console.log('Transaction committed');
    
  } catch (error) {
    await trx.rollback(); // ❌ Manually rollback
    console.error('Transaction rolled back:', error.message);
    throw error;
  }
}

Best Practice: Use the callback method (Method 1) - it's simpler and safer.

---

3. Common Concurrency Problems

When multiple users access the same data simultaneously, problems can occur.

Problem 1: Lost Updates

Two users read the same value and both update it, causing one update to be lost.

// ❌ PROBLEM: Lost Update
async function withdrawMoney(accountId, amount) {
  // User A and User B both read balance = $1000
  const account = await knex('accounts').where('id', accountId).first();
  console.log('Balance:', account.balance); // Both see $1000
  
  // Some processing time...
  await new Promise(r => setTimeout(r, 1000));
  
  // Both calculate new balance and update
  const newBalance = account.balance - amount;
  await knex('accounts').where('id', accountId).update({ balance: newBalance });
}

// If both withdraw $100, final balance might be $900 instead of $800!
await Promise.all([
  withdrawMoney(1, 100), // Thinks balance is $1000, sets to $900
  withdrawMoney(1, 100)  // Also thinks balance is $1000, sets to $900
]);

Problem 2: Race Conditions

Multiple operations happen simultaneously, causing unexpected results.

// ❌ PROBLEM: Race Condition
async function purchaseProduct(productId, userId) {
  // Check if product is in stock
  const product = await knex('products').where('id', productId).first();
  
  if (product.stock > 0) {
    console.log(`User ${userId}: Stock available!`);
    
    // Some processing time...
    await new Promise(r => setTimeout(r, 100));
    
    // Decrement stock
    await knex('products').where('id', productId).decrement('stock', 1);
    console.log(`User ${userId}: Purchase successful!`);
  }
}

// 10 users try to buy the last item simultaneously
// Multiple users pass the stock check before any decrement happens
// Result: Negative stock! 😱
await Promise.all([1, 2, 3, 4, 5, 6, 7, 8, 9, 10].map(id => 
  purchaseProduct(1, id)
));

Problem 3: Deadlocks

Two transactions wait for each other to release locks, causing both to freeze.

// ❌ PROBLEM: Deadlock
// Transaction 1: Lock Account A, then try to lock Account B
// Transaction 2: Lock Account B, then try to lock Account A
// Both wait forever! 💀

await Promise.all([
  knex.transaction(async (trx) => {
    await trx('accounts').where('id', 1).forUpdate().first(); // Lock A
    await trx('accounts').where('id', 2).forUpdate().first(); // Wait for B
  }),
  knex.transaction(async (trx) => {
    await trx('accounts').where('id', 2).forUpdate().first(); // Lock B
    await trx('accounts').where('id', 1).forUpdate().first(); // Wait for A
  })
]);

---

4. Preventing Race Conditions

Use forUpdate() to lock rows during a transaction, preventing other transactions from modifying them.

Using SELECT FOR UPDATE

// ✅ SOLUTION: Lock rows to prevent race conditions
async function withdrawMoneySafe(accountId, amount) {
  return knex.transaction(async (trx) => {
    // Lock the row - other transactions must wait
    const account = await trx('accounts')
      .where('id', accountId)
      .forUpdate() // 🔒 Locks this row
      .first();
    
    if (!account) {
      throw new Error('Account not found');
    }
    
    if (account.balance < amount) {
      throw new Error('Insufficient balance');
    }
    
    // Safe to update - we have exclusive lock
    await trx('accounts')
      .where('id', accountId)
      .decrement('balance', amount);
    
    console.log(`Withdrew ${amount}, new balance: ${account.balance - amount}`);
  });
}

// Now multiple withdrawals are safe - they happen one at a time
await Promise.all([
  withdrawMoneySafe(1, 100),
  withdrawMoneySafe(1, 200)
]);

Safe Product Purchase

// ✅ SOLUTION: Lock product during purchase
async function purchaseProductSafe(productId, userId) {
  return knex.transaction(async (trx) => {
    // Lock the product row
    const product = await trx('products')
      .where('id', productId)
      .forUpdate() // 🔒 Exclusive lock
      .first();
    
    if (!product) {
      throw new Error('Product not found');
    }
    
    if (product.stock <= 0) {
      throw new Error('Out of stock');
    }
    
    // Decrement stock safely
    await trx('products')
      .where('id', productId)
      .decrement('stock', 1);
    
    // Create order
    await trx('orders').insert({
      user_id: userId,
      product_id: productId
    });
    
    console.log(`User ${userId}: Purchased successfully`);
  });
}

// Now only available items can be purchased
await Promise.all([1, 2, 3, 4, 5].map(id => 
  purchaseProductSafe(1, id).catch(err => 
    console.log(`User ${id}: ${err.message}`)
  )
));

When to Use forUpdate()

✅ Use forUpdate() when:

• Reading data that you'll update based on its current value
• Checking availability before making changes (stock, seats, etc.)
• Preventing race conditions in critical operations

❌ Don't use forUpdate() for:

• Simple reads that don't lead to updates
• Reports or analytics queries
• High-traffic read-only operations (causes unnecessary blocking)

---

5. Avoiding Deadlocks

Deadlocks happen when transactions wait for each other. Here's how to prevent them.

Rule 1: Always Lock in the Same Order

// ❌ BAD: Locks in different order = Deadlock risk
async function transferBad() {
  await Promise.all([
    // Transaction 1: A → B
    knex.transaction(async (trx) => {
      await trx('accounts').where('id', 1).forUpdate().first();
      await trx('accounts').where('id', 2).forUpdate().first();
    }),
    // Transaction 2: B → A (opposite order!)
    knex.transaction(async (trx) => {
      await trx('accounts').where('id', 2).forUpdate().first();
      await trx('accounts').where('id', 1).forUpdate().first();
    })
  ]);
}

// ✅ GOOD: Always lock in ascending ID order
async function transferSafe(fromId, toId, amount) {
  return knex.transaction(async (trx) => {
    // Sort IDs to ensure consistent locking order
    const ids = [fromId, toId].sort((a, b) => a - b);
    
    // Lock both accounts in order
    const accounts = await trx('accounts')
      .whereIn('id', ids)
      .forUpdate()
      .select('*');
    
    const fromAccount = accounts.find(a => a.id === fromId);
    const toAccount = accounts.find(a => a.id === toId);
    
    if (fromAccount.balance < amount) {
      throw new Error('Insufficient balance');
    }
    
    // Perform transfer
    await trx('accounts').where('id', fromId).decrement('balance', amount);
    await trx('accounts').where('id', toId).increment('balance', amount);
    
    return { success: true };
  });
}

Rule 2: Keep Transactions Short

// ❌ BAD: Long transaction holding locks
async function processOrderBad(orderId) {
  return knex.transaction(async (trx) => {
    const order = await trx('orders').where('id', orderId).forUpdate().first();
    
    // 🐌 External API call while holding lock
    await fetch('https://payment-api.com/charge', { 
      method: 'POST',
      body: JSON.stringify({ amount: order.total })
    });
    
    // 🐌 Send email while holding lock
    await sendEmail(order.user_email, 'Order confirmed');
    
    await trx('orders').where('id', orderId).update({ status: 'paid' });
  });
}

// ✅ GOOD: Only hold locks for database operations
async function processOrderGood(orderId) {
  // Do external operations BEFORE transaction
  const order = await knex('orders').where('id', orderId).first();
  
  await fetch('https://payment-api.com/charge', { 
    method: 'POST',
    body: JSON.stringify({ amount: order.total })
  });
  
  // Quick transaction to update status
  return knex.transaction(async (trx) => {
    await trx('orders').where('id', orderId).update({ status: 'paid' });
  });
  
  // Send email AFTER transaction
  await sendEmail(order.user_email, 'Order confirmed');
}

Rule 3: Handle Deadlocks with Retry

// ✅ Retry logic for deadlocks
async function withDeadlockRetry(operation, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await operation();
      
    } catch (error) {
      // Check if it's a deadlock
      const isDeadlock = error.code === 'ER_LOCK_DEADLOCK';
      
      if (isDeadlock && attempt < maxRetries) {
        console.log(`Deadlock detected, retrying ${attempt}/${maxRetries}`);
        
        // Wait before retrying (exponential backoff)
        const delay = 100 * Math.pow(2, attempt - 1);
        await new Promise(r => setTimeout(r, delay));
        
        continue;
      }
      
      throw error; // Give up after max retries
    }
  }
}

// Usage
await withDeadlockRetry(async () => {
  return knex.transaction(async (trx) => {
    await trx('accounts').where('id', 1).forUpdate().decrement('balance', 100);
    await trx('accounts').where('id', 2).forUpdate().increment('balance', 100);
  });
});

---

6. Real-World Example

E-commerce Order Processing

Complete example showing transactions, locking, and error handling:

async function createOrder(userId, cartItems) {
  return knex.transaction(async (trx) => {
    // 1. Lock user account
    const user = await trx('users')
      .where('id', userId)
      .forUpdate()
      .first();
    
    if (!user) {
      throw new Error('User not found');
    }
    
    // 2. Lock all products (in ID order to prevent deadlocks)
    const productIds = cartItems
      .map(item => item.productId)
      .sort((a, b) => a - b);
    
    const products = await trx('products')
      .whereIn('id', productIds)
      .forUpdate()
      .select('*');
    
    // 3. Validate stock and calculate total
    let total = 0;
    
    for (const item of cartItems) {
      const product = products.find(p => p.id === item.productId);
      
      if (!product) {
        throw new Error(`Product ${item.productId} not found`);
      }
      
      if (product.stock < item.quantity) {
        throw new Error(`Not enough stock for ${product.name}`);
      }
      
      total += product.price * item.quantity;
    }
    
    // 4. Check user balance
    if (user.balance < total) {
      throw new Error('Insufficient balance');
    }
    
    // 5. Create order
    const [orderId] = await trx('orders').insert({
      user_id: userId,
      total_amount: total,
      status: 'confirmed',
      created_at: trx.fn.now()
    });
    
    // 6. Create order items and update stock
    for (const item of cartItems) {
      const product = products.find(p => p.id === item.productId);
      
      // Add order item
      await trx('order_items').insert({
        order_id: orderId,
        product_id: item.productId,
        quantity: item.quantity,
        price: product.price
      });
      
      // Decrease stock
      await trx('products')
        .where('id', item.productId)
        .decrement('stock', item.quantity);
    }
    
    // 7. Deduct from user balance
    await trx('users')
      .where('id', userId)
      .decrement('balance', total);
    
    // All operations succeed or all fail together!
    return { orderId, total };
  });
}

// Usage
try {
  const result = await createOrder(1, [
    { productId: 10, quantity: 2 },
    { productId: 15, quantity: 1 }
  ]);
  
  console.log(`Order ${result.orderId} created, total: $${result.total}`);
  
} catch (error) {
  console.error('Order failed:', error.message);
  // Nothing was changed - transaction rolled back
}

---

7. Quick Reference

Transaction Checklist

Before deploying code with transactions:

• ✅ All related operations are in the same transaction
• ✅ Using forUpdate() when reading data you'll modify
• ✅ Locking rows in consistent order (sorted by ID)
• ✅ Transactions are short (no API calls or emails inside)
• ✅ Error handling rolls back the transaction
• ✅ Testing with concurrent requests

Common Patterns

Simple Transfer:

await knex.transaction(async (trx) => {
  await trx('accounts').where('id', fromId).decrement('balance', amount);
  await trx('accounts').where('id', toId).increment('balance', amount);
});

Read then Update:

await knex.transaction(async (trx) => {
  const row = await trx('table')
    .where('id', id)
    .forUpdate() // 🔒 Lock it!
    .first();
  
  // Modify based on current value
  await trx('table').where('id', id).update({ value: row.value + 10 });
});

Multi-table Update:

await knex.transaction(async (trx) => {
  const [orderId] = await trx('orders').insert({ user_id: 1 });
  await trx('order_items').insert([
    { order_id: orderId, product_id: 1 },
    { order_id: orderId, product_id: 2 }
  ]);
});

The Golden Rules

1. Use transactions for multi-step operations - All-or-nothing guarantee
2. Lock what you'll modify - Use forUpdate() to prevent race conditions
3. Lock in consistent order - Always lock by ascending ID to avoid deadlocks
4. Keep transactions short - No external API calls or slow operations
5. Handle errors properly - Transactions auto-rollback on errors (with callback method)

When Things Go Wrong

Lost Update? → Use forUpdate() to lock rows
Race Condition? → Wrap in transaction with forUpdate()
Deadlock? → Lock in consistent order + implement retry logic
Slow Transaction? → Move non-DB operations outside the transaction

---

Next Steps

1. Identify operations that need transactions (multi-step processes)
2. Add forUpdate() to queries where you read-then-modify
3. Ensure consistent locking order (sort IDs before locking)
4. Test with concurrent requests to find race conditions
5. Monitor for deadlocks in production logs

Remember: Transactions are your safety net for data consistency. Use them for any operation that should be atomic (all-or-nothing)!