Transactions

Transactions ensure data consistency and atomicity in MatsushibaDB. Learn how to implement proper transaction management for reliable database operations.

What are Transactions?

A transaction is a sequence of database operations that are treated as a single unit of work. Transactions follow the ACID properties:

Atomicity: All operations succeed or all fail
Consistency: Database remains in a valid state
Isolation: Concurrent transactions don’t interfere
Durability: Committed changes persist

Basic Transaction Usage

Simple Transactions

const MatsushibaDB = require('matsushibadb');
const db = new MatsushibaDB('app.db');

// Basic transaction
try {
    db.run('BEGIN TRANSACTION');
    
    // Multiple operations
    db.run('INSERT INTO users (name, email) VALUES (?, ?)', 
           ['Alice', '[email protected]']);
    db.run('INSERT INTO users (name, email) VALUES (?, ?)', 
           ['Bob', '[email protected]']);
    
    // Commit if successful
    db.run('COMMIT');
    console.log('Transaction committed successfully');
} catch (error) {
    // Rollback on error
    db.run('ROLLBACK');
    console.error('Transaction rolled back:', error.message);
}

// Using transaction helper
function transferMoney(fromUserId, toUserId, amount) {
    try {
        db.run('BEGIN TRANSACTION');
        
        // Check sender balance
        const sender = db.get('SELECT balance FROM accounts WHERE user_id = ?', [fromUserId]);
        if (!sender || sender.balance < amount) {
            throw new Error('Insufficient funds');
        }
        
        // Deduct from sender
        db.run('UPDATE accounts SET balance = balance - ? WHERE user_id = ?', 
               [amount, fromUserId]);
        
        // Add to receiver
        db.run('UPDATE accounts SET balance = balance + ? WHERE user_id = ?', 
               [amount, toUserId]);
        
        // Log transaction
        db.run(`
            INSERT INTO transactions (from_user, to_user, amount, type)
            VALUES (?, ?, ?, ?)
        `, [fromUserId, toUserId, amount, 'transfer']);
        
        db.run('COMMIT');
        return { success: true, message: 'Transfer completed' };
    } catch (error) {
        db.run('ROLLBACK');
        return { success: false, error: error.message };
    }
}

Advanced Transaction Management

// Transaction with savepoints
function complexOperation() {
    try {
        db.run('BEGIN TRANSACTION');
        
        // First operation
        const userResult = db.run('INSERT INTO users (name, email) VALUES (?, ?)', 
                                 ['John', '[email protected]']);
        const userId = userResult.lastInsertRowid;
        
        // Savepoint for optional operations
        db.run('SAVEPOINT profile_creation');
        
        try {
            // Create profile
            db.run('INSERT INTO profiles (user_id, bio) VALUES (?, ?)', 
                   [userId, 'New user profile']);
            
            // Create settings
            db.run('INSERT INTO user_settings (user_id, theme) VALUES (?, ?)', 
                   [userId, 'dark']);
            
            db.run('RELEASE SAVEPOINT profile_creation');
        } catch (error) {
            // Rollback to savepoint
            db.run('ROLLBACK TO SAVEPOINT profile_creation');
            console.log('Profile creation failed, continuing without profile');
        }
        
        db.run('COMMIT');
        return { success: true, userId: userId };
    } catch (error) {
        db.run('ROLLBACK');
        throw error;
    }
}

// Nested transactions using savepoints
function nestedTransaction() {
    try {
        db.run('BEGIN TRANSACTION');
        
        // Outer transaction operations
        db.run('INSERT INTO orders (customer_id, total) VALUES (?, ?)', [1, 100.00]);
        
        // Nested transaction (savepoint)
        db.run('SAVEPOINT order_items');
        
        try {
            // Inner transaction operations
            db.run('INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)', 
                   [1, 101, 2]);
            db.run('INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)', 
                   [1, 102, 1]);
            
            db.run('RELEASE SAVEPOINT order_items');
        } catch (error) {
            db.run('ROLLBACK TO SAVEPOINT order_items');
            throw error;
        }
        
        db.run('COMMIT');
    } catch (error) {
        db.run('ROLLBACK');
        throw error;
    }
}

