How to avoid LOST UPDATE in database

A lost update will happen when there are many transactions which concurrently modify on one column

This is one transaction that update amount for the person which has id = 1

func updatePersonWith(db *sqlx.DB, ctx context.Context, extra int) {
   context := ctx
   tx, err := db.BeginTx(context, &sql.TxOptions{Isolation: sql.LevelDefault})
   if err != nil {
      log.Infof("update1 Create tx fail %v", err)
   }

   rows, err := tx.Query(`select amount from person where id=$1`, 1)
   if err != nil {
      log.Infof("Exec sql failed %v", err)
   }

   var amount int   for rows.Next() {
      if err := rows.Err(); err != nil {
         log.Infof("Exect sql get amount1 failed %v", err)
      }

      err = rows.Scan(&amount)
      log.Infof("amount= %d", amount)
      break
   }
   rows.Close()

   //just for test
   time.Sleep(time.Second*3)

   amount = amount + extra   log.Infof("amount= %d with extra= %d", amount,extra)

   _, err = tx.ExecContext(context, `update person SET amount =$2 where id=$1`, 1, amount)
   if err != nil {
      log.Infof("update1 failed %v with extra %d", err,extra)

   }

   //time.Sleep(time.Second*5)   log.Infof("Starting commit with extra %d",extra)
   err = tx.Commit()
   if err != nil {
      log.Infof("commit failed %v with extra %d", err,extra)
   }

}

func selectAmout(db *sqlx.DB, ctx context.Context){

   context := ctx
   tx, err := db.BeginTx(context, &sql.TxOptions{Isolation: sql.LevelDefault})
   if err != nil {
      log.Infof("select amount tx fail %v", err)
   }
   rows, err := tx.Query(`select amount from person where id=$1`, 1)
   if err != nil {
      log.Infof("Exect sql failed %v", err)
   }



   var amount int   for rows.Next() {
      if err := rows.Err(); err != nil {
         log.Infof("Exect sql get amount1 failed %v", err)
      }

      err = rows.Scan(&amount)
      log.Infof("amount= %d", amount)
      break
   }
   rows.Close()
}

func update(db *sqlx.DB, ctx context.Context) {
   go update1(db, ctx, 10)
   go update1(db, ctx, 20)

   // just wait for complete
   time.Sleep(time.Second * 10)

   selectAmout(db, ctx)

}

------log----------------

INFO[0000] amount= 130
INFO[0000] amount= 130
INFO[0003] amount= 150 with extra= 20
INFO[0003] Starting commit with extra 20
INFO[0003] amount= 140 with extra= 10
INFO[0003] Starting commit with extra 10


INFO[0010] amount= 140  

==> both transactions was succeeded => result should be 160, but it lost update with extra = 20

Because

func updatePersonWith(db *sqlx.DB, ctx context.Context, extra int) {
   context := ctx
   tx, err := db.BeginTx(context, &sql.TxOptions{Isolation: sql.LevelDefault})

So what is the solution for this case

func update1(db *sqlx.DB, ctx context.Context, extra int) {
   context := ctx
   tx, err := db.BeginTx(context, &sql.TxOptions{Isolation: sql.LevelSerializable})

-------------log-----------------

INFO[0000] amount= 140                                  
INFO[0000] amount= 140                                  
INFO[0003] amount= 160 with extra= 20                   
INFO[0003] amount= 150 with extra= 10                   
INFO[0003] Starting commit with extra 10                
INFO[0003] update1 failed pq: could not serialize access due to concurrent update with extra 20 
INFO[0003] Starting commit with extra 20                
INFO[0003] commit failed pq: Could not complete operation in a failed transaction with extra 20 
INFO[0010] amount= 150   

==>one transaction with extra=20 failed => result should be 150

===> in this case, we should try to excute the transaction with extra =20 again

PS: You can avoid lost update by use versioning for another column in your table then

your table should have a column called VERSION and type is INT

then the Update command: "update table_name Set amount = $1 where id = $2 and version =$3"

with $3 = current version