ACID Properties & Data Integrity Fundamentals

A beginner-friendly guide to keeping your data safe, consistent, and reliable.

---

Table of Contents

1. What is Data Integrity?
2. Database Constraints
3. What is ACID?
4. Atomicity - All or Nothing
5. Consistency - Following the Rules
6. Isolation - No Interference
7. Durability - Data Survives
8. Quick Reference

---

1. What is Data Integrity?

Data integrity means your data is accurate, consistent, and reliable. Think of it as the database's safety rules.

Why It Matters

Without data integrity:

• 😱 Customer emails are duplicated
• 💸 Bank account balance goes negative
• 🔗 Orders reference non-existent customers
• 📊 Data becomes unreliable

With data integrity:

• ✅ Each email is unique
• ✅ Balance can't be negative
• ✅ Orders always have valid customers
• ✅ Data you can trust

Two Ways to Enforce Integrity

1. Database constraints - Rules enforced by the database itself (safer!)
2. Application logic - Rules enforced by your code (less reliable)

Best practice: Use database constraints whenever possible. They're your last line of defense.

---

2. Database Constraints

Constraints are rules that prevent bad data from entering your database.

Common Constraints

exports.up = function(knex) {
  return knex.schema.createTable('users', (table) => {
    // PRIMARY KEY - Unique identifier
    table.increments('id').primary();
    
    // UNIQUE - No duplicates allowed
    table.string('email').notNullable().unique();
    
    // NOT NULL - Must have a value
    table.string('username').notNullable();
    
    // DEFAULT - Automatic value if not provided
    table.decimal('balance', 10, 2).defaultTo(0);
    
    // CHECK - Custom validation rule
    table.check('balance >= 0'); // Balance can't be negative
    
    table.timestamps(true, true);
  });
};

Foreign Key Constraints

Foreign keys ensure relationships are valid:

exports.up = function(knex) {
  return knex.schema
    .createTable('customers', (table) => {
      table.increments('customer_id').primary();
      table.string('name').notNullable();
      table.string('email').notNullable().unique();
    })
    .createTable('orders', (table) => {
      table.increments('order_id').primary();
      table.integer('customer_id').unsigned().notNullable();
      table.decimal('total_amount', 10, 2).notNullable();
      
      // Foreign key constraint
      table.foreign('customer_id')
        .references('customer_id')
        .inTable('customers')
        .onDelete('RESTRICT'); // Can't delete customer with orders
    });
};

Handling Constraint Violations

async function createUser(email, username) {
  try {
    const [userId] = await knex('users').insert({
      email: email,
      username: username,
      balance: 0
    });
    
    return userId;
    
  } catch (error) {
    // Handle specific errors
    if (error.code === 'ER_DUP_ENTRY') {
      throw new Error('Email already exists');
    }
    if (error.code === 'ER_CHECK_CONSTRAINT_VIOLATED') {
      throw new Error('Balance cannot be negative');
    }
    throw error;
  }
}

// Usage
try {
  await createUser('john@example.com', 'john');
  await createUser('john@example.com', 'john2'); // Fails - duplicate email
} catch (error) {
  console.error(error.message); // "Email already exists"
}

---

3. What is ACID?

ACID is a set of properties that guarantee database transactions are processed reliably.

Atomicity - All or nothing
Consistency - Rules are followed
Isolation - Transactions don't interfere
Durability - Changes are permanent

These work together to keep your data safe, especially when multiple things happen at once.

---

4. Atomicity - All or Nothing

Atomicity means a transaction either completes fully or doesn't happen at all. No partial updates!

The Problem Without Atomicity

// ❌ DANGEROUS: No transaction
async function transferMoneyBad(fromId, toId, amount) {
  // Step 1: Deduct from sender
  await knex('users').where('id', fromId).decrement('balance', amount);
  
  // 💥 App crashes here!
  
  // Step 2: Add to receiver (never happens!)
  await knex('users').where('id', toId).increment('balance', amount);
}

