# Database Transaction Management

Provides safe, reliable database transaction management with automatic commit/rollback, row-level locking, and proper connection cleanup.

## Features

- **Automatic commit/rollback**: Transactions automatically commit on success, rollback on errors
- **Row-level locking**: Support for `FOR UPDATE` queries to prevent race conditions
- **Transaction isolation levels**: Support for READ COMMITTED, REPEATABLE READ, SERIALIZABLE
- **Connection management**: Proper cleanup of connections and cursors
- **Nested transaction support**: Works with existing connections
- **Connection pooling**: Compatible with existing database.py connection pool

## Basic Usage

```python
from database import transaction

# Simple transaction
with transaction() as (conn, cursor):
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    # Auto-commits on success, auto-rolls back on exception
```

## Row-Level Locking

Use `FOR UPDATE` to lock rows and prevent concurrent modifications:

```python
with transaction(lock=True) as (conn, cursor):
    # Lock the row for update
    cursor.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (account_id,))
    balance = cursor.fetchone()[0]

    if balance >= amount:
        cursor.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s",
                      (amount, account_id))
    else:
        raise ValueError("Insufficient funds")
```

## Transaction Isolation Levels

Set custom isolation levels for specific requirements:

```python
# SERIALIZABLE for strict consistency
with transaction(isolation_level='SERIALIZABLE') as (conn, cursor):
    cursor.execute("SELECT COUNT(*) FROM orders")
    count = cursor.fetchone()[0]
    cursor.execute("INSERT INTO orders (order_num) VALUES (%s)", (count + 1,))

# READ COMMITTED for better performance
with transaction(isolation_level='READ COMMITTED') as (conn, cursor):
    cursor.execute("SELECT * FROM users WHERE active = 1")
    users = cursor.fetchall()
```

## Using Existing Connections

Pass an existing connection to avoid creating a new one:

```python
conn = get_database_connection()
try:
    with transaction(conn=conn) as (conn, cursor):
        cursor.execute("INSERT INTO logs (message) VALUES (%s)", ("Event logged",))

    # Connection still open, can do more work
    with transaction(conn=conn) as (conn, cursor):
        cursor.execute("UPDATE stats SET count = count + 1")
finally:
    conn.close()
```

## Helper Function

Use `execute_in_transaction` for simple operations:

```python
from database.transactions import execute_in_transaction

def my_operation(conn, cursor):
    cursor.execute("INSERT INTO users (name) VALUES (%s)", ('Alice',))
    return cursor.lastrowid

user_id = execute_in_transaction(my_operation)
```

## Real-World Examples

### Atomic Money Transfer

```python
def transfer_money(from_account_id, to_account_id, amount):
    """Transfer money between accounts atomically."""
    with transaction(lock=True) as (conn, cursor):
        # Lock and fetch source balance
        cursor.execute(
            "SELECT balance FROM accounts WHERE id = %s FOR UPDATE",
            (from_account_id,)
        )
        from_balance = cursor.fetchone()[0]

        if from_balance < amount:
            raise ValueError("Insufficient funds")

        # Lock destination account
        cursor.execute(
            "SELECT id FROM accounts WHERE id = %s FOR UPDATE",
            (to_account_id,)
        )
        if not cursor.fetchone():
            raise ValueError("Destination account not found")

        # Perform transfer
        cursor.execute(
            "UPDATE accounts SET balance = balance - %s WHERE id = %s",
            (amount, from_account_id)
        )
        cursor.execute(
            "UPDATE accounts SET balance = balance + %s WHERE id = %s",
            (amount, to_account_id)
        )
```

### Idempotent Operations

Prevent duplicate processing using database locks:

```python
def process_payment(payment_id, user_id, amount):
    """Process payment idempotently."""
    with transaction(lock=True) as (conn, cursor):
        # Check if already processed (with lock)
        cursor.execute(
            "SELECT id FROM processed_payments WHERE payment_id = %s FOR UPDATE",
            (payment_id,)
        )

        if cursor.fetchone():
            return False  # Already processed

        # Process payment
        cursor.execute(
            "UPDATE accounts SET balance = balance + %s WHERE user_id = %s",
            (amount, user_id)
        )

        # Mark as processed
        cursor.execute(
            "INSERT INTO processed_payments (payment_id, processed_at) VALUES (%s, NOW())",
            (payment_id,)
        )

        return True
```

### Inventory Management

Prevent overselling with row-level locking:

