github.com/acoshift/pgsql@v0.15.3/tx_test.go (about) 1 package pgsql_test 2 3 import ( 4 "database/sql" 5 "fmt" 6 "log" 7 "math/rand" 8 "sync" 9 "testing" 10 11 "github.com/acoshift/pgsql" 12 ) 13 14 func TestTx(t *testing.T) { 15 db := open(t) 16 defer db.Close() 17 18 _, err := db.Exec(` 19 drop table if exists test_pgsql_tx; 20 create table test_pgsql_tx ( 21 id int primary key, 22 value int 23 ); 24 insert into test_pgsql_tx ( 25 id, value 26 ) values 27 (0, 0), 28 (1, 0), 29 (2, 0); 30 `) 31 if err != nil { 32 t.Fatalf("prepare table error; %v", err) 33 } 34 defer db.Exec(`drop table test_pgsql_tx`) 35 db.SetMaxOpenConns(30) 36 37 opts := &pgsql.TxOptions{MaxAttempts: 10} 38 39 deposit := func(balance int) error { 40 return pgsql.RunInTx(db, opts, func(tx *sql.Tx) error { 41 var err error 42 43 // log.Println("deposit", balance) 44 var acc0, acc1 int 45 err = tx.QueryRow(`select value from test_pgsql_tx where id = 0`).Scan(&acc0) 46 if err != nil { 47 return err 48 } 49 err = tx.QueryRow(`select value from test_pgsql_tx where id = 1`).Scan(&acc1) 50 if err != nil { 51 return err 52 } 53 _, err = tx.Exec(`update test_pgsql_tx set value = $1 where id = 0`, acc0-balance) 54 if err != nil { 55 return err 56 } 57 _, err = tx.Exec(`update test_pgsql_tx set value = $1 where id = 1`, acc1+balance) 58 if err != nil { 59 return err 60 } 61 return nil 62 }) 63 } 64 withdraw := func(balance int) error { 65 return pgsql.RunInTx(db, opts, func(tx *sql.Tx) error { 66 var err error 67 68 // log.Println("withdraw", balance) 69 var acc0, acc1 int 70 err = tx.QueryRow(`select value from test_pgsql_tx where id = 1`).Scan(&acc1) 71 if err != nil { 72 return err 73 } 74 if acc1 < balance { 75 return fmt.Errorf("not enough balance to withdraw") 76 } 77 err = tx.QueryRow(`select value from test_pgsql_tx where id = 0`).Scan(&acc0) 78 if err != nil { 79 return err 80 } 81 _, err = tx.Exec(`update test_pgsql_tx set value = $1 where id = 0`, acc0+balance) 82 if err != nil { 83 return err 84 } 85 _, err = tx.Exec(`update test_pgsql_tx set value = $1 where id = 1`, acc1-balance) 86 if err != nil { 87 return err 88 } 89 return nil 90 }) 91 } 92 transfer := func(balance int) error { 93 return pgsql.RunInTx(db, opts, func(tx *sql.Tx) error { 94 var err error 95 96 // log.Println("transfer", balance) 97 var acc1, acc2 int 98 err = tx.QueryRow(`select value from test_pgsql_tx where id = 1`).Scan(&acc1) 99 if err != nil { 100 return err 101 } 102 if acc1 < balance { 103 return fmt.Errorf("not enough balance to transfer") 104 } 105 err = tx.QueryRow(`select value from test_pgsql_tx where id = 2`).Scan(&acc2) 106 if err != nil { 107 return err 108 } 109 _, err = tx.Exec(`update test_pgsql_tx set value = $1 where id = 1`, acc1-balance) 110 if err != nil { 111 return err 112 } 113 _, err = tx.Exec(`update test_pgsql_tx set value = $1 where id = 2`, acc2+balance) 114 if err != nil { 115 return err 116 } 117 return nil 118 }) 119 } 120 121 wg := sync.WaitGroup{} 122 for i := 0; i < 1000; i++ { 123 wg.Add(1) 124 go func() { 125 var err error 126 k := rand.Intn(3) 127 if k == 0 { 128 err = deposit(rand.Intn(100000)) 129 } else if k == 1 { 130 err = withdraw(rand.Intn(100000)) 131 } else { 132 err = transfer(rand.Intn(100000)) 133 } 134 if err != nil { 135 log.Println(err) 136 } 137 wg.Done() 138 }() 139 } 140 wg.Wait() 141 142 var result int 143 err = db.QueryRow(`select sum(value) from test_pgsql_tx`).Scan(&result) 144 if err != nil { 145 t.Fatalf("query result error; %v", err) 146 } 147 if result != 0 { 148 t.Fatalf("expected sum all value to be 0; got %d", result) 149 } 150 }