Transaction Isolation Levels

Understanding Isolation Levels

-- Set isolation level (if supported)
PRAGMA read_uncommitted = 1;  -- READ UNCOMMITTED
PRAGMA read_committed = 1;    -- READ COMMITTED
PRAGMA repeatable_read = 1;   -- REPEATABLE READ
PRAGMA serializable = 1;      -- SERIALIZABLE (default)

Isolation Level Examples

// Read Committed example
function readCommittedExample() {
    try {
        db.run('BEGIN TRANSACTION');
        
        // Read initial value
        const initial = db.get('SELECT balance FROM accounts WHERE user_id = ?', [1]);
        console.log('Initial balance:', initial.balance);
        
        // Simulate concurrent update (in another connection)
        // This would be done by another process/thread
        
        // Read again - might see different value
        const updated = db.get('SELECT balance FROM accounts WHERE user_id = ?', [1]);
        console.log('Updated balance:', updated.balance);
        
        db.run('COMMIT');
    } catch (error) {
        db.run('ROLLBACK');
        throw error;
    }
}

// Repeatable Read example
function repeatableReadExample() {
    try {
        db.run('BEGIN TRANSACTION');
        
        // First read
        const first = db.get('SELECT balance FROM accounts WHERE user_id = ?', [1]);
        console.log('First read:', first.balance);
        
        // Simulate concurrent update (in another connection)
        // This would be done by another process/thread
        
        // Second read - should see same value
        const second = db.get('SELECT balance FROM accounts WHERE user_id = ?', [1]);
        console.log('Second read:', second.balance);
        
        db.run('COMMIT');
    } catch (error) {
        db.run('ROLLBACK');
        throw error;
    }
}

Connection Pooling and Transactions

Pool-based Transaction Management

const { MatsushibaPool } = require('matsushibadb');

const pool = new MatsushibaPool({
    database: 'app.db',
    min: 5,
    max: 20
});

// Transaction with connection pool
async function poolTransaction() {
    const connection = await pool.acquire();
    
    try {
        await connection.beginTransaction();
        
        // Multiple operations
        await connection.run('INSERT INTO users (name, email) VALUES (?, ?)', 
                           ['Alice', '[email protected]']);
        await connection.run('INSERT INTO users (name, email) VALUES (?, ?)', 
                           ['Bob', '[email protected]']);
        
        await connection.commit();
        return { success: true };
    } catch (error) {
        await connection.rollback();
        throw error;
    } finally {
        pool.release(connection);
    }
}

// Batch operations with pool
async function batchOperations(operations) {
    const connection = await pool.acquire();
    
    try {
        await connection.beginTransaction();
        
        for (const operation of operations) {
            await connection.run(operation.sql, operation.params);
        }
        
        await connection.commit();
        return { success: true, processed: operations.length };
    } catch (error) {
        await connection.rollback();
        throw error;
    } finally {
        pool.release(connection);
    }
}

Error Handling in Transactions

Comprehensive Error Handling

// Advanced error handling
function robustTransaction(operations) {
    let transactionStarted = false;
    
    try {
        db.run('BEGIN TRANSACTION');
        transactionStarted = true;
        
        const results = [];
        
        for (const operation of operations) {
            try {
                const result = db.run(operation.sql, operation.params);
                results.push({
                    success: true,
                    result: result,
                    operation: operation.name
                });
            } catch (operationError) {
                // Log operation error but continue with transaction
                console.error(`Operation ${operation.name} failed:`, operationError.message);
                results.push({
                    success: false,
                    error: operationError.message,
                    operation: operation.name
                });
                
                // Decide whether to continue or abort
                if (operation.critical) {
                    throw operationError;
                }
            }
        }
        
        db.run('COMMIT');
        return {
            success: true,
            results: results,
            message: 'Transaction completed'
        };
    } catch (error) {
        if (transactionStarted) {
            db.run('ROLLBACK');
        }
        
        return {
            success: false,
            error: error.message,
            results: results || []
        };
    }
}

