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  }