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', 'alice@example.com']);
    db.run('INSERT INTO users (name, email) VALUES (?, ?)', 
           ['Bob', 'bob@example.com']);
    
    // 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 };
    }
}

import matsushibadb
db = matsushibadb.MatsushibaDB('app.db')

# Basic transaction
try:
    db.execute('BEGIN TRANSACTION')
    
    # Multiple operations
    db.execute('INSERT INTO users (name, email) VALUES (?, ?)', 
               ('Alice', 'alice@example.com'))
    db.execute('INSERT INTO users (name, email) VALUES (?, ?)', 
               ('Bob', 'bob@example.com'))
    
    # Commit if successful
    db.execute('COMMIT')
    print('Transaction committed successfully')
except Exception as e:
    # Rollback on error
    db.execute('ROLLBACK')
    print('Transaction rolled back:', str(e))

# Using transaction helper
def transfer_money(from_user_id, to_user_id, amount):
    try:
        db.execute('BEGIN TRANSACTION')
        
        # Check sender balance
        sender = db.execute('SELECT balance FROM accounts WHERE user_id = ?', 
                            (from_user_id,)).fetchone()
        if not sender or sender[0] < amount:
            raise Exception('Insufficient funds')
        
        # Deduct from sender
        db.execute('UPDATE accounts SET balance = balance - ? WHERE user_id = ?', 
                   (amount, from_user_id))
        
        # Add to receiver
        db.execute('UPDATE accounts SET balance = balance + ? WHERE user_id = ?', 
                   (amount, to_user_id))
        
        # Log transaction
        db.execute('''
            INSERT INTO transactions (from_user, to_user, amount, type)
            VALUES (?, ?, ?, ?)
        ''', (from_user_id, to_user_id, amount, 'transfer'))
        
        db.execute('COMMIT')
        return {'success': True, 'message': 'Transfer completed'}
    except Exception as e:
        db.execute('ROLLBACK')
        return {'success': False, 'error': str(e)}

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', 'john@example.com']);
        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 with savepoints
def complex_operation():
    try:
        db.execute('BEGIN TRANSACTION')
        
        # First operation
        user_result = db.execute('INSERT INTO users (name, email) VALUES (?, ?)', 
                                 ('John', 'john@example.com'))
        user_id = user_result.lastrowid
        
        # Savepoint for optional operations
        db.execute('SAVEPOINT profile_creation')
        
        try:
            # Create profile
            db.execute('INSERT INTO profiles (user_id, bio) VALUES (?, ?)', 
                       (user_id, 'New user profile'))
            
            # Create settings
            db.execute('INSERT INTO user_settings (user_id, theme) VALUES (?, ?)', 
                       (user_id, 'dark'))
            
            db.execute('RELEASE SAVEPOINT profile_creation')
        except Exception as e:
            # Rollback to savepoint
            db.execute('ROLLBACK TO SAVEPOINT profile_creation')
            print('Profile creation failed, continuing without profile')
        
        db.execute('COMMIT')
        return {'success': True, 'user_id': user_id}
    except Exception as e:
        db.execute('ROLLBACK')
        raise e

# Nested transactions using savepoints
def nested_transaction():
    try:
        db.execute('BEGIN TRANSACTION')
        
        # Outer transaction operations
        db.execute('INSERT INTO orders (customer_id, total) VALUES (?, ?)', (1, 100.00))
        
        # Nested transaction (savepoint)
        db.execute('SAVEPOINT order_items')
        
        try:
            # Inner transaction operations
            db.execute('INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)', 
                       (1, 101, 2))
            db.execute('INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)', 
                       (1, 102, 1))
            
            db.execute('RELEASE SAVEPOINT order_items')
        except Exception as e:
            db.execute('ROLLBACK TO SAVEPOINT order_items')
            raise e
        
        db.execute('COMMIT')
    except Exception as e:
        db.execute('ROLLBACK')
        raise e

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;
    }
}

# Read Committed example
def read_committed_example():
    try:
        db.execute('BEGIN TRANSACTION')
        
        # Read initial value
        initial = db.execute('SELECT balance FROM accounts WHERE user_id = ?', (1,)).fetchone()
        print('Initial balance:', initial[0])
        
        # Simulate concurrent update (in another connection)
        # This would be done by another process/thread
        
        # Read again - might see different value
        updated = db.execute('SELECT balance FROM accounts WHERE user_id = ?', (1,)).fetchone()
        print('Updated balance:', updated[0])
        
        db.execute('COMMIT')
    except Exception as e:
        db.execute('ROLLBACK')
        raise e

