> ## Documentation Index
> Fetch the complete documentation index at: https://docs.db.matsushiba.co/llms.txt
> Use this file to discover all available pages before exploring further.

# Transactions

> Learn how to use transactions in MatsushibaDB to ensure data consistency, atomicity, and reliability in your applications.

# 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

<CodeGroup>
  ```javascript Node.js theme={null}
  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 };
      }
  }
  ```

  ```python Python theme={null}
  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)}
  ```
</CodeGroup>

### Advanced Transaction Management

<CodeGroup>
  ```javascript Node.js theme={null}
  // 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;
      }
  }
  ```

  ```python Python theme={null}
  # 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
  ```
</CodeGroup>

## Transaction Isolation Levels

### Understanding Isolation Levels

```sql theme={null}
-- 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

<CodeGroup>
  ```javascript Node.js theme={null}
  // 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;
      }
  }
  ```

  ```python Python theme={null}
  # 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
  ```
</CodeGroup>

## Connection Pooling and Transactions

### Pool-based Transaction Management

<CodeGroup>
  ```javascript Node.js theme={null}
  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);
      }
  }
  ```

  ```python Python theme={null}
  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
  ```
</CodeGroup>

## Error Handling in Transactions

### Comprehensive Error Handling

<CodeGroup>
  ```javascript Node.js theme={null}
  // 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;
          }
      }
  }
  ```

  ```python Python theme={null}
  # 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
  ```
</CodeGroup>

## Performance Optimization

### Transaction Performance Tips

<CodeGroup>
  ```javascript Node.js theme={null}
  // 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;
      }
  }
  ```

  ```python Python theme={null}
  # 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
  ```
</CodeGroup>

## Real-world Examples

### E-commerce Order Processing

<CodeGroup>
  ```javascript Node.js theme={null}
  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
          };
      }
  }
  ```

  ```python Python theme={null}
  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)
          }
  ```
</CodeGroup>

### User Registration with Profile

<CodeGroup>
  ```javascript Node.js theme={null}
  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 };
      }
  }
  ```

  ```python Python theme={null}
  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)}
  ```
</CodeGroup>

## Best Practices

<Steps>
  <Step title="Keep Transactions Short">
    Minimize transaction duration to reduce lock contention and improve concurrency.
  </Step>

  <Step title="Handle Errors Properly">
    Always implement proper error handling with rollback on failures.
  </Step>

  <Step title="Use Savepoints Sparingly">
    Use savepoints for complex operations but avoid deep nesting.
  </Step>

  <Step title="Prepare Statements Outside Transactions">
    Prepare statements before starting transactions for better performance.
  </Step>

  <Step title="Avoid Long-Running Transactions">
    Don't perform I/O operations or external API calls within transactions.
  </Step>

  <Step title="Test Transaction Logic">
    Thoroughly test transaction logic with various failure scenarios.
  </Step>

  <Step title="Monitor Transaction Performance">
    Monitor transaction duration and identify performance bottlenecks.
  </Step>

  <Step title="Use Appropriate Isolation Levels">
    Choose the right isolation level based on your concurrency requirements.
  </Step>
</Steps>

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