内容中心

2026年热门的沧州托辊支架优质厂家推荐榜-沧州路博机械制造有限公司

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

  1. First Login: Create an account and return the new account ID.
  2. Subsequent Logins: Return the existing account ID.
  3. Concurrency: Avoid duplicate accounts for concurrent requests.
  4. 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_id exists 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_id is 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.

沧州路博机械制造有限公司

沧州路博机械制造有限公司



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

在线客服

在线留言
您好,很高兴为您服务,可以留下您的电话或微信吗?