Loyalty programs represent real financial value. If a user can exploit race conditions to double-spend points or exchange points for vouchers simultaneously, the business incurs direct losses. Ensuring database-level transactional consistency under concurrent loads is paramount.

When handling points balances, we must avoid the traditional "read balance, calculate, write balance" pattern in code. Concurrent requests will read the same old balance, leading to inaccurate updates.

1. Pessimistic Locking with SELECT FOR UPDATE

To guarantee that only one transaction can modify a user's points balance at a time, we use PostgreSQL's row-level locking. The SELECT ... FOR UPDATE query locks the row until the transaction commits or rolls back:

func (r *PointsRepository) DeductPoints(ctx context.Context, userID string, amount int) error {
    tx, err := r.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()
    
    var currentBalance int
    err = tx.QueryRowContext(ctx, 
        "SELECT balance FROM user_points WHERE user_id = $1 FOR UPDATE", 
        userID).Scan(¤tBalance)
    if err != nil {
        return err
    }
    
    if currentBalance < amount {
        return ErrInsufficientBalance
    }
    
    _, err = tx.ExecContext(ctx, 
        "UPDATE user_points SET balance = balance - $1 WHERE user_id = $2", 
        amount, userID)
    if err != nil {
        return err
    }
    
    return tx.Commit()
}

2. Double-Entry Bookkeeping

Instead of merely updating a single balance column, a secure loyalty engine should record every point transaction in an immutable ledger (double-entry bookkeeping). Every credit and debit is saved as a new row in a ledger table, and the user's current balance is the sum of their ledger entries. This provides auditability and makes balance tampering impossible.

Deterministic Lock Ordering to Prevent Deadlocks in Points Transfers

When transferring loyalty points between two accounts inside a PostgreSQL transaction, always acquire SELECT ... FOR UPDATE row locks in ascending account-ID order so concurrent transfers can never deadlock.