// Result: Money disappears! Sender loses $100, receiver gets nothing. 😱

The Solution With Atomicity

// ✅ SAFE: Transaction ensures all-or-nothing
async function transferMoneySafe(fromId, toId, amount) {
  return knex.transaction(async (trx) => {
    // Check balance first
    const sender = await trx('users').where('id', fromId).first();
    
    if (sender.balance < amount) {
      throw new Error('Insufficient balance');
    }
    
    // Step 1: Deduct from sender
    await trx('users').where('id', fromId).decrement('balance', amount);
    
    // Step 2: Add to receiver
    await trx('users').where('id', toId).increment('balance', amount);
    
    // Both succeed or both fail together!
  });
}

// Usage
try {
  await transferMoneySafe(1, 2, 100);
  console.log('Transfer successful');
} catch (error) {
  console.error('Transfer failed:', error.message);
  // Nothing changed - transaction rolled back
}

Key Point: If anything fails, the entire transaction rolls back. No partial updates!

---

5. Consistency - Following the Rules

Consistency means transactions take the database from one valid state to another. All rules (constraints) must be satisfied.

Enforcing Business Rules

// ✅ Transaction maintains consistency
async function createOrder(userId, items) {
  return knex.transaction(async (trx) => {
    // Rule 1: User must exist (referential integrity)
    const user = await trx('users').where('id', userId).first();
    
    if (!user) {
      throw new Error('User not found');
    }
    
    // Rule 2: Calculate total
    const total = items.reduce((sum, item) => {
      return sum + (item.price * item.quantity);
    }, 0);
    
    // Rule 3: User must have enough balance
    if (user.balance < total) {
      throw new Error('Insufficient balance');
    }
    
    // Rule 4: Create order
    const [orderId] = await trx('orders').insert({
      user_id: userId,
      total_amount: total,
      status: 'pending'
    });
    
    // Rule 5: Add order items
    for (const item of items) {
      await trx('order_items').insert({
        order_id: orderId,
        product_name: item.product_name,
        quantity: item.quantity,
        price: item.price
      });
    }
    
    // Rule 6: Deduct balance
    await trx('users').where('id', userId).decrement('balance', total);
    
    // All rules satisfied - commit!
    return orderId;
  });
}

// If ANY rule is violated, entire transaction fails
// Database remains in a consistent state

Database-Level Consistency

exports.up = function(knex) {
  return knex.schema.createTable('products', (table) => {
    table.increments('product_id').primary();
    table.string('name').notNullable();
    table.decimal('price', 10, 2).notNullable();
    table.integer('stock').notNullable().defaultTo(0);
    
    // Consistency rules enforced by database
    table.check('price > 0'); // Price must be positive
    table.check('stock >= 0'); // Stock can't be negative
  });
};

// These rules are ALWAYS enforced, even if your code has bugs!

---

6. Isolation - No Interference

Isolation means concurrent transactions don't interfere with each other. Each transaction acts like it's alone.

The Problem Without Isolation

// ❌ RACE CONDITION: No isolation
async function purchaseProductBad(userId, productId, price) {
  // User A and User B both run this at the same time
  
  // Both read balance = $100
  const user = await knex('users').where('id', userId).first();
  console.log('Balance:', user.balance); // Both see $100
  
  if (user.balance >= price) {
    // Both pass the check!
    
    // Both deduct $80
    await knex('users').where('id', userId).decrement('balance', price);
    
    // Final balance: $20 (should be -$60!)
    // One purchase was "free" because of race condition 😱
  }
}

The Solution With Isolation

// ✅ SAFE: Transaction with row locking
async function purchaseProductSafe(userId, productId, price) {
  return knex.transaction(async (trx) => {
    // Lock the user row - other transactions must wait
    const user = await trx('users')
      .where('id', userId)
      .forUpdate() // 🔒 Exclusive lock
      .first();
    
    if (user.balance < price) {
      throw new Error('Insufficient balance');
    }
    
    // Safe to deduct - we have exclusive access
    await trx('users')
      .where('id', userId)
      .decrement('balance', price);
    
    await trx('purchases').insert({
      user_id: userId,
      product_id: productId,
      price: price
    });
    
    // Lock released when transaction completes
  });
}

