Database Transactions in PHP

Introduction to Database Transactions

Database transactions are a fundamental concept in database management that ensure data consistency and integrity. A transaction is a sequence of database operations that are treated as a single unit of work. Transactions follow the ACID properties: Atomicity, Consistency, Isolation, and Durability.

ACID Properties

Atomicity

All operations in a transaction either complete successfully or none of them do. If any operation fails, the entire transaction is rolled back.

Consistency

Transactions bring the database from one valid state to another, maintaining all database rules and constraints.

Isolation

Concurrent transactions don't interfere with each other. Each transaction appears to be executed in isolation.

Durability

Once a transaction is committed, its changes persist even in case of system failure.

Basic Transaction Syntax

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=testdb", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Start transaction
    $pdo->beginTransaction();
    
    // Perform database operations
    $stmt1 = $pdo->prepare("INSERT INTO accounts (name, balance) VALUES (?, ?)");
    $stmt1->execute(['John Doe', 1000]);
    
    $stmt2 = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
    $stmt2->execute([100, 1]);
    
    // Commit transaction
    $pdo->commit();
    echo "Transaction completed successfully";
    
} catch (Exception $e) {
    // Rollback transaction on error
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}
?>

Transaction Control Methods

beginTransaction()

Starts a new transaction by turning off autocommit mode.

<?php
$pdo->beginTransaction();
?>

commit()

Commits the current transaction, making all changes permanent.

<?php
$pdo->commit();
?>

rollBack()

Rolls back the current transaction, undoing all changes made since beginTransaction().

<?php
$pdo->rollBack();
?>

inTransaction()

Checks if a transaction is currently active.

<?php
if ($pdo->inTransaction()) {
    echo "Transaction is active";
}
?>

Practical Transaction Examples

Bank Transfer Example

<?php
function transferMoney($pdo, $fromAccountId, $toAccountId, $amount) {
    try {
        $pdo->beginTransaction();
        
        // Check if source account has sufficient balance
        $checkStmt = $pdo->prepare("SELECT balance FROM accounts WHERE id = ? FOR UPDATE");
        $checkStmt->execute([$fromAccountId]);
        $fromAccount = $checkStmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$fromAccount || $fromAccount['balance'] < $amount) {
            throw new Exception("Insufficient funds");
        }
        
        // Deduct from source account
        $deductStmt = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
        $deductStmt->execute([$amount, $fromAccountId]);
        
        // Add to destination account
        $addStmt = $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
        $addStmt->execute([$amount, $toAccountId]);
        
        // Log the transaction
        $logStmt = $pdo->prepare("INSERT INTO transfers (from_account, to_account, amount, transfer_date) VALUES (?, ?, ?, NOW())");
        $logStmt->execute([$fromAccountId, $toAccountId, $amount]);
        
        $pdo->commit();
        return true;
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw new Exception("Transfer failed: " . $e->getMessage());
    }
}

