[Stability] Reduce fragmented transaction boundaries caused by frequent manual commits in S3 workflows

[chromonav]

Branch reviewed: s3

Problem

Several S3 upload/migration/cleanup flows call frappe.db.commit() repeatedly inside request or batch loops. This fragments transactions and can leave partially-applied state when a later step in the same logical workflow fails.

Evidence (file + line)

• aws_integration/aws_integration/api/s3.py#L423
• aws_integration/aws_integration/s3/backup.py#L135
• aws_integration/aws_integration/s3/backup.py#L160
• aws_integration/aws_integration/s3/backup.py#L188
• aws_integration/aws_integration/s3/backup.py#L221
• aws_integration/aws_integration/s3/backup.py#L244
• aws_integration/aws_integration/s3/backup.py#L281
• aws_integration/aws_integration/s3/backup.py#L300
• aws_integration/aws_integration/s3/backup.py#L458
• aws_integration/aws_integration/s3/backup.py#L468
• aws_integration/aws_integration/s3/backup.py#L613
• aws_integration/aws_integration/s3/handlers.py#L104
• aws_integration/aws_integration/s3/handlers.py#L119
• aws_integration/aws_integration/s3/handlers.py#L143
• aws_integration/aws_integration/s3/handlers.py#L155
• aws_integration/aws_integration/s3/scheduler.py#L99
• aws_integration/aws_integration/s3/scheduler.py#L113
• aws_integration/aws_integration/s3/scheduler.py#L135
• aws_integration/aws_integration/s3/scheduler.py#L149
• aws_integration/aws_integration/s3/scheduler.py#L246
• aws_integration/aws_integration/s3/scheduler.py#L257
• aws_integration/aws_integration/s3/scheduler.py#L269
• aws_integration/aws_integration/s3/scheduler.py#L383
• aws_integration/aws_integration/s3/scheduler.py#L488
• aws_integration/aws_integration/s3/scheduler.py#L537
• aws_integration/aws_integration/s3/scheduler.py#L559
• aws_integration/aws_integration/s3/scheduler.py#L573
• aws_integration/aws_integration/s3/scheduler.py#L585
• aws_integration/aws_integration/s3/scheduler.py#L595

Risk / impact

• Partial writes in File/S3 metadata synchronization paths.
• Hard-to-debug recovery after mid-loop failures.
• Higher lock contention and extra transaction overhead under load.

Replication suggestion

1. Configure batch size > 1 and process a mixed batch where one item fails mid-way.
2. Observe DB state after run: some rows committed with updated S3 fields while later rows are not, and completion signaling still progresses.
3. Compare behavior with one transaction-per-batch or savepoint-based strategy.

Resolution suggestion

• Replace per-item commits with controlled transaction scopes:
  • One commit per logical batch, or
  • Savepoints for per-item isolation with final commit once batch section is stable.
• Keep explicit commits only where boundary crossing is intentional and documented.
• For crash-safety, combine idempotent status flags + retry-safe transitions instead of frequent commits.

Acceptance criteria

• Commit cadence is documented and reduced to explicit transaction boundaries.
• No per-row commit in hot loops unless justified by comment + test.
• Failure in one item does not leave inconsistent state for dependent metadata transitions.
• Regression test covers partial-failure batch behavior.