跨数据库会话维护多用户状态的高效规范解决方案问询

Battle-Tested Solutions for Long-Lived, Cross-Session Editing in PostgreSQL #

Great question—this is a classic concurrency problem for shared data with long edit windows, and you’ve already correctly called out the flaws with naive approaches (trusting frontend timestamps, tying state to short-lived database sessions). Here are the standard, robust patterns to solve this in PostgreSQL:

1. Database-Stored Optimistic Locking (The Go-To for Most Cases) #

This approach avoids frontend trust issues and session binding by tracking edit state in a persistent database table, while leveraging version/timestamp checks to catch conflicts.

How it works:

• Step 1: Track edit sessions when the user starts editing
  Create a dedicated edit_sessions table to store active edit states:

  CREATE TABLE edit_sessions (
    session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INT NOT NULL, -- Your app's user identifier
    record_id INT NOT NULL, -- ID of the target record being edited
    record_version TIMESTAMP NOT NULL, -- Last updated timestamp of the record when editing started
    expires_at TIMESTAMP NOT NULL, -- Auto-expire stale edits (e.g., 2 hours)
    created_at TIMESTAMP DEFAULT NOW()
  );
  
  -- Add indexes for fast lookups
  CREATE INDEX idx_edit_sessions_record_id ON edit_sessions(record_id);
  CREATE INDEX idx_edit_sessions_expires_at ON edit_sessions(expires_at);
  

  When the user loads the edit page (runs the initial SELECT), insert a session record and return the session_id to the frontend (store it in a secure cookie or localStorage—never let the frontend track the version/timestamp itself):

  INSERT INTO edit_sessions (user_id, record_id, record_version, expires_at)
  VALUES ($1, $2, (SELECT last_updated FROM target_table WHERE id = $2), NOW() + INTERVAL '2 hours')
  RETURNING session_id;
  

• Step 2: Validate and apply updates safely
  When the user submits changes, pass the session_id along with the new data. Use a CTE to first validate the session is active and the record hasn’t changed, then apply the update:

  WITH valid_session AS (
    -- Lock the session to prevent concurrent validation conflicts
    SELECT record_version, record_id
    FROM edit_sessions
    WHERE session_id = $1 
      AND user_id = $2 
      AND expires_at > NOW()
    FOR UPDATE
  ), updated_record AS (
    -- Only update if the record's last_updated matches the version we stored
    UPDATE target_table
    SET column1 = $3, column2 = $4, last_updated = NOW()
    WHERE id = (SELECT record_id FROM valid_session)
      AND last_updated = (SELECT record_version FROM valid_session)
    RETURNING *
  )
  -- Refresh the session's expiry if the update succeeds
  UPDATE edit_sessions 
  SET expires_at = NOW() + INTERVAL '1 hour'
  WHERE session_id = $1;
  

  If the updated_record CTE returns 0 rows, that means either the session expired or another user modified the data—return a conflict error to the user, prompting them to refresh and re-apply changes.

• Bonus: Use version numbers instead of timestamps
  For even more reliability (avoids clock drift issues), replace the last_updated timestamp with an auto-incrementing version integer column (increment on every UPDATE). Store this version in edit_sessions instead of a timestamp, and validate against it in the UPDATE step.

• Housekeeping
  Use PostgreSQL’s pg_cron extension to periodically delete expired sessions and keep the table lean:

  SELECT cron.schedule('cleanup-stale-edit-sessions', '0 * * * *', 
    'DELETE FROM edit_sessions WHERE expires_at < NOW();');
  

2. Advisory Locks (For High-Conflict Scenarios) #

If your use case has extremely high conflict rates (users regularly edit the same records), optimistic locking might lead to frustrating retry cycles. In this case, use PostgreSQL’s advisory locks to reserve records for editing, but with safeguards to avoid permanent lockouts.

How it works:

• When a user starts editing, acquire an advisory lock tied to the record’s ID, and track the lock in the edit_sessions table:

  INSERT INTO edit_sessions (user_id, record_id, expires_at)
  VALUES ($1, $2, NOW() + INTERVAL '2 hours')
  RETURNING session_id;
  
  -- Acquire an exclusive lock for write edits
  SELECT pg_advisory_lock($2);
  

• Use a background cron job to release locks for expired sessions:

  SELECT pg_advisory_unlock(record_id)
  FROM edit_sessions
  WHERE expires_at < NOW();
  

• When the user submits updates, validate the session is active, apply the update, then release the lock:

  UPDATE target_table
  SET column1 = $3, last_updated = NOW()
  WHERE id = $2;
  
  SELECT pg_advisory_unlock($2);
  DELETE FROM edit_sessions WHERE session_id = $1;
  

• Caveats
  Advisory locks are tied to database sessions, so you need a way to release them if the user’s session drops (hence the cron job). This is more resource-heavy than optimistic locking, so only use it if conflict rates justify it.

Final Recommendation #

For most long-lived edit scenarios with moderate conflict rates, database-stored optimistic locking is the sweet spot: it’s secure (no frontend trust), scalable (no tied-up connections), and user-friendly (conflicts are resolved with a refresh instead of blocking edits entirely).

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