handling mysql transaction mysql, the beego way

Lets use the beegos transaction method to do transactional queries in mysql

package main

import (
    "fmt"
    "github.com/astaxie/beego/orm"
    _ "github.com/go-sql-driver/mysql"
)

func init() {
    orm.RegisterDriver("mysql", orm.DR_MySQL)
    orm.RegisterDataBase("default", "mysql", "root:@/mts_dev?charset=utf8")
}

func main() {
    o := orm.NewOrm()
    o.Using("default")

    var error_list []interface{}

    err := o.Begin()
    if err != nil {
        error_list = append(error_list, err)
    }
    for i := 0; i < 5; i++ {
        if val, err := o.Raw("INSERT into tickets set locked_by = ? ,booked_by = ?", i, i*i).Exec(); err == nil {
            fmt.Println("inserted", i, (i * i), "ID", val)
        } else {
            error_list = append(error_list, err)
            fmt.Println("Error in insert", err)
        }
    }

    if len(error_list) != 0 {
        fmt.Println("Rollback", len(error_list), "Errors", error_list)
        err = o.Rollback()
    } else {
        fmt.Println(len(error_list))
        err = o.Commit()
    }
}

To know how to bootstrap the initial part of code, please see previous post.

Let us run through each line of code

    var error_list []interface{}

    err := o.Begin()
    if err != nil {
        error_list = append(error_list, err)
    }

We initiate a list variable that will hold all the error messages.

We BEGIN the transaction using o.Begin()

And we append the error to the list if there is any error executing the query.

    for i := 0; i < 5; i++ {
        if val, err := o.Raw("INSERT into tickets set locked_by = ? ,booked_by = ?", i, i*i).Exec(); err == nil
        {
            fmt.Println("inserted", i, (i * i), "ID", val)
        } else {
            error_list = append(error_list, err)
            fmt.Println("Error in insert", err)
        }
    }

We loop and do insert. Catch if any error occurs and append it to the list variable error_list.

    if len(error_list) != 0 {
        fmt.Println("Rollback", len(error_list), "Errors", error_list)
        err = o.Rollback()
    } else {
        fmt.Println(len(error_list))
        err = o.Commit()
    }

If there is no errors in the error_list, if the error_list length is 0

Then COMMIT

else ROLLBACK

Exercise,

  1. Try making the insert query worng by adding a typo in the column name, and see the results.
  2. Instead of looping, write 5 distinct insert queries, and make the 5th query fail, and see if the 4 other queries are rolled back or not.

Find the source code here