// Now purchases happen one at a time - no race condition!

Isolation Levels

MySQL supports different isolation levels. Default is Repeatable Read (good for most cases):

// Default isolation (Repeatable Read)
await knex.transaction(async (trx) => {
  // Your queries here
});

// Specify isolation level if needed
await knex.transaction({ isolationLevel: 'read committed' }, async (trx) => {
  // Your queries here
});

Common Levels:

• Read Committed - See committed changes from other transactions
• Repeatable Read - Same query always returns same results (MySQL default)
• Serializable - Strongest isolation, like transactions run one after another

For beginners: Just use the default (Repeatable Read) and add forUpdate() when reading data you'll modify.

---

7. Durability - Data Survives

Durability means once a transaction commits, the changes are permanent - even if the power goes out!

How Durability Works

async function updateBalance(userId, newBalance) {
  await knex.transaction(async (trx) => {
    await trx('users')
      .where('id', userId)
      .update({ balance: newBalance });
    
    // Transaction commits here
  });
  
  console.log('Balance updated');
  
  // 💥 Even if the app crashes RIGHT NOW...
  // 💥 Even if the server loses power...
  // ✅ The balance change is still saved!
}

Behind the scenes: MySQL writes to a transaction log before committing. Even if the database crashes, it can recover from the log on restart.

Testing Durability

async function testDurability() {
  // Update balance
  await knex('users')
    .where('id', 1)
    .update({ balance: 1000 });
  
  console.log('Updated balance to 1000');
  
  // Query to verify
  const user = await knex('users').where('id', 1).first();
  console.log('Current balance:', user.balance); // 1000
  
  // Stop and restart MySQL server
  // Query again - still 1000! ✅
}

You don't need to do anything special - durability is automatic once a transaction commits!

---

8. Quick Reference

ACID Checklist

Before deploying code that modifies data:

• ✅ Using transactions for multi-step operations (Atomicity)
• ✅ All constraints defined in schema (Consistency)
• ✅ Using forUpdate() when reading then modifying (Isolation)
• ✅ Proper error handling and rollback logic (Atomicity)
• ✅ Foreign keys enforce relationships (Consistency)

Constraint Types

Constraint	Purpose	Example
PRIMARY KEY	Unique identifier	table.increments('id').primary()
UNIQUE	No duplicates	table.string('email').unique()
NOT NULL	Must have value	table.string('name').notNullable()
DEFAULT	Auto-fill if empty	table.decimal('balance').defaultTo(0)
CHECK	Custom rule	table.check('age >= 18')
FOREIGN KEY	Valid reference	table.foreign('user_id').references('id').inTable('users')

Common Constraint Errors

// Handle constraint violations
try {
  await knex('users').insert({ email: 'test@example.com', username: 'test' });
} catch (error) {
  if (error.code === 'ER_DUP_ENTRY') {
    console.error('Duplicate email or username');
  } else if (error.code === 'ER_CHECK_CONSTRAINT_VIOLATED') {
    console.error('Check constraint failed (e.g., negative balance)');
  } else if (error.code === 'ER_NO_REFERENCED_ROW_2') {
    console.error('Foreign key violation (referenced record does not exist)');
  } else {
    throw error; // Unknown error
  }
}

Transaction Pattern

Standard pattern for any multi-step operation:

async function doSomething() {
  return knex.transaction(async (trx) => {
    // 1. Read data (lock if needed)
    const record = await trx('table')
      .where('id', 1)
      .forUpdate() // Lock if you'll modify based on current value
      .first();
    
    // 2. Validate business rules
    if (/* some condition */) {
      throw new Error('Validation failed');
    }
    
    // 3. Make changes
    await trx('table').where('id', 1).update({ /* ... */ });
    await trx('other_table').insert({ /* ... */ });
    
    // 4. Return result (auto-commits if no error)
    return result;
  });
}

