isolation_level.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	"log"
)

func main() {
	db, err := connection()
	if err != nil {
		log.Fatalln(err)
	}
	defer db.Close()

	//isolationLevel := sql.LevelSerializable   // 完全排他
	isolationLevel := sql.LevelRepeatableRead // 他の tx がコミットしても読めない
	//isolationLevel := sql.LevelReadCommitted // 他の tx がコミットしたものも読める
	//isolationLevel := sql.LevelReadUncommitted // まだ他の tx がコミットしてないものも読める

	// p0 はどの IsolationLevel でも待ち状態になるのでコメントアウト
	//if err := truncate(db); err != nil {
	//	log.Fatalln(err)
	//}
	//
	//p0Result, err := p0DirtyWrite(db, isolationLevel)
	//if err != nil {
	//	log.Fatalln("p0:", err)
	//}
	//if p0Result {
	//	fmt.Println("p0: dirty write")
	//}

	if err := truncate(db); err != nil {
		log.Fatalln(err)
	}

	p1Result, err := p1DirtyRead(db, isolationLevel)
	if err != nil {
		log.Fatalln("p1:", err)
	}
	if p1Result {
		fmt.Println("p1: dirty read")
	}
	fmt.Println()

	if err := truncate(db); err != nil {
		log.Fatalln(err)
	}

	p2Result, err := p2FuzzyRead(db, isolationLevel)
	if err != nil {
		log.Fatalln("p2:", err)
	}
	if p2Result {
		fmt.Println("p2: fuzzy read")
	}
	fmt.Println()

	if err := truncate(db); err != nil {
		log.Fatalln(err)
	}

	p3Result, err := p3PhantomRead(db, isolationLevel)
	if err != nil {
		log.Fatalln("p3:", err)
	}
	if p3Result {
		fmt.Println("p3: phantom read")
	}
	fmt.Println()
}

func connection() (*sqlx.DB, error) {
	db, err := sqlx.Open("mysql", "root:root@/release_id_dev")
	if err != nil {
		return nil, fmt.Errorf("failed to open db: %w", err)
	}
	return db, nil
}

func beginTwoTxWithIsolationLevel(db *sqlx.DB, isolationLevel sql.IsolationLevel) (*sqlx.Tx, *sqlx.Tx, error) {
	ctx := context.Background()

	txOptions := &sql.TxOptions{
		Isolation: isolationLevel,
	}
	tx1, err := db.BeginTxx(ctx, txOptions)
	if err != nil {
		return nil, nil, fmt.Errorf("failed to begin tx1: %w", err)
	}

	tx2, err := db.BeginTxx(ctx, txOptions)
	if err != nil {
		return nil, nil, fmt.Errorf("failed to begin tx2: %w", err)
	}

	return tx1, tx2, nil
}

func truncate(db *sqlx.DB) error {
	_, err := db.Exec("TRUNCATE TABLE examples")
	if err != nil {
		return fmt.Errorf("failed to truncate table: %w", err)
	}
	return nil
}

type Example struct {
	ID  string `db:"id"`
	Val string `db:"val"`
}

func p0DirtyWrite(db *sqlx.DB, isolationLevel sql.IsolationLevel) (bool, error) {
	_, err := db.Exec("INSERT INTO examples (id, val) VALUES ('1', 'a')")
	if err != nil {
		return false, fmt.Errorf("failed to insert: %w", err)
	}

	tx1, tx2, err := beginTwoTxWithIsolationLevel(db, isolationLevel)
	if err != nil {
		return false, fmt.Errorf("failed to begin tx: %w", err)
	}

	ctx := context.Background()
	if _, err := tx1.ExecContext(ctx, "UPDATE examples SET val = 'b' WHERE id = '1'"); err != nil {
		return false, fmt.Errorf("failed to update tx1: %w", err)
	}

	// ここで待ちが発生してロックがタイムアウトする
	if _, err := tx2.ExecContext(ctx, "UPDATE examples SET val = 'c' WHERE id = '1'"); err != nil {
		return false, fmt.Errorf("failed to update tx2: %w", err)
	}

	if err := tx1.Commit(); err != nil {
		return false, fmt.Errorf("failed to commit tx1: %w", err)
	}

	if err := tx2.Rollback(); err != nil {
		return false, fmt.Errorf("failed to rollback tx2: %w", err)
	}

	if _, err := selectByID(ctx, db, "1"); err != nil {
		return false, fmt.Errorf("failed to select: %w", err)
	}
	return false, nil
}

