Shmulik Klein

Software Engineer @ JetBrains | Munich, Germany

Phantom Menace

In this second post of the series of “Transaction Isolation”, I would like to discuss about the committed read isolation level and its risks.


...

	ctx1 := context.Background()
	tx1, err := db.BeginTx(ctx1, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
	if err != nil {
		log.Fatalln(err)
	}
	printUsers(tx1)

	go func() {
		ctx2 := context.Background()
		tx2, err := db.BeginTx(ctx2, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
		if err != nil {
			log.Fatalln(err)
			tx2.Rollback()
			return
		}
		_, err = tx2.ExecContext(ctx2, "INSERT INTO users(name) VALUES('Elmo')")
		if err != nil {
			log.Fatalln(err)
			tx1.Rollback()
			return
		}
		tx2.Commit()
	}()

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

func printUsers(tx *sql.Tx) {
	r, err := tx.Query("SELECT name FROM users")
	if err != nil {
		log.Fatalln(err)
		tx.Rollback()
		return
	}
	defer r.Close()
	var name string
	fmt.Print("tx1 sees the following users: ")
	for r.Next() {
		r.Scan(&name)
		fmt.Printf(" %s ", name)
	}
	fmt.Print("\n")
}

When running the code, we see that the second transaction, tx2, being isolated with a COMMITED_READ level, sees the inconsistent values for the users. In the first SELECT query users, it sees Bert and Ernie, but on the second query, it sees Bert, Ernie and Elmo.