使用Go操作PostgreSQL 11.7时出现异常锁问题求助

首先，这种跨表锁阻塞的现象不太可能是PostgreSQL本身的问题，更大概率是Go标准库database/sql的连接池行为，或是pq驱动的使用方式不当导致的。下面拆解核心原因和验证/修复步骤：

1. 对db.Close()的误解是核心隐患 #

你提到在各处使用defer db.Close()，但这里存在关键误区：

• sql.DB是Go的数据库连接池对象，db.Close()的作用是关闭整个连接池，而不是释放当前操作使用的单个连接。
• 正常情况下，你应该只在应用退出时调用一次db.Close()，而不是每个查询/操作后都调用——频繁关闭连接池会导致连接复用逻辑混乱，反而可能造成连接泄漏或会话残留。

2. 连接池复用导致的会话残留锁 #

PostgreSQL的锁是和**数据库会话（连接）**绑定的，而不是单个查询。如果某个会话执行了COPY TO STDOUT后，没有正确释放连接（或者连接被连接池复用），但该会话之前在目标表（你要ALTER的表）上持有未释放的锁（比如某个未提交的事务里的查询锁），就会出现跨操作的锁阻塞：

• 你看到的阻塞进程是COPY TO STDOUT，但实际阻塞ALTER的是该会话之前未释放的目标表锁——因为会话活跃了12小时，而COPY只运行了几分钟，说明会话被连接池复用了，之前的操作留下了未释放的锁（比如未提交/回滚的事务）。

3. 验证步骤 #

要确认这个问题，你可以：

• 检查阻塞ALTER的会话的完整历史查询，而不仅仅是当前显示的COPY命令——PostgreSQL的pg_stat_activity里的query字段默认显示当前查询，但你可以查pg_stat_activity.query_history（PostgreSQL 11+支持，需要开启track_activity_query_size）或者通过日志查看该会话的所有操作，确认它是否在目标表上有未完成的事务或未释放的锁。
• 检查你的Go代码中是否存在未正确提交/回滚的事务：比如使用db.Begin()开启事务后，没有在所有分支路径执行Commit()或Rollback()，导致事务挂起，会话持有锁被连接池复用。

4. 修复措施 #

针对Go代码层面，你需要调整连接池和事务的使用方式：

• 不要在每个操作后调用db.Close()：只在应用生命周期结束时（比如main函数退出前）调用一次即可。
• 严格管理事务生命周期：所有通过db.Begin()创建的事务，必须确保在defer中执行回滚，并且在操作完成后正确提交：

  tx, err := db.Begin()
  if err != nil {
      // 错误处理
      return
  }
  defer func() {
      if r := recover(); r != nil {
          _ = tx.Rollback()
      } else if err != nil {
          _ = tx.Rollback()
      }
  }()
  
  // 执行你的操作，比如COPY、查询等
  _, err = tx.Exec("COPY ... TO STDOUT")
  if err != nil {
      // 错误处理
      return
  }
  
  err = tx.Commit()
  if err != nil {
      // 错误处理
  }
  

• 调整连接池参数：如果连接池中的连接长期闲置，可以设置db.SetMaxIdleConns(0)强制不保留闲置连接，或者缩短db.SetConnMaxLifetime()的时长，避免复用长时间活跃的会话。
• 检查pq驱动版本：Go 1.13.8对应的pq驱动可能存在旧版本的bug，尝试升级到最新兼容版本（注意Go 1.13.x的兼容性）。

5. 排除PostgreSQL本身的问题 #

虽然概率极低，但可以确认PostgreSQL的锁机制是否正常：

• 手动执行一个COPY TO STDOUT到某个表，然后立即关闭会话，再执行目标表的ALTER TABLE RENAME，看是否会阻塞——如果不会，就完全排除PostgreSQL的问题。

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