// Retry logic for failed transactions
async function retryTransaction(operation, maxRetries = 3) {
    for (let attempt = 1; attempt <= maxRetries; attempt++) {
        try {
            return await operation();
        } catch (error) {
            if (error.code === 'SQLITE_BUSY' && attempt < maxRetries) {
                // Wait before retry
                await new Promise(resolve => setTimeout(resolve, 100 * attempt));
                continue;
            }
            throw error;
        }
    }
}

Performance Optimization

Transaction Performance Tips

// Batch operations for better performance
function batchInsert(records) {
    try {
        db.run('BEGIN TRANSACTION');
        
        const stmt = db.prepare('INSERT INTO users (name, email, age) VALUES (?, ?, ?)');
        
        for (const record of records) {
            stmt.run(record.name, record.email, record.age);
        }
        
        stmt.finalize();
        db.run('COMMIT');
        
        return { success: true, count: records.length };
    } catch (error) {
        db.run('ROLLBACK');
        throw error;
    }
}

// Optimized transaction with prepared statements
function optimizedTransaction(operations) {
    const preparedStmts = {};
    
    try {
        db.run('BEGIN TRANSACTION');
        
        // Prepare all statements first
        for (const operation of operations) {
            if (!preparedStmts[operation.sql]) {
                preparedStmts[operation.sql] = db.prepare(operation.sql);
            }
        }
        
        // Execute operations
        for (const operation of operations) {
            preparedStmts[operation.sql].run(operation.params);
        }
        
        // Finalize prepared statements
        for (const stmt of Object.values(preparedStmts)) {
            stmt.finalize();
        }
        
        db.run('COMMIT');
        return { success: true };
    } catch (error) {
        // Finalize statements on error
        for (const stmt of Object.values(preparedStmts)) {
            try {
                stmt.finalize();
            } catch (e) {
                // Ignore finalize errors
            }
        }
        
        db.run('ROLLBACK');
        throw error;
    }
}

Real-world Examples

E-commerce Order Processing

function processOrder(orderData) {
    try {
        db.run('BEGIN TRANSACTION');
        
        // 1. Create order
        const orderResult = db.run(`
            INSERT INTO orders (customer_id, total_amount, status)
            VALUES (?, ?, ?)
        `, [orderData.customerId, orderData.total, 'pending']);
        
        const orderId = orderResult.lastInsertRowid;
        
        // 2. Add order items
        for (const item of orderData.items) {
            // Check inventory
            const inventory = db.get(`
                SELECT stock FROM products WHERE id = ?
            `, [item.productId]);
            
            if (!inventory || inventory.stock < item.quantity) {
                throw new Error(`Insufficient stock for product ${item.productId}`);
            }
            
            // Add order item
            db.run(`
                INSERT INTO order_items (order_id, product_id, quantity, price)
                VALUES (?, ?, ?, ?)
            `, [orderId, item.productId, item.quantity, item.price]);
            
            // Update inventory
            db.run(`
                UPDATE products SET stock = stock - ? WHERE id = ?
            `, [item.quantity, item.productId]);
        }
        
        // 3. Process payment
        const paymentResult = db.run(`
            INSERT INTO payments (order_id, amount, method, status)
            VALUES (?, ?, ?, ?)
        `, [orderId, orderData.total, orderData.paymentMethod, 'completed']);
        
        // 4. Update order status
        db.run(`
            UPDATE orders SET status = ?, payment_id = ?
            WHERE id = ?
        `, ['completed', paymentResult.lastInsertRowid, orderId]);
        
        db.run('COMMIT');
        
        return {
            success: true,
            orderId: orderId,
            message: 'Order processed successfully'
        };
    } catch (error) {
        db.run('ROLLBACK');
        return {
            success: false,
            error: error.message
        };
    }
}

User Registration with Profile

