含依赖关系的大文件导入REST服务的并发性能优化方案咨询

Hey there, let's break down this problem step by step—since you're dealing with long-running import jobs, concurrency risks, and all-or-nothing persistence, we need a balanced approach that prioritizes performance without sacrificing data integrity. Here's the optimal playbook:

3-4 minute imports are way too long for synchronous HTTP requests. Instead:

• When a user uploads a file, immediately return a unique job_id and kick off an asynchronous worker task (use something like Celery, Redis Queue, or your platform's native job scheduler).
• Track job states (pending, processing, success, failed) in a dedicated import_jobs table or Redis. Let users poll for updates or use WebSockets to push status changes—this frees up your HTTP server to handle more concurrent requests.

Validation (duplicate checks + dependency checks) is where concurrency risks live. Don't lock the entire system for the full 4 minutes—split this into two phases:

Quick Pre-Validation (Fast Fail) #

First, knock out cheap, non-blocking checks to reject bad jobs early:

• Validate file format, required fields, and basic data types.
• Use database unique indexes to check for obvious duplicates (e.g., SELECT 1 FROM assets WHERE external_id = ? LIMIT 1). If a duplicate is found, fail the job immediately without wasting processing time.

Distributed Lock + Snapshot-Based Full Validation #

For the cross-job validation (checking against in-progress imports and existing assets):

• Grab a distributed lock (e.g., Redis RedLock or ZooKeeper) only during the validation phase—not the entire processing step. Keep lock hold time as short as possible.
• While holding the lock, take a snapshot of:
  1. All already persisted assets (use a read-only database transaction to avoid dirty reads).
  2. All pending assets from in-progress import jobs (store these in a Redis set as workers start processing).
• Run your duplicate and dependency checks against this snapshot. If everything checks out, release the lock and proceed to process the assets. If not, release the lock and fail the job.

Don't tie up database connections while transforming assets. Instead:

• Process the file's assets in memory (or temporary disk storage) in the worker—do mapping, enrichment, or any business logic here.
• Store intermediate results in a lightweight store like Redis Hashes (keyed by job_id + asset_id) instead of touching the main database. This keeps your database focused on persistence, not processing.

Since you need to save all assets at once, use a two-phase approach to avoid partial saves:

Prepare Phase #

• Batch-write all processed assets to a temporary table (e.g., temp_assets) with a job_id tag, or add a status = 'pending' column to your main assets table.
• This step uses bulk inserts (e.g., INSERT INTO temp_assets (job_id, ...) VALUES (...), (...), (...)) to minimize database round-trips.

Commit Phase #

• Re-acquire the distributed lock to block concurrent writes that could cause conflicts.
• Run a final quick check: ensure none of the pending assets have been added by another job while you were processing.
• If all is clear, atomically move the assets from temp_assets to the main table (or update status to active), and resolve any dependency relationships in the same database transaction.
• If anything fails, roll back the temp table changes and mark the job as failed. Release the lock immediately either way.

• Rate Limit Concurrent Jobs: Use a Redis Semaphore to cap the number of active import workers (e.g., 5 concurrent jobs) based on your database's write capacity. This prevents overwhelming your system.
• Isolate Jobs: Run each import job in a separate worker process/thread. If one job fails, it won't take down others.

• Cache unique asset identifiers (e.g., external IDs) in a Redis Set for fast duplicate checks. Refresh this cache periodically or after each successful import.
• Cache dependency relationships (e.g., asset_id -> dependent_asset_ids) in Redis Hashes. This cuts down on expensive join queries during validation.

• Assign a unique import_request_id to every upload. If the same ID is submitted again, return the existing job status instead of reprocessing.
• For transient failures (e.g., database connection blips), retry the commit phase a few times with backoff. Never retry validation failures—those are permanent.

内容的提问来源于stack exchange，提问作者ddzz