Advertisement
rmloveland

txn.Begin does not appear to work

Jul 24th, 2019
302
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Go 9.51 KB | None | 0 0
  1. package main
  2.  
  3. // Summary: Using txn := db.Begin() followed by txn.Exec(foo),
  4. // txn.{Commit,Rollback} as shown below does not appear to work (see log output at
  5. // bottom).  However passing everything through db.Exec(foo) does,
  6. // including sending db.Exec(`BEGIN`), db.Exec('ROLLBACK'), etc. (see
  7. // code in PR at https://github.com/cockroachdb/docs/pull/5049 for the
  8. // db.Exec version).
  9.  
  10. import (
  11.     "fmt"
  12.     "log"
  13.     "math"
  14.     "math/rand"
  15.     "time"
  16.  
  17.     // Import GORM-related packages.
  18.     "github.com/jinzhu/gorm"
  19.     _ "github.com/jinzhu/gorm/dialects/postgres"
  20.  
  21.     // Necessary in order to check for transaction retry error codes.
  22.     // See implementation below in `transferFunds`.
  23.     "github.com/lib/pq"
  24. )
  25.  
  26. // Account is our model, which corresponds to the "accounts" database
  27. // table.
  28. type Account struct {
  29.     ID      int `gorm:"primary_key"`
  30.     Balance int
  31. }
  32.  
  33. func main() {
  34.     // Connect to the "bank" database as the "maxroach" user.
  35.     const addr = "postgresql://root@localhost:26257/bank?sslmode=disable"
  36.     db, err := gorm.Open("postgres", addr)
  37.     if err != nil {
  38.         log.Fatal(err)
  39.     }
  40.     defer db.Close()
  41.  
  42.     // Set to `true` and GORM will print out all DB queries.
  43.     db.LogMode(true)
  44.  
  45.     // Automatically create the "accounts" table based on the Account
  46.     // model.
  47.     db.AutoMigrate(&Account{})
  48.  
  49.     // Insert two rows into the "accounts" table.
  50.     db.Create(&Account{ID: 1, Balance: 1000})
  51.     db.Create(&Account{ID: 2, Balance: 250})
  52.  
  53.     // The sequence of steps in this section is:
  54.     // 1. Print account balances.
  55.     // 2. Set up some Accounts and transfer funds between them.
  56.     // 3. Print account balances again to verify the transfer occurred.
  57.  
  58.     // Print balances before transfer.
  59.     printBalances(db)
  60.  
  61.     var amount = 100
  62.     var fromAccount Account
  63.     var toAccount Account
  64.  
  65.     db.First(&fromAccount, 1)
  66.     db.First(&toAccount, 2)
  67.  
  68.     // Transfer funds between accounts.  To handle any possible
  69.     // transaction retry errors, we add a retry loop with exponential
  70.     // backoff to the transfer logic (see implementation below).
  71.     if err := transferFunds(db, fromAccount, toAccount, amount); err != nil {
  72.         // If the error is returned, it's either:
  73.         //   1. Not a transaction retry error, i.e., some other kind of
  74.         //   database error that you should handle here.
  75.         //   2. A transaction retry error that has occurred more than N
  76.         //   times (defined by the `maxRetries` variable inside
  77.         //   `transferFunds`), in which case you will need to figure out
  78.         //   why your database access is resulting in so much contention
  79.         //   (see 'Understanding and avoiding transaction contention':
  80.         //   https://www.cockroachlabs.com/docs/stable/performance-best-practices-overview.html#understanding-and-avoiding-transaction-contention)
  81.         fmt.Println(err)
  82.     }
  83.  
  84.     // Print balances after transfer to ensure that it worked.
  85.     printBalances(db)
  86.  
  87.     // Delete accounts so we can start fresh when we want to run this
  88.     // program again.
  89.     deleteAccounts(db)
  90. }
  91.  
  92. func transferFunds(db *gorm.DB, fromAccount Account, toAccount Account, amount int) error {
  93.     if fromAccount.Balance < amount {
  94.         return fmt.Errorf("account %d balance %d is lower than transfer amount %d", fromAccount.ID, fromAccount.Balance, amount)
  95.     }
  96.  
  97.     var maxRetries = 3
  98.     for retries := 0; retries <= maxRetries; retries++ {
  99.         if retries == maxRetries {
  100.             return fmt.Errorf("hit max of %d retries, aborting", retries)
  101.         }
  102.  
  103.         // db.Exec("BEGIN")
  104.         txn := db.Begin()
  105.         txn.Exec(`SELECT now()`) // disable server-side auto-retries
  106.         if err := txn.Exec(
  107.             `SELECT crdb_internal.force_retry('1s'::INTERVAL)`,
  108.  
  109.             // `UPSERT INTO accounts (id, balance) VALUES
  110.             //    (?, ((SELECT balance FROM accounts WHERE id = ?) - ?)),
  111.             //    (?, ((SELECT balance FROM accounts WHERE id = ?) + ?))`,
  112.             // fromAccount.ID, fromAccount.ID, amount,
  113.             // toAccount.ID, toAccount.ID, amount
  114.         ).Error; err != nil {
  115.  
  116.             // We need to cast GORM's db.Error to *pq.Error so we can
  117.             // detect the Postgres transaction retry error code and
  118.             // handle retries appropriately.
  119.             pqErr := err.(*pq.Error)
  120.  
  121.             if pqErr.Code == "40001" {
  122.                 // Since this is a transaction retry error, we
  123.                 // ROLLBACK the transaction and sleep a little before
  124.                 // trying again.  Each time through the loop we sleep
  125.                 // for a little longer than the last time
  126.                 // (A.K.A. exponential backoff).
  127.                 txn.Rollback()
  128.                 // db.Exec("ROLLBACK")
  129.                 var sleepMs = math.Pow(2, float64(retries)) * 100 * (rand.Float64() + 0.5)
  130.                 time.Sleep(time.Millisecond * time.Duration(sleepMs))
  131.             } else {
  132.                 return err
  133.             }
  134.         } else {
  135.             // Happy case.  All went well, so we commit and break out
  136.             // of the retry loop.
  137.             txn.Commit()
  138.             // db.Exec("COMMIT")
  139.             break
  140.         }
  141.     }
  142.     return nil
  143. }
  144.  
  145. func printBalances(db *gorm.DB) {
  146.     var accounts []Account
  147.     db.Find(&accounts)
  148.     fmt.Printf("Balance at '%s':\n", time.Now())
  149.     for _, account := range accounts {
  150.         fmt.Printf("%d %d\n", account.ID, account.Balance)
  151.     }
  152. }
  153.  
  154. func deleteAccounts(db *gorm.DB) error {
  155.     // Used to tear down the accounts table so we can re-run this
  156.     // program.
  157.     err := db.Exec("DELETE from accounts where ID > 0").Error
  158.     if err != nil {
  159.         return err
  160.     }
  161.     return nil
  162. }
  163.  
  164. // -*- mode: compilation; default-directory: "~/work/code/deemphasize-savepoints/golang/" -*-
  165. // Compilation started at Wed Jul 24 13:30:55
  166.  
  167. // make -k
  168. // go run gorm-sample.go
  169.  
  170. // (/Users/rloveland/work/code/deemphasize-savepoints/golang/gorm-sample.go:43)
  171. // [2019-07-24 13:30:55]  [0.77ms]  INSERT INTO "accounts" ("id","balance") VALUES ('1','1000') RETURNING "accounts"."id"  
  172. // [1 rows affected or returned ]
  173.  
  174. // (/Users/rloveland/work/code/deemphasize-savepoints/golang/gorm-sample.go:44)
  175. // [2019-07-24 13:30:55]  [0.69ms]  INSERT INTO "accounts" ("id","balance") VALUES ('2','250') RETURNING "accounts"."id"  
  176. // [1 rows affected or returned ]
  177.  
  178. // (/Users/rloveland/work/code/deemphasize-savepoints/golang/gorm-sample.go:140)
  179. // [2019-07-24 13:30:55]  [0.76ms]  SELECT * FROM "accounts"    
  180. // [2 rows affected or returned ]
  181. // Balance at '2019-07-24 13:30:55.617543 -0400 EDT m=+0.019774217':
  182. // 1 1000
  183. // 2 250
  184.  
  185. // (/Users/rloveland/work/code/deemphasize-savepoints/golang/gorm-sample.go:58)
  186. // [2019-07-24 13:30:55]  [0.48ms]  SELECT * FROM "accounts"  WHERE ("accounts"."id" = 1) ORDER BY "accounts"."id" ASC LIMIT 1  
  187. // [1 rows affected or returned ]
  188.  
  189. // (/Users/rloveland/work/code/deemphasize-savepoints/golang/gorm-sample.go:59)
  190. // [2019-07-24 13:30:55]  [0.50ms]  SELECT * FROM "accounts"  WHERE ("accounts"."id" = 2) ORDER BY "accounts"."id" ASC LIMIT 1  
  191. // [1 rows affected or returned ]
  192.  
  193. // (/Users/rloveland/work/code/deemphasize-savepoints/golang/gorm-sample.go:98)
  194. // [2019-07-24 13:30:55]  [0.61ms]  SELECT now()  
  195. // [1 rows affected or returned ]
  196.  
  197. // (/Users/rloveland/work/code/deemphasize-savepoints/golang/gorm-sample.go:99)
  198. // [2019-07-24 13:30:55]  pq: restart transaction: crdb_internal.force_retry(): TransactionRetryWithProtoRefreshError: forced by crdb_internal.force_retry() 
  199.  
  200. // (/Users/rloveland/work/code/deemphasize-savepoints/golang/gorm-sample.go:99)
  201. // [2019-07-24 13:30:55]  [0.90ms]  SELECT crdb_internal.force_retry('1s'::INTERVAL)  
  202. // [0 rows affected or returned ]
  203.  
  204. // (/Users/rloveland/work/code/deemphasize-savepoints/golang/gorm-sample.go:98)
  205. // [2019-07-24 13:30:55]  [0.79ms]  SELECT now()  
  206. // [1 rows affected or returned ]
  207.  
  208. // (/Users/rloveland/work/code/deemphasize-savepoints/golang/gorm-sample.go:99)
  209. // [2019-07-24 13:30:55]  pq: restart transaction: crdb_internal.force_retry(): TransactionRetryWithProtoRefreshError: forced by crdb_internal.force_retry() 
  210.  
  211. // (/Users/rloveland/work/code/deemphasize-savepoints/golang/gorm-sample.go:99)
  212. // [2019-07-24 13:30:55]  [1.07ms]  SELECT crdb_internal.force_retry('1s'::INTERVAL)  
  213. // [0 rows affected or returned ]
  214.  
  215. // (/Users/rloveland/work/code/deemphasize-savepoints/golang/gorm-sample.go:98)
  216. // [2019-07-24 13:30:56]  [0.74ms]  SELECT now()  
  217. // [1 rows affected or returned ]
  218.  
  219. // (/Users/rloveland/work/code/deemphasize-savepoints/golang/gorm-sample.go:99)
  220. // [2019-07-24 13:30:56]  pq: restart transaction: crdb_internal.force_retry(): TransactionRetryWithProtoRefreshError: forced by crdb_internal.force_retry() 
  221.  
  222. // (/Users/rloveland/work/code/deemphasize-savepoints/golang/gorm-sample.go:99)
  223. // [2019-07-24 13:30:56]  [1.56ms]  SELECT crdb_internal.force_retry('1s'::INTERVAL)  
  224. // [0 rows affected or returned ]
  225. // hit max of 3 retries, aborting
  226.  
  227. // (/Users/rloveland/work/code/deemphasize-savepoints/golang/gorm-sample.go:140)
  228. // [2019-07-24 13:30:56]  [1.44ms]  SELECT * FROM "accounts"    
  229. // [2 rows affected or returned ]
  230. // Balance at '2019-07-24 13:30:56.533603 -0400 EDT m=+0.935833949':
  231. // 1 1000
  232. // 2 250
  233.  
  234. // (/Users/rloveland/work/code/deemphasize-savepoints/golang/gorm-sample.go:150)
  235. // [2019-07-24 13:30:56]  [3.81ms]  DELETE from accounts where ID > 0  
  236. // [2 rows affected or returned ]
  237.  
  238. // Compilation finished at Wed Jul 24 13:30:56
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement