Skip to content

Bulk update/delete on Postgres can commit some documents after another document's hook rolls back the shared transaction #19819

Open

Description

@harrygantz

Package: payload 3.94.0, @payloadcms/drizzle 3.94.0, @payloadcms/db-postgres 3.94.0

What happens

A bulk update or delete (by where) saves every matched document at the same time on one shared req and one transaction (collections/operations/update.js: Promise.all(docs.map(processDocument)); delete.js: docs.map(async …) then Promise.all).

If a hook of document A runs a nested Local API operation with req and that operation fails, the nested operation's catch calls killTransaction(req). That rolls back the shared transaction and deletes req.transactionID. This happens even when the hook catches the error, which @payloadcms/plugin-search's sync does.

Document B is still mid-save. When it reaches its write, drizzle's getTransaction finds no transactionID (or no session for it) and falls back to adapter.drizzle (utilities/getTransaction.js). So B's row, its version, and its own nested hook writes commit outside any transaction. A later nested operation may also initTransaction a brand-new transaction on the same req, which another concurrent document's write then joins and commits.

The result: the operation reports A's failure, but B is saved, and part of the work that was meant to be atomic is committed.

Reproduction (outline)

  1. A collection with an afterChange hook that calls req.payload.create({ collection: 'other', data: invalid, req }) inside try/catch, for one specific document.
  2. payload.update({ collection, where: { id: { in: [a, b] } }, data: { title: 'x' } }).
  3. Hold B's db.updateOne until A's rollback (for example with a spy on rollbackTransaction). B's change is then stored, while A's was rolled back.

Why bulkOperationsSingleTransaction doesn't help

  • update.js starts a transaction per document on the shared req. After the first failure, later documents each open their own transaction and commit.
  • delete.js still creates every document's promise at once (docs.map(async …)), even in the "sequential" branch.

Suggested fixes (either helps)

  1. getTransaction should throw, not fall back to the plain connection, when the request carried a transactionID whose session is gone. A write that expected a transaction must never autocommit.
  2. When a bulk operation runs inside a transaction, process documents one after another, and fail the whole operation as soon as req.transactionID changed or disappeared during a document (a nested failure ended the transaction), so nothing after that document runs outside it. Ordinary per-document refusals (validation, access, locks) can keep today's behaviour: listed in errors while the others save.

Our workaround

We ship a pnpm patch on payload that does (2) in collections/operations/update.js and delete.js, with no behaviour change when there is no transaction. We can share the diff if that helps.

Trade-offs we accepted

  • Inside a transaction, bulk writes are sequential, so large selections take longer and hold row locks for the whole batch.
  • We first tried rolling everything back on any refused document, but that left non-transactional side effects of earlier documents (removed upload files, sent emails) describing changes that never happened. So we only fail the operation when the transaction itself ended mid-batch.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions