sqlite mvcc

main.go

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/mattn/go-sqlite3"
	"log"
	"context"
	"time"
	"os"
)

func main() {
	os.Remove("./foo.db")
	db, err := sql.Open("sqlite3", "./foo.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	sqlStmt := `
	create table foo (i integer);
	delete from foo;
	`
	_, err = db.Exec(sqlStmt)
	if err != nil {
		log.Printf("%q: %s\n", err, sqlStmt)
		return
	}

	_, err = db.Exec(`PRAGMA _journal_mode=wal`)
	if err != nil {
		log.Fatal(err)
	}

	ctx := context.Background()

	go func() {
		conn, err := db.Conn(ctx)
		if err != nil {
			log.Fatal(err)
		}
		conn.ExecContext(ctx, `PRAGMA _busy_timeout=100000`)
		tx, err := conn.BeginTx(ctx, nil)
		if err != nil {
			log.Printf("1 begin transaction %v\n", err)
		}
		log.Println("1 after tx begin")
		time.Sleep(1 * time.Second)
		rows, err := tx.Query(`select * from foo`)
		var s string
		for rows.Next() {
			if err := rows.Scan(&s); err != nil {
				log.Fatal(err)
			}
			fmt.Println(s)
		}
		tx.Commit()
		log.Println("exit 1")
		time.Sleep(1 * time.Second)
	}()
	conn, err := db.Conn(ctx)
	if err != nil {
		log.Fatal(err)
	}
	conn.ExecContext(ctx, `PRAGMA _busy_timeout=100000`)
	tx, err := conn.BeginTx(ctx, nil)
	if err != nil {
		log.Printf("2 begin transaction last!!! %v\n", err)
	}
	log.Println("2 after tx begin")
	time.Sleep(1 * time.Second)
	_, err = tx.Exec(`insert into foo values (1)`)
	rows, err := tx.Query(`select * from foo`)

	var s string
	for rows.Next() {
		if err := rows.Scan(&s); err != nil {
			log.Fatal(err)
		}
		fmt.Println(s)
	}
	tx.Commit()
	log.Println("exit 2")
	time.Sleep(1 * time.Second)


}