Practice/Microsoft/Design a database query execution system

Design a database query execution system

Object Oriented DesignMust

Problem

Design and implement a comprehensive query execution engine that manages database connections, executes SQL queries, and handles results efficiently. Your system should support connection pooling, transaction management, query caching, and robust error handling with automatic retry mechanisms.

The system needs to handle concurrent query execution, maintain connection pools to avoid overhead, support ACID transactions, and provide intelligent caching to optimize repeated queries. It should also gracefully handle failures with configurable retry logic and proper resource cleanup.

Requirements

1. DatabaseConnection: Implement a class representing a single database connection with methods to open, close, and check connection status
2. ConnectionPool: Design a connection pool that maintains reusable connections, with configurable pool size and automatic connection recycling
3. QueryExecutor: Create a query executor that handles SQL query execution, implements result caching, and provides retry logic for failed queries
4. Transaction: Implement transaction support with begin, commit, and rollback capabilities, ensuring ACID properties
5. QueryEngine: Design the main interface that orchestrates all components and provides a clean API for users
6. Error Handling: Implement comprehensive error handling with meaningful error messages and automatic retry with exponential backoff
7. Resource Management: Ensure proper cleanup of connections and resources using context managers or similar patterns
8. Thread Safety: Make the connection pool thread-safe for concurrent query execution

Constraints

• Connection pool size: 1 to 100 connections
• Query cache size: up to 1000 entries (use LRU eviction)
• Maximum retry attempts: 0 to 10
• Query timeout: configurable, default 30 seconds
• Support for both read and write queries
• Memory efficient result handling for large result sets

Examples

Example 1: Basic Query Execution

engine = QueryEngine(database="products_db", pool_size=5) engine.connect() results = engine.execute("SELECT * FROM products WHERE price > 100") print(results.row_count) # Output: 15 engine.disconnect()

Example 2: Transaction Management

` engine = QueryEngine(database="orders_db") engine.connect()

transaction = engine.begin_transaction() try: transaction.execute("INSERT INTO orders VALUES (1, 'pending')") transaction.execute("UPDATE inventory SET quantity = quantity - 1") transaction.commit() except Exception as e: transaction.rollback() print(f"Transaction failed: {e}") finally: engine.disconnect() `

Example 3: Query Caching

` engine = QueryEngine(database="analytics_db", enable_cache=True) engine.connect()

First execution - hits database

results1 = engine.execute("SELECT COUNT(*) FROM events") # Takes 500ms

Second execution - uses cache

results2 = engine.execute("SELECT COUNT(*) FROM events") # Takes 5ms

engine.disconnect() `

Example 4: Connection Pool Usage

` engine = QueryEngine(database="web_db", pool_size=3) engine.connect()

Multiple concurrent queries share connections from pool

results1 = engine.execute("SELECT * FROM users") results2 = engine.execute("SELECT * FROM sessions") results3 = engine.execute("SELECT * FROM logs")

Pool automatically manages connection allocation and reuse

print(f"Active connections: {engine.pool.active_count}") # Output: 3

engine.disconnect() `