If you remove the transaction and do an atomic update, the second request (writing to a neighboring table) may fail — how to ensure consistency?

If you remove the transaction and perform an atomic update, but the second request (for example, a record in a neighboring table) may fail, the following approaches can be used to ensure data consistency:

1. Idempotency and compensating operations: if the second request fails, a compensating operation is triggered, which rolls back the first request or corrects the state.

2. Using queues and event sourcing: the first request is recorded, and the write to the second table is done asynchronously via a queue. If the write fails, you can retry without data loss.

3. Two-phase commit (2PC): although more complex, a protocol can be implemented that guarantees consistency between two operations.

4. Change logging and periodic synchronization: record changes in a log and periodically synchronize data between tables.

Example using a queue in Go:

// After successfully updating the first table, send an event to the queue
err := updateFirstTable(data)
if err != nil {
    return err
}

err = sendToQueue(dataForSecondTable)
if err != nil {
    // Log the error to retry later
}

Thus, eventual consistency is achieved, and data is not lost.