# Repeatable Read example
def repeatable_read_example():
    try:
        db.execute('BEGIN TRANSACTION')
        
        # First read
        first = db.execute('SELECT balance FROM accounts WHERE user_id = ?', (1,)).fetchone()
        print('First read:', first[0])
        
        # Simulate concurrent update (in another connection)
        # This would be done by another process/thread
        
        # Second read - should see same value
        second = db.execute('SELECT balance FROM accounts WHERE user_id = ?', (1,)).fetchone()
        print('Second read:', second[0])
        
        db.execute('COMMIT')
    except Exception as e:
        db.execute('ROLLBACK')
        raise e

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', 'alice@example.com']);
        await connection.run('INSERT INTO users (name, email) VALUES (?, ?)', 
                           ['Bob', 'bob@example.com']);
        
        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);
    }
}

from matsushibadb import MatsushibaPool

pool = MatsushibaPool(
    database='app.db',
    min_connections=5,
    max_connections=20
)

# Transaction with connection pool
async def pool_transaction():
    async with pool.get_connection() as conn:
        try:
            await conn.begin_transaction()
            
            # Multiple operations
            await conn.execute('INSERT INTO users (name, email) VALUES (?, ?)', 
                              ('Alice', 'alice@example.com'))
            await conn.execute('INSERT INTO users (name, email) VALUES (?, ?)', 
                              ('Bob', 'bob@example.com'))
            
            await conn.commit()
            return {'success': True}
        except Exception as e:
            await conn.rollback()
            raise e

# Batch operations with pool
async def batch_operations(operations):
    async with pool.get_connection() as conn:
        try:
            await conn.begin_transaction()
            
            for operation in operations:
                await conn.execute(operation['sql'], operation['params'])
            
            await conn.commit()
            return {'success': True, 'processed': len(operations)}
        except Exception as e:
            await conn.rollback()
            raise e

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;
        }
    }
}

# Advanced error handling
def robust_transaction(operations):
    transaction_started = False
    
    try:
        db.execute('BEGIN TRANSACTION')
        transaction_started = True
        
        results = []
        
        for operation in operations:
            try:
                result = db.execute(operation['sql'], operation['params'])
                results.append({
                    'success': True,
                    'result': result,
                    'operation': operation['name']
                })
            except Exception as operation_error:
                # Log operation error but continue with transaction
                print(f"Operation {operation['name']} failed: {str(operation_error)}")
                results.append({
                    'success': False,
                    'error': str(operation_error),
                    'operation': operation['name']
                })
                
                # Decide whether to continue or abort
                if operation.get('critical', False):
                    raise operation_error
        
        db.execute('COMMIT')
        return {
            'success': True,
            'results': results,
            'message': 'Transaction completed'
        }
    except Exception as e:
        if transaction_started:
            db.execute('ROLLBACK')
        
        return {
            'success': False,
            'error': str(e),
            'results': results if 'results' in locals() else []
        }

# Retry logic for failed transactions
import asyncio

async def retry_transaction(operation, max_retries=3):
    for attempt in range(1, max_retries + 1):
        try:
            return await operation()
        except Exception as e:
            if 'database is locked' in str(e) and attempt < max_retries:
                # Wait before retry
                await asyncio.sleep(0.1 * attempt)
                continue
            raise e

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;
    }
}

# Batch operations for better performance
def batch_insert(records):
    try:
        db.execute('BEGIN TRANSACTION')
        
        stmt = db.prepare('INSERT INTO users (name, email, age) VALUES (?, ?, ?)')
        
        for record in records:
            stmt.execute(record['name'], record['email'], record['age'])
        
        stmt.close()
        db.execute('COMMIT')
        
        return {'success': True, 'count': len(records)}
    except Exception as e:
        db.execute('ROLLBACK')
        raise e