func p1DirtyRead(db *sqlx.DB, isolationLevel sql.IsolationLevel) (bool, error) {
	fmt.Println("# p1DirtyRead")

	_, err := db.Exec("INSERT INTO examples (id, val) VALUES ('1', 'a')")
	if err != nil {
		return false, fmt.Errorf("failed to insert: %w", err)
	}

	var (
		v1 string
		v2 string
	)

	ctx := context.Background()
	tx1, tx2, err := beginTwoTxWithIsolationLevel(db, isolationLevel)
	if err != nil {
		return false, fmt.Errorf("failed to begin tx: %w", err)
	}

	fmt.Println("## tx2: select")
	if ex, err := selectByID(ctx, tx2, "1"); err != nil {
		return false, fmt.Errorf("failed to select: %w", err)
	} else {
		if len(ex) > 0 {
			v1 = ex[0].Val
		}
	}

	fmt.Println("## tx1: update to b")
	if _, err := tx1.Exec("UPDATE examples SET val = 'b' WHERE id = '1'"); err != nil {
		return false, fmt.Errorf("failed to update tx1: %w", err)
	}

	fmt.Println("## tx2: select")
	if ex, err := selectByID(ctx, tx2, "1"); err != nil {
		return false, fmt.Errorf("failed to select: %w", err)
	} else {
		if len(ex) > 0 {
			v2 = ex[0].Val
		}
	}

	fmt.Println("## tx1: commit")
	if err := tx1.Commit(); err != nil {
		return false, fmt.Errorf("failed to commit tx1: %w", err)
	}

	fmt.Println("## tx2: rollback")
	if err := tx2.Rollback(); err != nil {
		return false, fmt.Errorf("failed to rollback tx2: %w", err)
	}

	return v1 != v2, nil
}

func p2FuzzyRead(db *sqlx.DB, isolationLevel sql.IsolationLevel) (bool, error) {
	fmt.Println("# p2FuzzyRead")

	_, err := db.Exec("INSERT INTO examples (id, val) VALUES ('1', 'a')")
	if err != nil {
		return false, fmt.Errorf("failed to insert: %w", err)
	}

	var (
		v1 string
		v2 string
	)

	ctx := context.Background()
	tx1, tx2, err := beginTwoTxWithIsolationLevel(db, isolationLevel)
	if err != nil {
		return false, fmt.Errorf("failed to begin tx: %w", err)
	}

	fmt.Println("## tx2: select")
	if ex, err := selectByID(ctx, tx2, "1"); err != nil {
		return false, fmt.Errorf("failed to select: %w", err)
	} else {
		if len(ex) > 0 {
			v1 = ex[0].Val
		}
	}

	fmt.Println("## tx1: update to b")
	if _, err := tx1.Exec("UPDATE examples SET val = 'b' WHERE id = '1'"); err != nil {
		return false, fmt.Errorf("failed to update tx1: %w", err)
	}

	fmt.Println("## tx1: commit")
	if err := tx1.Commit(); err != nil {
		return false, fmt.Errorf("failed to commit tx1: %w", err)
	}

	fmt.Println("## tx2: select")
	if ex, err := selectByID(ctx, tx2, "1"); err != nil {
		return false, fmt.Errorf("failed to select: %w", err)
	} else {
		if len(ex) > 0 {
			v2 = ex[0].Val
		}
	}

	fmt.Println("## tx2: rollback")
	if err := tx2.Rollback(); err != nil {
		return false, fmt.Errorf("failed to rollback tx2: %w", err)
	}

	return v1 != v2, nil
}

func p3PhantomRead(db *sqlx.DB, isolationLevel sql.IsolationLevel) (bool, error) {
	fmt.Println("# p3PhantomRead")

	_, err := db.Exec("INSERT INTO examples (id, val) VALUES ('1', 'a')")
	if err != nil {
		return false, fmt.Errorf("failed to insert: %w", err)
	}

	var (
		v1 int
		v2 int
	)

	ctx := context.Background()
	tx1, tx2, err := beginTwoTxWithIsolationLevel(db, isolationLevel)
	if err != nil {
		return false, fmt.Errorf("failed to begin tx: %w", err)
	}

	fmt.Println("## tx2: select")
	if ex, err := selectByVal(ctx, tx2, "a"); err != nil {
		return false, fmt.Errorf("failed to select: %w", err)
	} else {
		v1 = len(ex)
	}

	fmt.Println("## tx1: insert")
	if _, err := tx1.Exec("INSERT INTO examples (id, val) VALUES ('2', 'a')"); err != nil {
		return false, fmt.Errorf("failed to insert tx1: %w", err)
	}

	fmt.Println("## tx1: commit")
	if err := tx1.Commit(); err != nil {
		return false, fmt.Errorf("failed to commit tx1: %w", err)
	}

	fmt.Println("## tx2: select")
	if ex, err := selectByVal(ctx, tx2, "a"); err != nil {
		return false, fmt.Errorf("failed to select: %w", err)
	} else {
		v2 = len(ex)
	}

	fmt.Println("## tx2: rollback")
	if err := tx2.Rollback(); err != nil {
		return false, fmt.Errorf("failed to rollback tx2: %w", err)
	}

	return v1 != v2, nil
}

func selectByID(ctx context.Context, db sqlx.ExtContext, id string) ([]*Example, error) {
	var result []*Example
	if err := sqlx.SelectContext(ctx, db, &result, "SELECT * FROM examples WHERE id = ?", id); err != nil {
		return nil, fmt.Errorf("failed to select id %s: %w", id, err)
	}
	for _, example := range result {
		fmt.Printf("id: %s, val: %s\n", example.ID, example.Val)
	}
	return result, nil
}

func selectByVal(ctx context.Context, db sqlx.ExtContext, val string) ([]*Example, error) {
	var result []*Example
	if err := sqlx.SelectContext(ctx, db, &result, "SELECT * FROM examples WHERE val = ?", val); err != nil {
		return nil, fmt.Errorf("failed to select val %s: %w", val, err)
	}
	for _, example := range result {
		fmt.Printf("id: %s, val: %s\n", example.ID, example.Val)
	}
	return result, nil
}