PostgreSQL中并发读写字符串列如何防止更新覆盖？

你的问题属于典型的读-改-写模式下的并发丢失更新：两个事务同时读取初始值，各自修改后提交，后提交的会覆盖前一个的修改。针对你的场景（中间有10-20秒的耗时操作），以下是几种可行的解决方案，按推荐程度排序：

1. 乐观锁（推荐，适合长耗时中间操作） #

乐观锁通过版本号检测并发冲突，不主动加锁，性能更好，适配你的场景：

步骤： #

• 给目标表添加一个版本号列：

  ALTER TABLE tablename ADD COLUMN version INT DEFAULT 1;
  

• 修改代码，读取时同时获取版本号，更新时校验版本号：

  def replace(person):
      # 读取字符串和版本号
      cur.execute("SELECT stringcol, version FROM tablename")
      fetchedstring, current_version = cur.fetchone()
  
      # 此处耗时10-20秒的逻辑
      if person == 'jack':
          # 精准匹配"1:"避免误替换其他位置的1
          newstring = fetchedstring.replace("1:", "blue:")
      elif person == 'joe':
          newstring = fetchedstring.replace("2:", "green:")
      else:
          return
  
      # 更新时校验版本号，仅版本匹配才执行更新
      cur.execute(
          "UPDATE tablename SET stringcol = %s, version = version + 1 WHERE version = %s",
          (newstring, current_version)
      )
      # 若受影响行数为0，说明存在并发修改，需重试
      if cur.rowcount == 0:
          replace(person)  # 可根据实际情况优化重试逻辑，比如限制重试次数
      else:
          conn.commit()
  

• 第一个提交的事务会将版本号加1，第二个事务的UPDATE会因版本不匹配返回0行受影响，此时需要重新读取最新值再执行修改。

2. 悲观锁（不推荐，仅适合短操作场景） #

悲观锁在读取时直接锁定该行，阻止其他事务读取或修改，直到当前事务提交。但你的中间操作耗时10-20秒，会导致锁长时间持有，降低并发性能，甚至引发锁等待超时：

def replace(person):
    # 加FOR UPDATE锁，其他事务读取该行时会被阻塞
    cur.execute("SELECT stringcol FROM tablename FOR UPDATE")
    fetchedstring = cur.fetchone()

    # 耗时逻辑
    if person == 'jack':
        newstring = fetchedstring.replace("1:", "blue:")
    elif person == 'joe':
        newstring = fetchedstring.replace("2:", "green:")

    cur.execute("UPDATE tablename SET stringcol = %s", (newstring,))
    conn.commit()

注意：仅当中间耗时操作极短时才考虑这种方式，否则会严重影响系统并发能力。

3. 直接在数据库层面执行修改（最佳实践，若逻辑可迁移） #

如果修改逻辑能直接在数据库中实现，完全规避"读-改-写"流程，这是最安全高效的方式——数据库会保证更新的原子性：

Jack的更新SQL：

UPDATE tablename 
SET stringcol = regexp_replace(stringcol, '(^|_)1:', '\1blue:', 'g');

Joe的更新SQL：

UPDATE tablename 
SET stringcol = regexp_replace(stringcol, '(^|_)2:', '\1green:', 'g');

这种方式下，每个UPDATE都是原子操作，PostgreSQL会自动处理并发，不会出现丢失更新的问题。若你的耗时逻辑也能通过SQL或存储过程实现，优先选择该方案。

关于事务隔离级别说明 #

PostgreSQL默认的可重复读隔离级别仅能保证事务内读取的一致性，无法解决这种"读-改-写"的并发冲突——两个事务会在各自的快照中读取初始值，提交时后写的会覆盖先写的。因此必须通过锁或版本控制来解决问题。

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