Transactions don’t socialize

A Database Transaction is an atomic unit of work, sometimes made up of multiple operations.

When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled-back.

Isolation is the ‘I’ in ACID (a set of properties of a database transactions intend to guarantee data validity) It ensures that concurrent execution of transactions will obtain the same state that would have been obtained if the transactions were executed sequentially.

When executing transactions, we can choose differnt levels of isolation, which sets the tradeoff between performance and reliability.

To set an isolation level for a transaction we pass sql.TxOptions with the desired isolation level:

...
tx, err := db.BeginTx(context.Background(), &sql.TxOptions{Isolation: sql.LevelReadUncommitted})
...

In the following series of blogs I would like to introduce the different isolation level together with their tradeoffs.

Lets use the following users table for the next examples:

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `balance` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
)

INSERT INTO users(name, balance) values ("Bert", 100);
INSERT INTO users(name, balance) values ("Ernie", 100);

Reading Gets Dirty

When using the least isolated level - READ_UNCOMITTED - one transacation can see another transaction’s uncommited changes. Such phenomena has a name. It is called a Dirty Read.

func main() {
	db, err := sql.Open("mysql", "user:pass@tcp(127.0.0.1:3306)/my_db")
	if err != nil {
		log.Fatalln(err)
	}
	defer db.Close()

	ctx1 := context.Background()
	tx1, err := db.BeginTx(ctx1, nil)
	if err != nil {
		log.Fatalln(err)
	}

	_, err = tx1.ExecContext(ctx1, "UPDATE users SET amount = 50 WHERE name = 'Bert'")
	if err != nil {
		log.Fatalln(err)
		tx1.Rollback()
		return
	}

	go func() {
		ctx2 := context.Background()
		tx2, err := db.BeginTx(ctx2, &sql.TxOptions{Isolation: sql.LevelReadUncommitted})
		if err != nil {
			log.Fatalln(err)
			tx2.Rollback()
			return
		}
		r, err := tx2.Query("SELECT amount FROM users WHERE id = 1")
		if err != nil {
			log.Fatalln(err)
			tx2.Rollback()
			return
		}
		defer r.Close()
		var amount int
		for r.Next() {
			r.Scan(&amount)
		}
		fmt.Printf("TX2: Bert has a balance of %d€\n", amount)
	}()

	time.Sleep(1 * time.Second)
	tx1.Rollback()
}

When running the code, we see that the second transaction, tx2, being isolated with a UNCOMMITED_READ level, sees the uncommited value of Bert’s balance. This value is in consistent as it wasn’t commited and can be rolled back.