The Golden Rules

1. Use transactions for related operations - Ensure atomicity
2. Define constraints in the database - Don't rely only on app code
3. Lock rows you'll modify - Use forUpdate() to prevent race conditions
4. Handle constraint violations gracefully - Catch and show user-friendly errors
5. Let the database do the work - ACID is automatic when you use transactions
6. Test failure scenarios - Make sure rollback works as expected
7. Keep transactions short - Acquire locks, do work, release quickly

---

Common Patterns

Pattern 1: Money Transfer

async function transfer(fromId, toId, amount) {
  return knex.transaction(async (trx) => {
    // Lock both accounts
    const [from, to] = await Promise.all([
      trx('accounts').where('id', fromId).forUpdate().first(),
      trx('accounts').where('id', toId).forUpdate().first()
    ]);
    
    // Validate
    if (from.balance < amount) {
      throw new Error('Insufficient funds');
    }
    
    // Transfer
    await trx('accounts').where('id', fromId).decrement('balance', amount);
    await trx('accounts').where('id', toId).increment('balance', amount);
    
    // Log transaction
    await trx('transfers').insert({ from_id: fromId, to_id: toId, amount });
  });
}

Pattern 2: Create with Validation

async function createUser(data) {
  // Constraints handle validation automatically
  try {
    const [userId] = await knex('users').insert({
      email: data.email,        // UNIQUE constraint
      username: data.username,  // NOT NULL constraint
      balance: data.balance || 0 // CHECK constraint (>= 0)
    });
    return userId;
  } catch (error) {
    // Convert database errors to user-friendly messages
    if (error.code === 'ER_DUP_ENTRY') {
      throw new Error('Email or username already taken');
    }
    throw error;
  }
}

Pattern 3: Update with Validation

async function updateProductStock(productId, quantity) {
  return knex.transaction(async (trx) => {
    const product = await trx('products')
      .where('id', productId)
      .forUpdate()
      .first();
    
    if (!product) {
      throw new Error('Product not found');
    }
    
    if (product.stock < quantity) {
      throw new Error('Not enough stock');
    }
    
    await trx('products')
      .where('id', productId)
      .decrement('stock', quantity);
  });
}

---

Testing Your Integrity

Quick tests to verify ACID properties:

async function testAcidProperties() {
  // Test 1: Atomicity
  console.log('Testing atomicity...');
  try {
    await knex.transaction(async (trx) => {
      await trx('users').insert({ email: 'test@example.com', username: 'test' });
      throw new Error('Simulated failure');
      await trx('orders').insert({ user_id: 1 }); // Never executes
    });
  } catch (error) {
    const user = await knex('users').where('email', 'test@example.com').first();
    console.log('User should not exist:', user === undefined); // true ✅
  }
  
  // Test 2: Consistency (Constraints)
  console.log('Testing constraints...');
  try {
    await knex('users').insert({ email: 'bad@example.com', balance: -100 });
  } catch (error) {
    console.log('Negative balance rejected:', error.code === 'ER_CHECK_CONSTRAINT_VIOLATED'); // true ✅
  }
  
  // Test 3: Isolation (No race condition)
  console.log('Testing isolation...');
  await Promise.all([
    purchaseProductSafe(1, 10, 50),
    purchaseProductSafe(1, 11, 50)
  ]);
  const user = await knex('users').where('id', 1).first();
  console.log('Balance correct after concurrent purchases:', user.balance === 0); // true ✅
}

---

Next Steps

1. Add constraints to your existing tables (UNIQUE, CHECK, etc.)
2. Identify operations that need transactions (multi-step processes)
3. Add forUpdate() to queries where you read-then-modify
4. Test your error handling (duplicate emails, negative balances, etc.)
5. Verify foreign key relationships are working

Remember: The database is your partner in maintaining data integrity. Use its built-in features (constraints, transactions, foreign keys) rather than trying to handle everything in application code!

ACID properties work automatically when you use transactions properly - no magic required! 🎉