121 lines
2.7 KiB
Go
121 lines
2.7 KiB
Go
package pgutil
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
"sync"
|
|
"testing"
|
|
"time"
|
|
)
|
|
|
|
// TestExecuteTx verifies transaction retry using the classic
|
|
// example of write skew in bank account balance transfers.
|
|
func TestWithTx(t *testing.T) {
|
|
db, err := sql.Open(
|
|
"postgres",
|
|
"host=127.0.0.1 dbname=test sslmode=disable user=test password=test")
|
|
if err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
|
|
if err := DropAllTables(db); err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
|
|
defer db.Close()
|
|
|
|
initStmt := `
|
|
CREATE TABLE t (acct INT PRIMARY KEY, balance INT);
|
|
INSERT INTO t (acct, balance) VALUES (1, 100), (2, 100);
|
|
`
|
|
if _, err := db.Exec(initStmt); err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
|
|
type queryI interface {
|
|
Query(string, ...interface{}) (*sql.Rows, error)
|
|
}
|
|
|
|
getBalances := func(q queryI) (bal1, bal2 int, err error) {
|
|
var rows *sql.Rows
|
|
rows, err = q.Query(`SELECT balance FROM t WHERE acct IN (1, 2);`)
|
|
if err != nil {
|
|
return
|
|
}
|
|
defer rows.Close()
|
|
balances := []*int{&bal1, &bal2}
|
|
i := 0
|
|
for ; rows.Next(); i++ {
|
|
if err = rows.Scan(balances[i]); err != nil {
|
|
return
|
|
}
|
|
}
|
|
if i != 2 {
|
|
err = fmt.Errorf("expected two balances; got %d", i)
|
|
return
|
|
}
|
|
return
|
|
}
|
|
|
|
txRunner := SerialTxRunner{100 * time.Millisecond, 800 * time.Millisecond}
|
|
|
|
runTxn := func(wg *sync.WaitGroup, iter *int) <-chan error {
|
|
errCh := make(chan error, 1)
|
|
go func() {
|
|
*iter = 0
|
|
errCh <- txRunner.WithTx(db, func(tx *sql.Tx) error {
|
|
*iter++
|
|
bal1, bal2, err := getBalances(tx)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
// If this is the first iteration, wait for the other tx to
|
|
// also read.
|
|
if *iter == 1 {
|
|
wg.Done()
|
|
wg.Wait()
|
|
}
|
|
// Now, subtract from one account and give to the other.
|
|
if bal1 > bal2 {
|
|
if _, err := tx.Exec(`
|
|
UPDATE t SET balance=balance-100 WHERE acct=1;
|
|
UPDATE t SET balance=balance+100 WHERE acct=2;
|
|
`); err != nil {
|
|
return err
|
|
}
|
|
} else {
|
|
if _, err := tx.Exec(`
|
|
UPDATE t SET balance=balance+100 WHERE acct=1;
|
|
UPDATE t SET balance=balance-100 WHERE acct=2;
|
|
`); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return nil
|
|
})
|
|
}()
|
|
return errCh
|
|
}
|
|
|
|
var wg sync.WaitGroup
|
|
wg.Add(2)
|
|
var iters1, iters2 int
|
|
txn1Err := runTxn(&wg, &iters1)
|
|
txn2Err := runTxn(&wg, &iters2)
|
|
if err := <-txn1Err; err != nil {
|
|
t.Errorf("expected success in txn1; got %s", err)
|
|
}
|
|
if err := <-txn2Err; err != nil {
|
|
t.Errorf("expected success in txn2; got %s", err)
|
|
}
|
|
if iters1+iters2 <= 2 {
|
|
t.Errorf("expected retries between the competing transactions; "+
|
|
"got txn1=%d, txn2=%d", iters1, iters2)
|
|
}
|
|
bal1, bal2, err := getBalances(db)
|
|
if err != nil || bal1 != 100 || bal2 != 100 {
|
|
t.Errorf("expected balances to be restored without error; "+
|
|
"got acct1=%d, acct2=%d: %s", bal1, bal2, err)
|
|
}
|
|
}
|