Wrap multi-step DB operations in transactions to prevent partial writes #9

New Issue

2026-04-20T09:35:10Z

sharang commented

2026-04-20 09:35:10 +00:00

Problem

Multi-step database operations that must succeed or fail atomically are executed as separate, unguarded DB calls. If the second call fails, the first write is committed and leaves orphaned or inconsistent data.

Key examples:

compliance/services/change_request_engine.py:36-52 — creates a change request record, then updates the source document in separate transactions
VVT creation with linked Loeschfrist entries
Control creation with associated evidence records

Required Actions

Identify all service methods that perform 2+ DB writes — audit compliance/services/
Wrap each in a db.begin_nested() savepoint or ensure the SQLAlchemy session is committed atomically at the end of the request via a single db.commit()
Verify rollback on exception: use try/except/rollback in every service that mutates multiple tables
Add a utility context manager atomic(db) to enforce this pattern consistently

Acceptance Criteria

No service method contains two separate db.commit() calls without explicit rollback handling
Test: simulate failure between two writes, verify first write is rolled back

## Problem Multi-step database operations that must succeed or fail atomically are executed as separate, unguarded DB calls. If the second call fails, the first write is committed and leaves orphaned or inconsistent data. Key examples: - `compliance/services/change_request_engine.py:36-52` — creates a change request record, then updates the source document in separate transactions - VVT creation with linked Loeschfrist entries - Control creation with associated evidence records ## Required Actions 1. Identify all service methods that perform 2+ DB writes — audit `compliance/services/` 2. Wrap each in a `db.begin_nested()` savepoint or ensure the SQLAlchemy session is committed atomically at the end of the request via a single `db.commit()` 3. Verify rollback on exception: use `try/except/rollback` in every service that mutates multiple tables 4. Add a utility context manager `atomic(db)` to enforce this pattern consistently ## Acceptance Criteria - No service method contains two separate `db.commit()` calls without explicit rollback handling - Test: simulate failure between two writes, verify first write is rolled back

sharang added this to the M2: Data Integrity & Reliability milestone 2026-04-20 09:35:10 +00:00

sharang added the data-integrity severity: high labels 2026-04-20 09:35:10 +00:00

sharang referenced this issue

2026-04-20 09:37:15 +00:00

Add transaction rollback tests for multi-step write operations #21

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: Benjamin_Boenisch/breakpilot-compliance#9