One Transaction or Half-Written Data: BeginTxx in sqlx
Org and member writes ran as separate statements; a mid-way failure left half-written data. One BeginTxx closes the gap.
TL;DR
Kartara had a design flaw. The organisation was written separately from its members. Using transactions fixed this issue.
The organisation row was there. Its roster was not.
Kartara, a regional organization-data portal built in Go on top of sqlx, had accepted a registration the night before and returned a clean success. The dashboard showed the new organisation. The member table showed nothing under it. The write that created the organisation had committed; the write that inserted its members had not. A failure between the two statements left a record that looked valid and behaved like a broken one. [2]
That was the moment the design stopped looking fine. The portal had been writing organisations and members as separate operations since the first version, and every test ran on a database that never failed mid-path. Production did not offer the same guarantee.
Two writes, one window for failure
The original flow was straightforward. Insert the organisation, get its generated id, delete the existing member rows for that organisation, insert the new member rows, return. Each step was its own statement, and each committed on its own. Nothing in the code tied them together, so nothing in the database could either.
The failure mode is not exotic. A request cancelled between the two statements, a connection dropped by a pool timeout, a process restart, or a constraint violation on a later insert all leave the same result: the organisation exists and the member list is either empty or stale. Users see a saved organisation; downstream reads see a half-written one.
Worse, the existence check ran before any of this, in its own query, outside any write. A concurrent delete could remove the organisation between the check and the insert, and the insert would still proceed against an id that no longer existed.
Moving every write under BeginTxx
The fix is to treat the whole operation as one unit. sqlx exposes the standard library transaction interface through BeginTxx, which returns a *sqlx.Tx. Every write inside the operation uses that handle instead of the *sqlx.DB, and a single Commit at the end makes the result visible. If anything returns an error first, the deferred Rollback discards all of it, and the database never sees a half-finished organisation. [1]
CreateWithMembers and UpdateWithMembers both follow this shape. The organisation row insert, the member delete, and the member insert all run against the same transaction. Member order is not left to the database; each row carries the request index, so the stored order matches the order the caller sent. The existence check moved inside the transaction as well, so the check and the write can no longer disagree.
Passing the *sqlx.Tx through the service functions rather than reaching for the global handle is what makes this work. The repository methods take a querier interface that both *sqlx.DB and *sqlx.Tx satisfy, so the same code path serves reads outside a transaction and writes inside one. [3]
The trade-off: two concurrent updates to the same organisation now serialize. A stricter isolation level would surface as a deadlock or a serialization error rather than a silent merge, and that is the point.
Here is the shape of the validation and the handler that maps it back to HTTP:
tx, err := db.BeginTxx(ctx, nil)
if err != nil {
return err
}
defer tx.Rollback()
if _, err := tx.ExecContext(ctx, insertOrg, org.Name); err != nil {
return err
}
// drop old members, then insert the new ones from the request
// order follows the payload index, not map iteration order
return tx.Commit()The %w verb keeps the sentinel reachable through errors.Is, so the handler can map validation failures to a 400 and missing rows to a 404 without string matching.
What the transaction actually guarantees
A transaction gives atomicity, not isolation from every other concern. It does not stop a slow query from holding locks, and it does not make the member insert cheap if the list is large. What it removes is the space between two writes where a partial result can live. Once the organisation write and the member writes share a transaction, the only two outcomes are the full organisation or no change at all.
A retry is not a substitute. Replaying a half-written organisation does not fix it; the second run inserts the members but the first run's empty organisation is already committed. Only the transaction removes the intermediate state.
The portal now returns an error whenever the member insert fails, and the organisation row it would have left behind never exists. The support message that started this is no longer reproducible, which is the only kind of fix worth trusting.
Whether the existence check belongs in the same transaction or in a separate validation pass is still open. Keeping it inside is correct but couples it to the write path; pulling it out reintroduces the race unless it takes a lock. For now, inside is the decision.