# Optimized transaction with prepared statements
def optimized_transaction(operations):
    prepared_stmts = {}
    
    try:
        db.execute('BEGIN TRANSACTION')
        
        # Prepare all statements first
        for operation in operations:
            if operation['sql'] not in prepared_stmts:
                prepared_stmts[operation['sql']] = db.prepare(operation['sql'])
        
        # Execute operations
        for operation in operations:
            prepared_stmts[operation['sql']].execute(operation['params'])
        
        # Close prepared statements
        for stmt in prepared_stmts.values():
            stmt.close()
        
        db.execute('COMMIT')
        return {'success': True}
    except Exception as e:
        # Close statements on error
        for stmt in prepared_stmts.values():
            try:
                stmt.close()
            except:
                # Ignore close errors
                pass
        
        db.execute('ROLLBACK')
        raise e

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
        };
    }
}

def process_order(order_data):
    try:
        db.execute('BEGIN TRANSACTION')
        
        # 1. Create order
        order_result = db.execute('''
            INSERT INTO orders (customer_id, total_amount, status)
            VALUES (?, ?, ?)
        ''', (order_data['customer_id'], order_data['total'], 'pending'))
        
        order_id = order_result.lastrowid
        
        # 2. Add order items
        for item in order_data['items']:
            # Check inventory
            inventory = db.execute('''
                SELECT stock FROM products WHERE id = ?
            ''', (item['product_id'],)).fetchone()
            
            if not inventory or inventory[0] < item['quantity']:
                raise Exception(f"Insufficient stock for product {item['product_id']}")
            
            # Add order item
            db.execute('''
                INSERT INTO order_items (order_id, product_id, quantity, price)
                VALUES (?, ?, ?, ?)
            ''', (order_id, item['product_id'], item['quantity'], item['price']))
            
            # Update inventory
            db.execute('''
                UPDATE products SET stock = stock - ? WHERE id = ?
            ''', (item['quantity'], item['product_id']))
        
        # 3. Process payment
        payment_result = db.execute('''
            INSERT INTO payments (order_id, amount, method, status)
            VALUES (?, ?, ?, ?)
        ''', (order_id, order_data['total'], order_data['payment_method'], 'completed'))
        
        # 4. Update order status
        db.execute('''
            UPDATE orders SET status = ?, payment_id = ?
            WHERE id = ?
        ''', ('completed', payment_result.lastrowid, order_id))
        
        db.execute('COMMIT')
        
        return {
            'success': True,
            'order_id': order_id,
            'message': 'Order processed successfully'
        }
    except Exception as e:
        db.execute('ROLLBACK')
        return {
            'success': False,
            'error': str(e)
        }

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 };
    }
}

def register_user(user_data):
    try:
        db.execute('BEGIN TRANSACTION')
        
        # 1. Create user account
        user_result = db.execute('''
            INSERT INTO users (username, email, password_hash, status)
            VALUES (?, ?, ?, ?)
        ''', (user_data['username'], user_data['email'], 
              user_data['password_hash'], 'active'))
        
        user_id = user_result.lastrowid
        
        # 2. Create user profile
        db.execute('''
            INSERT INTO profiles (user_id, first_name, last_name, bio)
            VALUES (?, ?, ?, ?)
        ''', (user_id, user_data['first_name'], user_data['last_name'], 
              user_data.get('bio', '')))
        
        # 3. Set default preferences
        db.execute('''
            INSERT INTO user_preferences (user_id, theme, notifications, language)
            VALUES (?, ?, ?, ?)
        ''', (user_id, 'light', 1, 'en'))
        
        # 4. Create user settings
        db.execute('''
            INSERT INTO user_settings (user_id, privacy_level, email_notifications)
            VALUES (?, ?, ?)
        ''', (user_id, 'public', 1))
        
        # 5. Log registration event
        db.execute('''
            INSERT INTO user_events (user_id, event_type, description)
            VALUES (?, ?, ?)
        ''', (user_id, 'registration', 'User account created'))
        
        db.execute('COMMIT')
        
        return {
            'success': True,
            'user_id': user_id,
            'message': 'User registered successfully'
        }
    except Exception as e:
        db.execute('ROLLBACK')
        
        # Handle specific errors
        if 'UNIQUE constraint failed' in str(e):
            if 'username' in str(e):
                return {'success': False, 'error': 'Username already exists'}
            elif 'email' in str(e):
                return {'success': False, 'error': 'Email already exists'}
        
        return {'success': False, 'error': str(e)}

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

​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