Transaction Retry with Rollback

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"time"
)

var ErrConflict = errors.New("serialization conflict")

func WithTx(
	ctx context.Context,
	db *sql.DB,
	attempts int,
	work func(*sql.Tx) error,
) error {
	if attempts < 1 {
		attempts = 1
	}
	var last error
	for attempt := 0; attempt < attempts; attempt++ {
		tx, err := db.BeginTx(ctx, nil)
		if err != nil {
			return err
		}
		err = work(tx)
		if err == nil {
			if commitErr := tx.Commit(); commitErr == nil {
				return nil
			} else {
				last = commitErr
			}
		} else {
			_ = tx.Rollback()
			last = err
		}
		if !errors.Is(last, ErrConflict) {
			return last
		}
		select {
		case <-ctx.Done():
			return ctx.Err()
		case <-time.After(time.Duration(attempt+1) * 50 * time.Millisecond):
		}
	}
	return fmt.Errorf("transaction failed after %d attempts: %w", attempts, last)
}

func transfer(ctx context.Context, db *sql.DB, from, to, amount int64) error {
	return WithTx(ctx, db, 3, func(tx *sql.Tx) error {
		result, err := tx.ExecContext(
			ctx,
			"UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?",
			amount, from, amount,
		)
		if err != nil {
			return err
		}
		affected, err := result.RowsAffected()
		if err != nil {
			return err
		}
		if affected == 0 {
			return errors.New("insufficient funds")
		}
		_, err = tx.ExecContext(
			ctx,
			"UPDATE accounts SET balance = balance + ? WHERE id = ?",
			amount, to,
		)
		return err
	})
}

func main() {
	fmt.Println(transfer != nil, ErrConflict)
}