centos postgresql并发控制方法

并发控制总体思路

• 以 MVCC（多版本并发控制） 为基础，读写互不阻塞，写不阻塞读、读不阻塞写在 READ COMMITTED/REPEATABLE READ/可序列化快照 SSI 下均成立。
• 通过选择合适的事务隔离级别（PostgreSQL 实际实现为三种：READ COMMITTED、REPEATABLE READ、SERIALIZABLE；“READ UNCOMMITTED”与“READ COMMITTED”行为一致）来平衡一致性与并发度。
• 在需要“当前读”或排他修改时，使用 显式锁定（如 SELECT FOR UPDATE/SHARE、ROW EXCLUSIVE、ACCESS EXCLUSIVE 等）。
• 对跨事务、跨会话的资源竞争，使用 咨询锁（Advisory Locks） 实现应用级互斥。
• 通过 保存点（SAVEPOINT） 做部分回滚，降低冲突成本。

事务隔离级别与现象

• 标准现象与 PostgreSQL 实现要点如下：

• 设置方式：
  • 会话级：BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; …
  • 会话默认：SET default_transaction_isolation = ‘serializable’;
  • 注意：SET TRANSACTION 必须在事务块中使用。

显式锁定与典型用法

• 常用 SQL 与锁模式映射：
  • SELECT：表级 ACCESS SHARE（与 ACCESS EXCLUSIVE 冲突）。
  • SELECT FOR UPDATE/SHARE：表级 ROW SHARE（与 EXCLUSIVE/ACCESS EXCLUSIVE 冲突），行级获得排他/共享行锁。
  • INSERT/UPDATE/DELETE：表级 ROW EXCLUSIVE（与 SHARE/SHARE ROW EXCLUSIVE/EXCLUSIVE/ACCESS EXCLUSIVE 冲突），行级排他锁。
  • CREATE INDEX（非并发）：表级 SHARE；CREATE INDEX CONCURRENTLY：SHARE UPDATE EXCLUSIVE。
  • VACUUM（非 FULL）、ANALYZE、部分 ALTER TABLE：SHARE UPDATE EXCLUSIVE。
  • DROP/TRUNCATE/REINDEX/CLUSTER/VACUUM FULL：ACCESS EXCLUSIVE（与所有模式冲突）。
• 显式加锁示例：
  • LOCK TABLE t IN ACCESS EXCLUSIVE MODE NOWAIT;（不等待，冲突即报错）
• 冲突与死锁：不同事务对同一对象持有冲突锁会互相等待；PostgreSQL 会检测并终止其中一个事务。避免死锁的常用做法是按固定顺序获取锁、尽量缩短事务、减少持有锁的范围。

监控与诊断

• 查看当前锁与等待关系：
  • 查询视图 pg_locks，结合 pg_stat_activity 定位阻塞会话与锁模式；必要时对热点对象设置 lock_timeout 或改用 NOWAIT 快速失败。
• 观察事务与隔离：
  • 使用 txid_current() 查看当前事务 ID；在排查可重复读/序列化问题时，结合 xmin/xmax 理解行版本可见性。
• 典型诊断路径：
  • 发现长事务或锁等待 → 检查应用是否及时提交/回滚 → 核对是否缺少 FOR UPDATE/SHARE 或锁粒度过大 → 评估是否需要 SERIALIZABLE 或改为 READ COMMITTED 并重构逻辑。

CentOS 上的并发相关配置建议

• 连接与内存（示例为单机估算，需结合实际负载压测微调）：
  • 用连接池（如 PgBouncer）控制活跃连接，避免把 max_connections 设得过大；连接开销高，过多连接会加剧锁等待与上下文切换。
  • 常见起点：shared_buffers ≈ 内存的 25%（不超过约 40%）；work_mem 按“每个排序/哈希操作”估算，避免过大导致内存争用；effective_cache_size 设为内存的 50%–75% 供成本估算使用；maintenance_work_mem 在大表创建索引/VACUUM 时适当增大。
• 操作系统资源限制（/etc/security/limits.conf）：
  • 提升数据库专用用户（如 postgres）的 nproc 与 nofile，避免“Too many connections/open files”。
• I/O 平滑：
  • 调整 vm.dirty_background_ratio 或 vm.dirty_background_bytes，降低大内存机器一次性刷脏带来的 I/O 毛刺。
• 示例（仅示意，需按硬件与应用调优）：
  • shared_buffers = 4GB；work_mem = 64MB；effective_cache_size = 12GB；maintenance_work_mem = 1GB；max_connections = 300（配合连接池使用）。