Golang でトランザクションをネストさせるたった一つの冴えたやり方

FROM golang:1.13-stretch

RUN set -x; \
  apt-get update -y &&\
  apt-get install -y mysql-client

version: "3.7"
services:
  app:
    container_name: go-app
    volumes:
      - ./:/root/
    working_dir: /root/
    build: .
    tty: true
    networks:
      - go-sql-network

  mysql:
    image: mariadb:latest
    container_name: go-mysql
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: db
      MYSQL_USER: usr
      MYSQL_PASSWORD: pw
    volumes:
      - ./data/mysql/:/var/lib/
    expose:
      - "3306"
    networks:
      - go-sql-network
    ports:
      - "3306:3306"


networks:
  go-sql-network:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, _ := sql.Open("mysql", "usr:pw@tcp(mysql:3306)/db")
	tx, _ := db.Begin()
	defer tx.Rollback()
	tx.Exec("INSERT animals (name) VALUES ('alpaca'),('dog'),('cat') ;")

	rows, _ := tx.Query("SELECT id, name FROM animals")
	for rows.Next() {
		var id int
		var name string
		rows.Scan(&id, &name)
		fmt.Println(id, name)
	}
}

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, _ := sql.Open("mysql", "usr:pw@tcp(mysql:3306)/db")
	tx, _ := db.Begin()
	defer tx.Rollback()

	txNested, _ := db.Begin()
	txNested.Exec("INSERT animals (name) VALUES ('alpaca'),('dog'),('cat') ;")
	txNested.Commit()

	rows, _ := tx.Query("SELECT id, name FROM animals")
	for rows.Next() {
		var id int
		var name string
		rows.Scan(&id, &name)
		fmt.Println(id, name)
	}
}

package transaction

import (
	"database/sql"
	"strconv"
)

type NestableTx struct {
	*sql.Tx

	savePoint int
	next      *NestableTx
	resolved  bool
}

func (tx *NestableTx) Begin() (*NestableTx, error) {
	tx.next = &NestableTx{
		Tx:        tx.Tx,
		savePoint: tx.savePoint + 1,
	}

	_, err := tx.Exec("SAVEPOINT SP" + strconv.Itoa(tx.next.savePoint))

	if err != nil {
		return nil, err
	}

	return tx.next, nil
}

func (tx *NestableTx) Rollback() error {
	tx.resolved = true

	if tx.savePoint > 0 {
		_, err := tx.Exec("ROLLBACK TO SAVEPOINT SP" + strconv.Itoa(tx.savePoint))
		return err
	}

	return tx.Tx.Rollback()
}

func (tx *NestableTx) Commit() error {
	if tx.next != nil && !tx.next.resolved {
		err := tx.next.Commit()
		if err != nil {
			return err
		}
	}

	tx.resolved = true

	if tx.savePoint > 0 {
		_, err := tx.Exec("RELEASE SAVEPOINT SP" + strconv.Itoa(tx.savePoint))

		return err
	}

	return tx.Tx.Commit()
}

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"

	"nested-transaction/transaction"
)

func main() {
	db, _ := sql.Open("mysql", "usr:pw@tcp(mysql:3306)/db")
	tx, _ := db.Begin()
	ntx := &transaction.NestableTx{Tx: tx}
	defer ntx.Rollback()

	ntx.Exec("INSERT animals (name) VALUES (?);", "alpaca")

	ntx2, _ := ntx.Begin()
	ntx2.Exec("INSERT animals (name) VALUES (?);", "pheasant")

	ntx3, _ := ntx2.Begin()
	ntx3.Exec("INSERT animals (name) VALUES (?);", "reindeer")

	ntx4, _ := ntx3.Begin()
	ntx4.Exec("INSERT animals (name) VALUES (?);", "mole")
	ntx4.Commit()
	ntx2.Rollback()

	ntx5, _ := ntx4.Begin()
	ntx5.Exec("INSERT animals (name) VALUES (?);", "weasel")

	ntx6, _ := ntx5.Begin()
	ntx6.Exec("INSERT animals (name) VALUES (?);", "ostrich")
	ntx5.Commit()
	ntx6.Exec("INSERT animals (name) VALUES (?);", "hare")
	ntx6.Rollback()
	show(ntx)
}

func show(ntx *transaction.NestableTx) {
	rows, _ := ntx.Query("SELECT id, name FROM animals")
	for rows.Next() {
		var id int
		var name string
		rows.Scan(&id, &name)
		fmt.Println(id, name)
	}
}

