Aurora PostgreSQL 9.6.3单行列更新触发死锁的原因咨询

这个问题的核心原因其实是缺少匹配UPDATE条件的组合索引，导致数据库在执行UPDATE时不得不扫描并锁定多行，最终引发了循环等待的死锁场景——和你之前理解的「乱序更新多行触发死锁」本质是同一个逻辑，只是这个场景里的「多行锁定」是数据库执行过程中隐性发生的。

具体原因分析 #

1. 索引缺失导致全扫描/范围扫描
   你的UPDATE语句条件是WHERE uuid = %s AND activity_id = %s，但students表上并没有(uuid, activity_id)的组合索引或唯一约束，只有单独的activity_id索引和主键id索引。这意味着PostgreSQL无法直接定位到目标行，只能先通过activity_id索引找到所有匹配该activity_id的行，再逐个检查uuid是否符合条件。

2. 扫描过程中的行锁竞争
   在扫描这些行的过程中，每个事务会对扫描到的每一行加RowExclusiveLock（UPDATE操作的行锁）。业务高峰时，多个并发事务执行同一个UPDATE语句，它们扫描同activity_id下的行时，很可能会以相反的顺序锁定这些行（比如事务A从id小的行往大的扫，事务B从大的往小的扫）。

3. 循环等待触发死锁
   当两个事务都锁定了对方后续需要访问的行时，就会形成循环等待，触发死锁。哪怕你逻辑上只想更新单行，但数据库执行时因为索引缺失，已经在后台锁定了多行，满足了死锁的必要条件。

日志里显示的ShareLock on transaction等待，本质就是底层行锁的循环等待升级为事务级的锁等待。

解决方案 #

1. 添加组合唯一约束（优先）

首先给students表添加(uuid, activity_id)的唯一约束——这不仅能解决死锁问题，还能避免你的自定义UPSERT逻辑插入重复行：

ALTER TABLE students ADD CONSTRAINT students_uuid_activity_id_unique UNIQUE (uuid, activity_id);

这个约束会自动生成对应的唯一索引，让UPDATE语句可以直接通过索引定位到目标行，不需要扫描其他无关行，从根源上消除了多行锁定的可能。

2. 替换为原生UPSERT语法

PostgreSQL 9.5+支持原生的原子UPSERT操作，比你手动实现的UPDATE + INSERT更安全高效，数据库会自动处理锁的问题：

with self.connection.cursor() as cursor:
    cursor.execute("""
        INSERT INTO students (name, uuid, activity_id)
        VALUES (%s, %s, %s)
        ON CONFLICT (uuid, activity_id) DO UPDATE
        SET name = EXCLUDED.name
    """, (name, uuid, activityId))
    if cursor.rowcount <= 0:
        self.connection.rollback()
        raise BaseDao.NotUpserted("No student name was updated or inserted for activity_id %d and uuid %s" % (activityId, uuid))
    else:
        self.connection.commit()

注意：使用这个语法必须先创建上面的唯一约束，ON CONFLICT需要指定唯一约束或主键作为冲突判断依据。

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