多节点架构下并发场景中task_info表单插入多更新的数据库设计方案咨询

Alright, let's tackle this problem step by step. You've got a multi-threaded app (cloud or on-prem) hitting a shared database, with the key requirement that only one thread inserts into task_info for a given logical task—others should update instead. And you've got strict constraints: no unique constraints on task_info, no full table locks, must work across Oracle, SQL Server, MySQL/MariaDB, only use DB and Memcache, minimal changes to legacy code.

This approach balances low overhead, cross-database compatibility, and minimal code changes, while addressing all your constraints.

Core Idea #

We use Memcache as a lightweight front-door lock to reduce DB concurrency pressure, and rely on database row-level locks + transactions to guarantee atomicity even if Memcache goes down. The Memcache lock is a performance optimization, not a single source of truth—so data safety doesn't depend on it.

Step 1: Acquire Memcache Distributed Lock (Per Task) #

First, each thread tries to grab a lock tied to the specific task_id you're operating on. This prevents a flood of threads hitting the database at once.

• Use Memcache's add command (it's atomic—only succeeds if the key doesn't exist yet) to create a lock key like task_info_op_lock:{task_id}.
• Set an expiration time (e.g., 30 seconds, adjust based on your typical operation duration) to avoid deadlocks if a thread crashes.
  • Pseudo-code example: memcache.add(lock_key, "locked", 30)
• If add succeeds: proceed to database operations.
• If add fails: either retry after a short backoff, or fall straight to the DB logic (the DB locks will catch it anyway).

Step 2: Atomic DB Operation in a Transaction #

Wrap your insert/update logic in a database transaction to ensure consistency. Use cross-database compatible row-level lock syntax to prevent race conditions:

1. Lock-aware Query
   Run a query that tries to fetch the existing task_info record for your task_id, with a lock that skips already locked records (so other threads don't block):

   • MySQL/MariaDB: SELECT id FROM task_info WHERE task_id = ? FOR UPDATE SKIP LOCKED
   • SQL Server: SELECT id FROM task_info WITH (UPDLOCK, READPAST) WHERE task_id = ?
   • Oracle: SELECT id FROM task_info WHERE task_id = ? FOR UPDATE SKIP LOCKED
     You can encapsulate these in a DAO layer that picks the right SQL based on the DB type—no changes needed to your business code.

2. Branch Logic

   • If the query returns a record: Run your UPDATE statement on task_info with the fetched id.
   • If no record is returned: Run your INSERT statement to create the new task_info entry.

3. Commit the Transaction
   Once the insert/update is done, commit the transaction to release the row lock.

Step 3: Release the Memcache Lock #

After the transaction succeeds or fails, delete the Memcache lock key to free it up for other threads:

• Pseudo-code: memcache.delete(lock_key)

Why This Works for Your Constraints #

• Cross-Database Compatibility: The row-level lock syntax is supported across all your target DBs, and can be abstracted away in a DAO layer.
• No Full Table Locks: We only lock the specific task_info record (if it exists), so other tasks aren't blocked.
• Legacy Retry Safe: No unique constraints added to task_info, so your existing retry logic remains intact.
• Low Invasion: You only need to wrap your existing insert/update logic with the lock check and transactional block—no major code refactoring or new services.
• Memcache Failure Resilient: If Memcache goes down, the DB's row-level locks still ensure only one thread inserts the record. Memcache just reduces unnecessary DB load.

Alternative: Leverage new_table's Unique Constraint as a Fallback #

If you can tweak the order of operations slightly, you can use the existing unique constraint on new_table (instance_info_id + task_info_id) to add an extra layer of safety:

1. First, attempt to insert into new_table with your instance_info_id and target task_info_id.
2. If the insert fails due to unique constraint violation: Another thread already linked this task_info to an instance—so run an UPDATE on task_info.
3. If the insert succeeds: Proceed with the Memcache + DB insert logic from the main solution to create the task_info record.

This adds another guardrail but requires adjusting when you write to new_table, so it's slightly more invasive than the main solution.

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