// Usage
try {
    transferMoney($pdo, 1, 2, 100.00);
    echo "Transfer completed successfully";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
?>

Order Processing Example

<?php
function processOrder($pdo, $customerId, $orderItems) {
    try {
        $pdo->beginTransaction();
        
        // Create order record
        $orderStmt = $pdo->prepare("INSERT INTO orders (customer_id, order_date, status) VALUES (?, NOW(), 'pending')");
        $orderStmt->execute([$customerId]);
        $orderId = $pdo->lastInsertId();
        
        $totalAmount = 0;
        
        // Process each order item
        foreach ($orderItems as $item) {
            // Check inventory
            $inventoryStmt = $pdo->prepare("SELECT quantity, price FROM products WHERE id = ? FOR UPDATE");
            $inventoryStmt->execute([$item['product_id']]);
            $product = $inventoryStmt->fetch(PDO::FETCH_ASSOC);
            
            if (!$product || $product['quantity'] < $item['quantity']) {
                throw new Exception("Product {$item['product_id']} is out of stock");
            }
            
            // Add order item
            $itemStmt = $pdo->prepare("INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)");
            $itemStmt->execute([$orderId, $item['product_id'], $item['quantity'], $product['price']]);
            
            // Update inventory
            $updateStmt = $pdo->prepare("UPDATE products SET quantity = quantity - ? WHERE id = ?");
            $updateStmt->execute([$item['quantity'], $item['product_id']]);
            
            $totalAmount += $product['price'] * $item['quantity'];
        }
        
        // Update order total
        $totalStmt = $pdo->prepare("UPDATE orders SET total_amount = ?, status = 'confirmed' WHERE id = ?");
        $totalStmt->execute([$totalAmount, $orderId]);
        
        $pdo->commit();
        return $orderId;
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw new Exception("Order processing failed: " . $e->getMessage());
    }
}
?>

Nested Transactions and Savepoints

PDO supports savepoints, which allow you to create nested transaction-like behavior:

<?php
function processComplexOrder($pdo, $orderData) {
    try {
        $pdo->beginTransaction();
        
        // Main order processing
        $orderId = createOrder($pdo, $orderData);
        
        // Create savepoint before payment processing
        $pdo->exec("SAVEPOINT payment_processing");
        
        try {
            // Process payment
            processPayment($pdo, $orderId, $orderData['payment']);
            
        } catch (Exception $e) {
            // Rollback to savepoint only
            $pdo->exec("ROLLBACK TO SAVEPOINT payment_processing");
            
            // Mark order as payment failed
            $pdo->prepare("UPDATE orders SET status = 'payment_failed' WHERE id = ?")->execute([$orderId]);
            
            throw new Exception("Payment failed: " . $e->getMessage());
        }
        
        // Release savepoint
        $pdo->exec("RELEASE SAVEPOINT payment_processing");
        
        // Finalize order
        finalizeOrder($pdo, $orderId);
        
        $pdo->commit();
        return $orderId;
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}
?>

Transaction Isolation Levels

Different isolation levels control how transactions interact with each other:

<?php
// Set isolation level before starting transaction
$pdo->exec("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
$pdo->beginTransaction();

// Available isolation levels:
// - READ UNCOMMITTED
// - READ COMMITTED  
// - REPEATABLE READ
// - SERIALIZABLE
?>

Example: Handling Concurrent Access

<?php
function reserveSeats($pdo, $eventId, $seatNumbers, $userId) {
    try {
        // Use higher isolation level for seat reservation
        $pdo->exec("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
        $pdo->beginTransaction();
        
        // Check if seats are available
        $placeholders = str_repeat('?,', count($seatNumbers) - 1) . '?';
        $checkStmt = $pdo->prepare("
            SELECT seat_number 
            FROM event_seats 
            WHERE event_id = ? AND seat_number IN ($placeholders) AND status = 'available'
            FOR UPDATE
        ");
        $params = array_merge([$eventId], $seatNumbers);
        $checkStmt->execute($params);
        
        $availableSeats = $checkStmt->fetchAll(PDO::FETCH_COLUMN);
        
        if (count($availableSeats) !== count($seatNumbers)) {
            $unavailable = array_diff($seatNumbers, $availableSeats);
            throw new Exception("Seats not available: " . implode(', ', $unavailable));
        }
        
        // Reserve the seats
        foreach ($seatNumbers as $seatNumber) {
            $reserveStmt = $pdo->prepare("
                UPDATE event_seats 
                SET status = 'reserved', user_id = ?, reserved_at = NOW() 
                WHERE event_id = ? AND seat_number = ?
            ");
            $reserveStmt->execute([$userId, $eventId, $seatNumber]);
        }
        
        $pdo->commit();
        return true;
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}
?>

Error Handling in Transactions

Comprehensive Error Handling

<?php
class TransactionManager {
    private $pdo;
    
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    
    public function executeTransaction(callable $operations) {
        $retry = 0;
        $maxRetries = 3;
        
        while ($retry < $maxRetries) {
            try {
                $this->pdo->beginTransaction();
                
                $result = $operations($this->pdo);
                
                $this->pdo->commit();
                return $result;
                
            } catch (PDOException $e) {
                $this->pdo->rollBack();
                
                // Check if error is retryable (deadlock, timeout)
                if ($this->isRetryableError($e) && $retry < $maxRetries - 1) {
                    $retry++;
                    // Exponential backoff
                    usleep(100000 * pow(2, $retry)); // 100ms, 200ms, 400ms
                    continue;
                }
                
                throw $e;
            } catch (Exception $e) {
                $this->pdo->rollBack();
                throw $e;
            }
        }
    }
    
    private function isRetryableError(PDOException $e) {
        $retryableCodes = [
            1205, // Lock wait timeout
            1213, // Deadlock found
        ];
        
        return in_array($e->getCode(), $retryableCodes);
    }
}

// Usage
$transactionManager = new TransactionManager($pdo);

try {
    $result = $transactionManager->executeTransaction(function($pdo) {
        // Your transaction operations here
        $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
        $stmt->execute(['John Doe', '[email protected]']);
        
        return $pdo->lastInsertId();
    });
    
    echo "User created with ID: $result";
} catch (Exception $e) {
    echo "Transaction failed: " . $e->getMessage();
}
?>

Transaction Performance Optimization

Batch Operations

<?php
function batchInsertUsers($pdo, $users) {
    try {
        $pdo->beginTransaction();
        
        $stmt = $pdo->prepare("INSERT INTO users (name, email, created_at) VALUES (?, ?, ?)");
        
        foreach ($users as $user) {
            $stmt->execute([
                $user['name'],
                $user['email'],
                date('Y-m-d H:i:s')
            ]);
        }
        
        $pdo->commit();
        return count($users);
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}

// For better performance with large datasets
function bulkInsertUsers($pdo, $users) {
    try {
        $pdo->beginTransaction();
        
        // Build values clause for bulk insert
        $values = [];
        $params = [];
        
        foreach ($users as $user) {
            $values[] = "(?, ?, ?)";
            $params[] = $user['name'];
            $params[] = $user['email'];
            $params[] = date('Y-m-d H:i:s');
        }
        
        $sql = "INSERT INTO users (name, email, created_at) VALUES " . implode(', ', $values);
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        
        $pdo->commit();
        return count($users);
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}
?>

Monitoring and Debugging Transactions

Transaction Logging

<?php
class LoggingTransaction {
    private $pdo;
    private $logger;
    private $transactionStart;
    
    public function __construct(PDO $pdo, $logger = null) {
        $this->pdo = $pdo;
        $this->logger = $logger ?: function($message) { error_log($message); };
    }
    
    public function begin() {
        $this->transactionStart = microtime(true);
        $this->log("Transaction started");
        return $this->pdo->beginTransaction();
    }
    
    public function commit() {
        $duration = microtime(true) - $this->transactionStart;
        $this->log("Transaction committed in " . round($duration * 1000, 2) . "ms");
        return $this->pdo->commit();
    }
    
    public function rollBack() {
        $duration = microtime(true) - $this->transactionStart;
        $this->log("Transaction rolled back after " . round($duration * 1000, 2) . "ms");
        return $this->pdo->rollBack();
    }
    
    public function execute($sql, $params = []) {
        $start = microtime(true);
        $stmt = $this->pdo->prepare($sql);
        $result = $stmt->execute($params);
        $duration = microtime(true) - $start;
        
        $this->log("Query executed in " . round($duration * 1000, 2) . "ms: " . $sql);
        
        return $stmt;
    }
    
    private function log($message) {
        ($this->logger)("[TRANSACTION] " . $message);
    }
}
?>

Best Practices

1. Keep Transactions Short

<?php
// Good - short transaction
function updateUserStatus($pdo, $userId, $status) {
    try {
        $pdo->beginTransaction();
        
        $stmt = $pdo->prepare("UPDATE users SET status = ?, updated_at = NOW() WHERE id = ?");
        $stmt->execute([$status, $userId]);
        
        $pdo->commit();
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}

// Avoid - long transaction holding locks
function badLongTransaction($pdo, $userId) {
    try {
        $pdo->beginTransaction();
        
        // This holds locks for too long
        $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
        $stmt->execute([$userId]);
        
        // External API call - should not be in transaction
        $apiResponse = callExternalAPI();
        
        // Update based on API response
        $updateStmt = $pdo->prepare("UPDATE users SET external_data = ? WHERE id = ?");
        $updateStmt->execute([$apiResponse, $userId]);
        
        $pdo->commit();
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}
?>

2. Always Handle Rollbacks

<?php
function processUserRegistration($pdo, $userData) {
    try {
        $pdo->beginTransaction();
        
        // Create user
        $userStmt = $pdo->prepare("INSERT INTO users (name, email, password) VALUES (?, ?, ?)");
        $userStmt->execute([$userData['name'], $userData['email'], $userData['password']]);
        $userId = $pdo->lastInsertId();
        
        // Create user profile
        $profileStmt = $pdo->prepare("INSERT INTO user_profiles (user_id, bio, preferences) VALUES (?, ?, ?)");
        $profileStmt->execute([$userId, $userData['bio'], json_encode($userData['preferences'])]);
        
        // Send welcome email (should be outside transaction)
        $emailQueued = queueWelcomeEmail($userId);
        
        if (!$emailQueued) {
            throw new Exception("Failed to queue welcome email");
        }
        
        $pdo->commit();
        return $userId;
        
    } catch (Exception $e) {
        $pdo->rollBack();
        
        // Log the error
        error_log("User registration failed: " . $e->getMessage());
        
        throw new Exception("Registration failed. Please try again.");
    }
}
?>

3. Use Appropriate Isolation Levels

<?php
function getAccountBalance($pdo, $accountId) {
    // Use READ COMMITTED for balance queries
    $pdo->exec("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
    $pdo->beginTransaction();
    
    try {
        $stmt = $pdo->prepare("SELECT balance FROM accounts WHERE id = ?");
        $stmt->execute([$accountId]);
        $balance = $stmt->fetchColumn();
        
        $pdo->commit();
        return $balance;
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}

function criticalFinancialOperation($pdo, $data) {
    // Use SERIALIZABLE for critical operations
    $pdo->exec("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
    $pdo->beginTransaction();
    
    try {
        // Critical operations that need complete isolation
        performCriticalOperations($pdo, $data);
        
        $pdo->commit();
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}
?>

Common Pitfalls

1. Forgetting to Commit or Rollback

<?php
// Bad - transaction left hanging
function badTransaction($pdo) {
    $pdo->beginTransaction();
    
    $stmt = $pdo->prepare("INSERT INTO users (name) VALUES (?)");
    $stmt->execute(['John']);
    
    // Missing commit() or rollBack()
    // This leaves the transaction open!
}

// Good - always handle transaction completion
function goodTransaction($pdo) {
    try {
        $pdo->beginTransaction();
        
        $stmt = $pdo->prepare("INSERT INTO users (name) VALUES (?)");
        $stmt->execute(['John']);
        
        $pdo->commit();
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}
?>

2. Nested Transactions Without Savepoints

<?php
// Be careful with nested calls that start transactions
function outerFunction($pdo) {
    try {
        $pdo->beginTransaction();
        
        // This might start another transaction
        innerFunction($pdo);
        
        $pdo->commit();
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}

function innerFunction($pdo) {
    // Check if transaction is already active
    if (!$pdo->inTransaction()) {
        $pdo->beginTransaction();
        $shouldCommit = true;
    } else {
        $shouldCommit = false;
    }
    
    try {
        $stmt = $pdo->prepare("INSERT INTO logs (message) VALUES (?)");
        $stmt->execute(['Inner operation']);
        
        if ($shouldCommit) {
            $pdo->commit();
        }
        
    } catch (Exception $e) {
        if ($shouldCommit) {
            $pdo->rollBack();
        }
        throw $e;
    }
}
?>

Database transactions are essential for maintaining data integrity in PHP applications. They ensure that related database operations either all succeed or all fail, preventing data corruption and maintaining consistency. Always use transactions for operations that involve multiple related database changes, and handle errors appropriately to ensure proper rollback when needed.