package main import ( "database/sql" "io" "log" "os" "strconv" "sync" "syscall" "time" _ "github.com/mattn/go-sqlite3" "golang.org/x/sys/unix" ) const ( File = "demo.db" // DSN points to a local DB file without cache enabled. DSN = "file:" + File ) func setup() { _ = os.Remove(File) db, err := sql.Open("sqlite3", DSN) if err != nil { log.Fatalf("open: %v", err) } defer func() { _ = db.Close() log.Println("finishing setup") }() if _, err = db.Exec(` CREATE TABLE foo ( id SERIAL PRIMARY KEY, description VARCHAR(255) ); `); err != nil { log.Fatalf("creating schema: %v", err) } } func ping(wg *sync.WaitGroup, id int, start, linger time.Duration) { logger := log.New(os.Stderr, "("+strconv.Itoa(id)+") ", log.LstdFlags) defer func() { wg.Done() }() time.Sleep(start) f, err := os.Open(File) if err != nil { logger.Fatalf("file open: %v", err) } lock := unix.Flock_t{ Start: 0, Len: 0, Pid: int32(os.Getpid()), Type: syscall.F_WRLCK, Whence: io.SeekStart, } for { if err := unix.FcntlFlock(f.Fd(), unix.F_GETLK, &lock); err != nil { logger.Fatalf("file get lock info: %v", err) } // if err := unix.Flock(int(f.Fd()), unix.LOCK_SH); err != nil { // logger.Fatalf("file lock: %v", err) // } if int(lock.Type) == syscall.F_UNLCK { logger.Println("locked DB") break } logger.Println("attempted to locked DB, but it was locked") time.Sleep(100 * time.Millisecond) } defer func() { if err := unix.FcntlFlock(f.Fd(), unix.F_SETLK, &lock); err != nil { logger.Fatalf("file unlock: %v", err) } logger.Println("unlocked DB") }() db, err := sql.Open("sqlite3", DSN) if err != nil { logger.Fatalf("sql open: %v", err) } defer db.Close() logger.Println("open: OK") res, err := db.Exec(` INSERT INTO foo(id, description) VALUES (?, ?) `, id, strconv.Itoa(id)) if err != nil { logger.Fatalf("insert: %v", err) } else { ra, _ := res.RowsAffected() last, _ := res.LastInsertId() logger.Printf("inserted count %d / last %d", ra, last) } row := db.QueryRow(` SELECT COUNT(id) FROM foo `) count := 0 if err = row.Scan(&count); err != nil { logger.Fatalf("scanning: %v", err) } logger.Printf("select %d rows: OK", count) time.Sleep(linger) if err := db.Close(); err != nil { logger.Fatalf("close: %v", err) } logger.Println("close: OK") } func main() { setup() wg := sync.WaitGroup{} wg.Add(2) go ping(&wg, 1, 0, 2*time.Second) go ping(&wg, 2, time.Second, 0) wg.Wait() log.Println("all done") }