Have you worked with transactions at the business process level — SELECT FOR UPDATE, rollback on external API failure?

Yes, I have worked with transactions at the business process level in Go. I used SELECT FOR UPDATE to lock rows in the database to prevent race conditions during parallel operations. For example, when reserving an item in an order, I would first select with a lock, then update the quantity, and only after successful interaction with an external API would I confirm the transaction.

If the external API failed, I would call rollback to avoid saving changes to the database and leaving the system in an inconsistent state.

An example using database/sql and PostgreSQL:

ctx := context.Background()
tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return err
}

// Lock the row
row := tx.QueryRowContext(ctx, "SELECT quantity FROM products WHERE id=$1 FOR UPDATE", productID)
var quantity int
if err := row.Scan(&quantity); err != nil {
    tx.Rollback()
    return err
}

if quantity < needed {
    tx.Rollback()
    return errors.New("not enough stock")
}

// Call external API
if err := callExternalAPI(); err != nil {
    tx.Rollback()
    return err
}

// Update the quantity
_, err = tx.ExecContext(ctx, "UPDATE products SET quantity = quantity - $1 WHERE id = $2", needed, productID)
if err != nil {
    tx.Rollback()
    return err
}

return tx.Commit()

This approach guarantees the atomicity of the business operation and data consistency.