```python
def purchase_item(item_id, quantity):
    """Purchase items with inventory check."""
    with transaction(lock=True) as (conn, cursor):
        # Lock inventory row
        cursor.execute(
            "SELECT quantity FROM inventory WHERE item_id = %s FOR UPDATE",
            (item_id,)
        )

        available = cursor.fetchone()
        if not available or available[0] < quantity:
            raise ValueError("Insufficient inventory")

        # Update inventory
        cursor.execute(
            "UPDATE inventory SET quantity = quantity - %s WHERE item_id = %s",
            (quantity, item_id)
        )

        # Create order
        cursor.execute(
            "INSERT INTO orders (item_id, quantity) VALUES (%s, %s)",
            (item_id, quantity)
        )
```

## Error Handling

Transactions automatically rollback on any exception:

```python
try:
    with transaction() as (conn, cursor):
        cursor.execute("UPDATE users SET credits = credits + 100 WHERE id = 1")

        # If this fails, the above UPDATE is rolled back
        cursor.execute("INSERT INTO transactions (user_id, amount) VALUES (1, 100)")

except mysql.connector.Error as e:
    print(f"Database error: {e}")
    # Transaction already rolled back automatically
```

## Testing

### Unit Tests (No Database Required)

```bash
# Run unit tests with mocks
pytest tests/test_transactions_unit.py -v
```

### Integration Tests (Requires MySQL)

```bash
# Run integration tests with live database
pytest tests/test_transactions_integration.py -v
```

## Performance Considerations

1. **Keep transactions short**: Long transactions hold locks and block other operations
2. **Use appropriate isolation levels**: Lower isolation = better performance
3. **Lock only what you need**: Use specific WHERE clauses with FOR UPDATE
4. **Connection pooling**: The module uses the existing connection pool for efficiency
5. **Avoid nested transactions**: MySQL doesn't support true nested transactions

## Common Patterns

### Pattern 1: Check-Then-Update

```python
with transaction(lock=True) as (conn, cursor):
    cursor.execute("SELECT status FROM orders WHERE id = %s FOR UPDATE", (order_id,))
    status = cursor.fetchone()[0]

    if status == 'pending':
        cursor.execute("UPDATE orders SET status = 'processed' WHERE id = %s", (order_id,))
```

### Pattern 2: Multi-Table Update

```python
with transaction() as (conn, cursor):
    cursor.execute("INSERT INTO orders (user_id, total) VALUES (%s, %s)", (user_id, total))
    order_id = cursor.lastrowid

    cursor.executemany(
        "INSERT INTO order_items (order_id, item_id, quantity) VALUES (%s, %s, %s)",
        [(order_id, item_id, qty) for item_id, qty in items]
    )

    cursor.execute("UPDATE users SET last_order_id = %s WHERE id = %s", (order_id, user_id))
```

### Pattern 3: Conditional Insert

```python
with transaction(lock=True) as (conn, cursor):
    cursor.execute("SELECT id FROM users WHERE email = %s FOR UPDATE", (email,))

    if cursor.fetchone():
        raise ValueError("User already exists")

    cursor.execute("INSERT INTO users (email, name) VALUES (%s, %s)", (email, name))
```

## Migration Guide

### Before (Manual Transaction Management)

```python
conn = get_database_connection()
try:
    cursor = conn.cursor()
    conn.start_transaction()

    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")

    conn.commit()
except Exception as e:
    conn.rollback()
    raise
finally:
    cursor.close()
    conn.close()
```

### After (Transaction Context Manager)

```python
with transaction() as (conn, cursor):
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
```

## Best Practices

1. **Always use context manager**: Don't manually manage commit/rollback
2. **Use FOR UPDATE for concurrent updates**: Prevents race conditions
3. **Validate before updating**: Check conditions inside the transaction
4. **Keep transactions short**: Release locks quickly
5. **Handle specific exceptions**: Catch and handle database errors appropriately
6. **Test with concurrent access**: Use integration tests to verify locking behavior
7. **Log transaction errors**: Include context for debugging

## Troubleshooting

### Deadlocks

If you encounter deadlocks:
- Always lock rows in consistent order
- Keep transactions short
- Use appropriate isolation levels
- Consider using SELECT ... FOR UPDATE NOWAIT

### Slow Transactions

If transactions are slow:
- Check for long-running queries
- Reduce transaction scope
- Use lower isolation levels if appropriate
- Review locking strategy

### Connection Pool Exhaustion

If you run out of connections:
- Ensure transactions complete quickly
- Check for connection leaks
- Increase pool size in database.py
- Review concurrent transaction patterns