To solve this problem, we need to design a system that efficiently handles user logins by creating an account for first-time users and returning the existing account ID for subsequent logins. The system must handle concurrency and minimize database operations.
Key Requirements
- First Login: Create an account and return the new account ID.
- Subsequent Logins: Return the existing account ID.
- Concurrency: Avoid duplicate accounts for concurrent requests.
- Minimize DB Operations: Reduce unnecessary database hits.
System Design
The solution combines caching (for fast lookups) and atomic database operations (to handle concurrency) to meet the requirements.
1. Database Schema
We use a relational database (PostgreSQL/MySQL) with a table to store accounts. The table includes a unique constraint on the user identifier to prevent duplicates.
SQL Schema:
CREATE TABLE accounts (
account_id INT AUTO_INCREMENT PRIMARY KEY,
user_id VARCHAR(255) NOT NULL UNIQUE, -- Unique user identifier (email/phone/OAuth ID)
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
2. Cache Layer
A distributed cache (e.g., Redis) is used to store the mapping of user_id → account_id for fast access. This reduces database hits for repeated logins.
3. Core Logic
The system follows these steps for each login request:
- Cache Check: First, check if the
user_idexists in the cache. If yes, return the cached account ID. - DB Insert: If the cache misses, attempt to insert the user into the database. This is atomic and ensures only one account is created for concurrent requests.
- Handle Duplicate: If the insert fails (due to duplicate
user_id), query the database for the existing account ID. - Cache Update: Store the account ID in the cache for future requests.
Implementation Code (Python Example)
Assuming we use Redis for caching and PostgreSQL for the database:
import redis
import psycopg2
from psycopg2 import IntegrityError
# Initialize Redis and DB connections
redis_client = redis.Redis(host="localhost", port=6379, db=0)
db_conn = psycopg2.connect(
dbname="mydb", user="user", password="pass", host="localhost"
)
db_cursor = db_conn.cursor()
def is_duplicate_key_error(e):
"""Check if the error is a duplicate key violation."""
return "unique constraint" in str(e) or "duplicate key" in str(e)
def get_account_id(user_id):
# Step 1: Check cache
cache_key = f"user:{user_id}:account_id"
cached_id = redis_client.get(cache_key)
if cached_id:
return int(cached_id)
# Step 2: Attempt to insert into DB
try:
insert_query = "INSERT INTO accounts (user_id) VALUES (%s) RETURNING account_id;"
db_cursor.execute(insert_query, (user_id,))
account_id = db_cursor.fetchone()[0]
db_conn.commit()
# Step 3: Cache the new account ID
redis_client.set(cache_key, account_id)
return account_id
except IntegrityError as e:
if is_duplicate_key_error(e):
# Step 4: Query existing account ID
select_query = "SELECT account_id FROM accounts WHERE user_id = %s;"
db_cursor.execute(select_query, (user_id,))
account_id = db_cursor.fetchone()[0]
# Step 5: Cache the existing account ID
redis_client.set(cache_key, account_id)
return account_id
else:
raise # Re-raise other integrity errors
except Exception as e:
db_conn.rollback()
raise # Handle other errors (e.g., connection issues)
Why This Works
- Concurrency: The database's unique constraint ensures only one insert succeeds for concurrent requests. Other requests get a duplicate error and fetch the existing account ID.
- Minimal DB Operations:
- New Users: 1 DB insert + cache update.
- Existing Users: Cache hit → 0 DB ops; cache miss → 1 insert (fail) + 1 select + cache update.
- Cache Efficiency: The mapping
user_id → account_idis immutable, so the cache can be permanent (no TTL) or have a long TTL.
Optimizations
- Connection Pooling: Use database connection pools to handle high concurrency.
- Read Replicas: For large-scale systems, use read replicas to offload select queries.
- Cache Hit Rate: Monitor the cache hit rate to ensure most requests are served from the cache.
This design efficiently handles user logins while meeting all the requirements. It is scalable, concurrent, and minimizes database operations.


作者声明:本文包含人工智能生成内容。