function registerUser(userData) {
    try {
        db.run('BEGIN TRANSACTION');
        
        // 1. Create user account
        const userResult = db.run(`
            INSERT INTO users (username, email, password_hash, status)
            VALUES (?, ?, ?, ?)
        `, [userData.username, userData.email, userData.passwordHash, 'active']);
        
        const userId = userResult.lastInsertRowid;
        
        // 2. Create user profile
        db.run(`
            INSERT INTO profiles (user_id, first_name, last_name, bio)
            VALUES (?, ?, ?, ?)
        `, [userId, userData.firstName, userData.lastName, userData.bio || '']);
        
        // 3. Set default preferences
        db.run(`
            INSERT INTO user_preferences (user_id, theme, notifications, language)
            VALUES (?, ?, ?, ?)
        `, [userId, 'light', 1, 'en']);
        
        // 4. Create user settings
        db.run(`
            INSERT INTO user_settings (user_id, privacy_level, email_notifications)
            VALUES (?, ?, ?)
        `, [userId, 'public', 1]);
        
        // 5. Log registration event
        db.run(`
            INSERT INTO user_events (user_id, event_type, description)
            VALUES (?, ?, ?)
        `, [userId, 'registration', 'User account created']);
        
        db.run('COMMIT');
        
        return {
            success: true,
            userId: userId,
            message: 'User registered successfully'
        };
    } catch (error) {
        db.run('ROLLBACK');
        
        // Handle specific errors
        if (error.message.includes('UNIQUE constraint failed')) {
            if (error.message.includes('username')) {
                return { success: false, error: 'Username already exists' };
            } else if (error.message.includes('email')) {
                return { success: false, error: 'Email already exists' };
            }
        }
        
        return { success: false, error: error.message };
    }
}

Best Practices

Keep Transactions Short

Minimize transaction duration to reduce lock contention and improve concurrency.

Handle Errors Properly

Always implement proper error handling with rollback on failures.

Use Savepoints Sparingly

Use savepoints for complex operations but avoid deep nesting.

Prepare Statements Outside Transactions

Prepare statements before starting transactions for better performance.

Avoid Long-Running Transactions

Don’t perform I/O operations or external API calls within transactions.

Test Transaction Logic

Thoroughly test transaction logic with various failure scenarios.

Monitor Transaction Performance

Monitor transaction duration and identify performance bottlenecks.

Use Appropriate Isolation Levels

Choose the right isolation level based on your concurrency requirements.

Transactions are crucial for maintaining data integrity. Always wrap related operations in transactions and implement proper error handling to ensure your database remains consistent.

🚀 Get Started

📦 Installation

🧠 Core Concepts

📖 Advanced Guides

🔧 API Reference

🛠️ Troubleshooting

Transactions

Transactions

What are Transactions?

Basic Transaction Usage

Simple Transactions

Advanced Transaction Management

Transaction Isolation Levels

Understanding Isolation Levels

Isolation Level Examples

Connection Pooling and Transactions

Pool-based Transaction Management

Error Handling in Transactions

Comprehensive Error Handling

Performance Optimization

Transaction Performance Tips

Real-world Examples

E-commerce Order Processing

User Registration with Profile

Best Practices

🚀 Get Started

📦 Installation

🧠 Core Concepts

📖 Advanced Guides

🔧 API Reference

🛠️ Troubleshooting

​Transactions

​What are Transactions?

​Basic Transaction Usage

​Simple Transactions

​Advanced Transaction Management

​Transaction Isolation Levels

​Understanding Isolation Levels

​Isolation Level Examples

​Connection Pooling and Transactions

​Pool-based Transaction Management

​Error Handling in Transactions

​Comprehensive Error Handling

​Performance Optimization

​Transaction Performance Tips

​Real-world Examples

​E-commerce Order Processing

​User Registration with Profile

​Best Practices

Transactions

What are Transactions?

Basic Transaction Usage

Simple Transactions

Advanced Transaction Management

Transaction Isolation Levels

Understanding Isolation Levels

Isolation Level Examples

Connection Pooling and Transactions

Pool-based Transaction Management

Error Handling in Transactions

Comprehensive Error Handling

Performance Optimization

Transaction Performance Tips

Real-world Examples

E-commerce Order Processing

User Registration with Profile

Best Practices