package target

import (
	"nested-transaction/transaction"

	_ "github.com/go-sql-driver/mysql"
)

func multipleInsert(ntx *transaction.NestableTx, animals []string) {
	ntx, _ = ntx.Begin()
	for _, a := range animals {
		ntx.Exec("INSERT animals (name) VALUES (?);", a)
	}
	ntx.Commit()
}

package target

import (
	"database/sql"
	"testing"

	"nested-transaction/transaction"

	_ "github.com/go-sql-driver/mysql"
	"github.com/google/go-cmp/cmp"
)

func TestMultipleInsert(t *testing.T) {
	db, _ := sql.Open("mysql", "usr:pw@tcp(mysql:3306)/db")
	defer db.Close()
	tx, _ := db.Begin()
	ntx := &transaction.NestableTx{Tx: *tx}
	defer ntx.Rollback()

	expected := []string{"alpaca", "dog", "cat"}
	multipleInsert(ntx, expected)
	actual := getAnimals(ntx)
	if r := cmp.Diff(expected, actual); r != "" {
		t.Errorf("expected: %#v, actual: %#v", expected, actual)
	}
}

func getAnimals(ntx *transaction.NestableTx) []string {
	animals := []string{}
	rows, _ := ntx.Query("SELECT name FROM animals")
	for rows.Next() {
		var name string
		rows.Scan(&name)
		animals = append(animals, name)
	}
	return animals
}

package transaction

import (
	"strconv"

	"github.com/go-gorp/gorp"
)

type NestableGorpTx struct {
	*gorp.Transaction

	savePoint int
	next      *NestableGorpTx
	resolved  bool
}

func (tx *NestableGorpTx) Begin() (*NestableGorpTx, error) {
	tx.next = &NestableGorpTx{
		Transaction: tx.Transaction,
		savePoint:   tx.savePoint + 1,
	}
	if err := tx.Savepoint("SP" + strconv.Itoa(tx.next.savePoint)); err != nil {
		return nil, err
	}
	return tx.next, nil
}

func (tx *NestableGorpTx) Rollback() error {
	tx.resolved = true
	if tx.savePoint > 0 {
		return tx.RollbackToSavepoint("SP" + strconv.Itoa(tx.savePoint))
	}
	return tx.Transaction.Rollback()
}

func (tx *NestableGorpTx) Commit() error {
	if tx.next != nil && !tx.next.resolved {
		err := tx.next.Commit()
		if err != nil {
			return err
		}
	}
	tx.resolved = true

	if tx.savePoint > 0 {
		return tx.ReleaseSavepoint("SP" + strconv.Itoa(tx.savePoint))
	}
	return tx.Transaction.Commit()
}

package main

import (
	"database/sql"
	"fmt"

	"github.com/go-gorp/gorp"
	_ "github.com/go-sql-driver/mysql"

	"nested-transaction/transaction"
)

func main() {
	db, _ := sql.Open("mysql", "usr:pw@tcp(mysql:3306)/db")
	dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{Engine: "InnoDB", Encoding: "utf8mb4"}}
	defer dbmap.Db.Close()
	tx, _ := dbmap.Begin()
	ntx := &transaction.NestableGorpTx{Transaction: tx}
	defer ntx.Rollback()

	ntx.Exec("INSERT animals (name) VALUES (?);", "alpaca")

	ntx2, _ := ntx.Begin()
	ntx2.Exec("INSERT animals (name) VALUES (?);", "pheasant")

	ntx3, _ := ntx2.Begin()
	ntx3.Exec("INSERT animals (name) VALUES (?);", "reindeer")

	ntx4, _ := ntx3.Begin()
	ntx4.Exec("INSERT animals (name) VALUES (?);", "mole")
	ntx4.Commit()
	ntx2.Rollback()

	ntx5, _ := ntx4.Begin()
	ntx5.Exec("INSERT animals (name) VALUES (?);", "weasel")

	ntx6, _ := ntx5.Begin()
	ntx6.Exec("INSERT animals (name) VALUES (?);", "ostrich")
	ntx5.Commit()
	ntx6.Exec("INSERT animals (name) VALUES (?);", "hare")
	ntx6.Rollback()
	show(ntx)
}

func show(exec gorp.SqlExecutor) {
	animals := []struct {
		ID   int
		Name string
	}{}

	exec.Select(&animals, "SELECT id, name FROM animals") // 今回はエラーは捨てる
	for _, row := range animals {
		fmt.Println(row.ID, row.Name)
	}
}