Skip to content

Instantly share code, notes, and snippets.

@kazz187
Created May 26, 2023 17:44
Show Gist options
  • Save kazz187/693f7ed408a131bda527e0ab2f59b6b0 to your computer and use it in GitHub Desktop.
Save kazz187/693f7ed408a131bda527e0ab2f59b6b0 to your computer and use it in GitHub Desktop.
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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment