在Scala Skunk中回滚多事务的正确方式是什么？

业务场景 #

• 需要删除一行数据
• 同一张表中可能存在多条引用该行的数据，删除该行后，所有引用它的行需更新为其他值或null，避免数据库出现“悬空指针”
• 若删除或任何更新操作失败，需将数据库回滚至初始状态

背景说明 #

Postgres不支持嵌套事务，因此最初计划使用savepoint来实现需求。

初始实现代码 #

postgres.use { s =>
  s.prepare(updateGenreParent).flatMap { ps =>
    s.transaction.use { t =>
      me.headOption match {
        case None => throw UnknownGenreError
        case Some(dg) =>
          dg.parent match {
            case None => ???
            case Some(p) => // Node is middle, realign the tree
              val savepoint = t.savepoint
              myChildren
                .traverse(c => {
                  for {
                    _  <- Logger[F].println("Updating parent for " + c.uuid + " With " + p)
                    sp <- savepoint
                    _ <- ps.execute(Some(GenreParentId(p.value)) ~ c.toplevel ~ c.uuid).recoverWith {
                      case e: Exception =>
                        Logger[F].println(e) *> t.rollback(sp)
                    }
                  } yield ()
                })
                .flatMap(r => {
                  s.prepare(deleteById).flatMap { ps2 =>
                    savepoint.flatMap(
                      sp =>
                        ps2.execute(id).as(id).recoverWith {
                          case e: Exception =>
                            Logger[F].println(e) *> t.rollback(sp) *> ApplicativeError[F, Throwable]
                              .raiseError(e)
                        }
                    )
                  }
                })
          }
      }
    }
  }
}

遇到的问题 #

• 代码能正常完成更新和删除操作，但无法验证回滚功能是否正常工作。Skunk文档中的基础回滚演示使用constraints，并不适用于当前表或删除查询。尝试在更新后、删除前让Postgres不可达来测试回滚，但未成功。
• Typelevel Discord社区的开发者指出，不要嵌套prepare/transaction语句，这种写法会交错无关逻辑，可能引发问题，例如以下写法就不推荐：

s.prepare(updateGenreParent).flatMap { ps =>
  s.transaction.use { t =>
    val savepoint = t.savepoint
    ....... 
    s.prepare(deleteById).flatMap { ps2 => .... restore(savepoint)... }
  }
}

• 如果改用flatMap链式调用事务，又会导致delete查询丢失对原始savepoint的追踪。

核心问题 #

在Scala + Cats + Skunk中，如何正确执行此类包含多步操作的事务回滚？

备选方案的顾虑 #

曾考虑记录所有已执行的更改，使用Defer创建重试循环手动将所有行恢复至初始状态，但这会引入新的问题——恢复操作本身也可能失败。

Postgres查询日志 #

2023-05-17 23:05:10.535 EDT [46120] LOG:  statement: RESET ALL
2023-05-17 23:05:10.716 EDT [46120] LOG:  statement: BEGIN
2023-05-17 23:05:10.799 EDT [46120] LOG:  statement: SAVEPOINT savepoint_6
2023-05-17 23:05:10.882 EDT [46120] LOG:  execute statement_5/portal_7:
              UPDATE genres
              SET parent_id=$1, top_level=$2
              WHERE uuid=$3

2023-05-17 23:05:10.882 EDT [46120] DETAIL:  parameters: $1 = NULL, $2 = NULL, $3 = 'b82fb0c0-a8d4-434e-849b-f026ff198713'
2023-05-17 23:05:10.947 EDT [46120] LOG:  execute statement_5/portal_9:
              UPDATE genres
              SET parent_id=$1, top_level=$2
              WHERE uuid=$3

2023-05-17 23:05:10.947 EDT [46120] DETAIL:  parameters: $1 = NULL, $2 = NULL, $3 = 'b82fb0c0-a8d4-434e-849b-f026ff198713'
2023-05-17 23:05:10.977 EDT [46120] LOG:  execute statement_5/portal_11:
              UPDATE genres
              SET parent_id=$1, top_level=$2
              WHERE uuid=$3

2023-05-17 23:05:10.977 EDT [46120] DETAIL:  parameters: $1 = 'b82fb0c0-a8d4-434e-849b-f026ff198713', $2 = 'b82fb0c0-a8d4-434e-849b-f026ff198713', $3 = 'e9bce582-d8e8-485c-a01d-04cee2cd26ca'
2023-05-17 23:05:11.082 EDT [46120] LOG:  execute statement_12/portal_14: DELETE FROM genres WHERE uuid = $1
2023-05-17 23:05:11.082 EDT [46120] DETAIL:  parameters: $1 = '467005e0-84fd-4729-a5cc-6fe1cd75787b'
2023-05-17 23:05:11.098 EDT [46120] LOG:  statement: COMMIT
2023-05-17 23:05:11.130 EDT [46120] LOG:  statement: UNLISTEN *
2023-05-17 23:05:11.146 EDT [46120] LOG:  statement: RESET ALL

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