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

Advertisements

Handling a mysql Transaction BEGIN / COMMIT / ROLLBACK with beego and GO, using raw sql

There is a way Beego handles transaction. I will post a blog on it later. This round is to give an idea about how error and BEGIN/COMMIT work with beego and GO language.

This exercise requires you to have this table schema in your test database

CREATE TABLE `tickets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `locked_by` int(11) DEFAULT NULL,
  `booked_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Load up a row to have our tests run

INSERT INTO tickets values();

Lets get your code Boostraped.

Get it from here: https://gist.github.com/sumitasok/bfaa8a7194d3532878e9#file-bootstrap_beego_orm-go

Use this code to start the exercise.

Update the below line of code in bootstrap file with your mysql settings

orm.RegisterDataBase("default", "mysql", "mysql_username:mysql_password@/database_name?charset=utf8")

A Transaction in mysql start with BEGIN, finishes with COMMIT on complete success, or executes ROLLBACK on failure at any point.

BEGIN

QUERY X: UPDATE a value in a table.row

QUERY Y: tries UPDATE a value in a table and fails

COMMIT

ROLLBACK

When Query Y fails, the changes done by Query X should be reverted. That’s the whole point about a transaction rollback.

Every o.Raw(“Query”).Exec() returns two results, One is the result itself and the next is the err_status. If the second retur value is not nil, it means the query failed.

o here is a newORM instance. refer the bootstrap file. Line

    o := orm.NewOrm()
    o.Using("default")

So lets write a raw query that prints the error message when it fails.

Right below the above lines in bootstrap file, add

    if _, err1 := o.Raw("UPDATE tickets set booked_by = 12345 where id = 1").Exec(); err1 != nil {
        fmt.Println("Error 1", err1)
    } else {
        fmt.Println("Perfect!")
    }

Please remember to reset the values of all columns to 0 in row with id as 1. Every single time we run the code

This is a passing line of code. So the output will be “Perfect!” and the data change will be seen in mysql database row.

Lets change the value 12345 to a string, “12345_string”. booked_by is an integer column, and trying to assign a string vaue to it will through error. Lets do that.

Now the code will look like below,

    if _, err1 := o.Raw("UPDATE tickets set booked_by = '12345_string' where id = 1").Exec(); err1 != nil {
        fmt.Println("Error 1", err1)
    } else {
        fmt.Println("Perfect!")
    }

Run the file with go run command

Output will be: Error 1 Error 1265: Data truncated for column ‘booked_by’ at row 1

We just saw how the error catchiing works with o.Raw().Exec() command.

Now lets see how we can use this to get the transaction feature of mysql.

BEGIN is the raw sql query that starts a transaction Lets send it through the ORM.

Replace the above example with this code now, in the boot strap file.

    o.Raw("BEGIN").Exec()

    if _, err1 := o.Raw("UPDATE tickets set booked_by = 1234567 where id = 1").Exec(); err1 != nil {
        fmt.Println("Error 1", err1)
    } else {
        if _, err2 := o.Raw("UPDATE tickets set locked_by = '45440000_q' where id = 15").Exec(); err2 != nil {
            fmt.Println("Error 2", err2)
        } else {
            o.Raw("COMMIT").Exec()
        }
    }

Here,

  • after the BEGIN statement everything is a single transaction, and until the COMMIT command is passed, the transactions are not recorded in the database.
  • In the first if loop, the SQL query will execute without any error. So it will enter the second if loop.
  • In second if loop, however the integer column locked_by is trying to be set with a string value ‘12345_string’. This will fail.
  • So if the transaction was not rollbacked properly, the value in booked_by is supposed to be 1234567. But see it for yourself!
  • because the COMMIT was not called the transaction was not recorded in the database.
  • Only if all the Raw sql query executions succeed, then is when COMMIT is send.
  • So we get a transaction. (This is not the ORM specified way of doing it. I will blog on it later.)

Now try changing the string value assigned to locked_by with an integer value, and you will see both the values are assigned. because the transaction executed successfully.

The transaction failing and rollback executing code:https://github.com/sumitasok/go_packages/blob/master/mysql/orm_raw_multiple_query_return.go

Dont forget to change the mysql config.

And the King and his family lived ever after 🙂