Day 2: MySQL Schema Design & Distributed Locking

Day 2 focused on building the database foundation and preventing race conditions. I designed a comprehensive MySQL schema for the trading engine and implemented Redis-based distributed locking to ensure account state consistency.

What I Built

• MySQL Schema: Complete database design with 5 core tables (account, trades, pending_orders, decisions, audit_log)
• Alembic Migrations: Async-compatible migration system with proper MySQL UUID() function handling
• Distributed Locking: Redis-based LockManager with atomic operations using Lua scripts
• Comprehensive Tests: 11 unit tests covering all locking scenarios and edge cases
• Database Constraints: Foreign keys, check constraints, and proper indexing for performance

Database Schema Design

The schema follows a single-source-of-truth pattern with the account table as the central entity. All trades are immutable records, while pending_orders serves as a write-ahead log for order management.

Core Tables

-- Account (single source-of-truth)
CREATE TABLE account (
    id VARCHAR(36) NOT NULL DEFAULT (UUID()),
    initial_balance NUMERIC(18, 8) NOT NULL,
    current_balance NUMERIC(18, 8) NOT NULL,
    cumulative_pnl NUMERIC(18, 8) NOT NULL DEFAULT '0',
    max_drawdown_pct NUMERIC(5, 2) NOT NULL DEFAULT '0',
    version INTEGER NOT NULL DEFAULT '1',
    updated_at DATETIME NOT NULL DEFAULT now(),
    updated_by VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT check_positive_balance CHECK (current_balance > 0),
    CONSTRAINT check_max_drawdown_range CHECK (max_drawdown_pct >= 0 AND max_drawdown_pct <= 100)
);

-- Trades (immutable record)
CREATE TABLE trades (
    id VARCHAR(36) NOT NULL DEFAULT (UUID()),
    account_id VARCHAR(36) NOT NULL,
    timestamp DATETIME NOT NULL,
    symbol VARCHAR(10) NOT NULL,
    action ENUM('BUY', 'SELL') NOT NULL,
    shares NUMERIC(12, 4) NOT NULL,
    entry_price NUMERIC(12, 4) NOT NULL,
    exit_price NUMERIC(12, 4),
    strategy_name VARCHAR(255) NOT NULL,
    pnl NUMERIC(18, 8),
    PRIMARY KEY (id),
    FOREIGN KEY (account_id) REFERENCES account(id)
);

Distributed Locking Implementation

To prevent race conditions when multiple strategies might try to trade simultaneously, I implemented a Redis-based distributed locking system.

LockManager Class

class LockManager:
    def __init__(self, redis_client: redis.Redis):
        self.redis = redis_client

    async def acquire_lock(self, account_id: str, timeout: int = 30) -> str:
        """Acquire distributed lock for account."""
        lock_key = f"lock:account:{account_id}"
        lock_value = str(uuid4())

        # Use Lua script for atomic operation
        acquired = await self.redis.set(
            lock_key, lock_value,
            ex=timeout,
            nx=True  # Only set if key doesn't exist
        )
        return lock_value if acquired else None

    async def release_lock(self, account_id: str, lock_value: str) -> bool:
        """Release distributed lock."""
        lock_key = f"lock:account:{account_id}"

        # Lua script to ensure only lock owner can release
        lua_script = """
        if redis.call("get", KEYS[1]) == ARGV[1] then
            return redis.call("del", KEYS[1])
        else
            return 0
        end
        """

        released = await self.redis.eval(lua_script, 1, lock_key, lock_value)
        return released == 1

Architecture Decision

MySQL over PostgreSQL: I chose MySQL 8.4 for this project; a decision based on MySQL's familiarity for deployment scenarios and sufficient capabilities for a swing trading system. The main trade-off was MySQL's less sophisticated UUID handling (requiring explicit parentheses in defaults), but this was easily resolved.

Redis for Distributed Locking: Instead of database-level advisory locks, I chose Redis for its superior performance and explicit timeout handling. This provides better visibility into lock contention and prevents deadlocks in distributed scenarios.

Testing Results

All 11 unit tests pass, covering critical scenarios:

• Lock acquisition and release
• Concurrent access prevention
• Lock extension and timeout handling
• Context manager usage patterns
• Error conditions and edge cases

Next Steps

Day 3: FastAPI scaffold and account state machine implementation. I'll build the REST API foundation and implement strict state transitions to prevent invalid trading operations.

---

Follow @therealkamba on X for regular updates. View all posts →