Execution become very slow when writing concurrently.

[hxhxhx88]

Hi. Please check the following code to reproduce the behaviour, which does simply the following things:

• Initialise a local database file.
• Create a connection pool of 10 connections.
• Use 5 goroutines to concurrently insert into the database 1 record for each.

package main

import (
	"context"
	"fmt"
	"os"
	"path/filepath"
	"sync"
	"time"

	"github.com/pkg/errors"
	"zombiezen.com/go/sqlite"
	"zombiezen.com/go/sqlite/sqlitex"
)

const dbPath = "local/db.sqlite3"

func main() {
	mustOk(initializeDatabaseIfNecessary(dbPath))

	pool, err := sqlitex.Open(dbPath, 0, 10)
	mustOk(err)
	defer pool.Close()

	nj := 5
	var wg sync.WaitGroup
	for k := 0; k < nj; k++ {
		wg.Add(1)
		go func(idx int) {
			defer wg.Done()

			start := time.Now()
			defer func() {
				fmt.Printf("Goroutine #%d costs %v\n", idx, time.Since(start))
			}()

			ctx := context.Background()
			conn := pool.Get(ctx)
			defer pool.Put(conn)

			_, err = create(ctx, conn)
			mustOk(err)
		}(k)
	}
	wg.Wait()
}

func create(ctx context.Context, conn *sqlite.Conn) (int64, error) {
	if err := sqlitex.ExecuteTransient(conn, `INSERT INTO projects (name) VALUES (:name)`, &sqlitex.ExecOptions{
		Named: map[string]interface{}{
			":name": "hello",
		},
	}); err != nil {
		return 0, errors.WithStack(err)
	}

	id := conn.LastInsertRowID()
	return id, nil
}

func initializeDatabaseIfNecessary(path string) error {
	// initialzie a database if file at path does not exist
	if _, err := os.Stat(path); err == nil {
		// db file exists
		return nil
	} else if !errors.Is(err, os.ErrNotExist) {
		// unexpected error
		return errors.WithStack(err)
	}

	// create intermediate folders
	dir := filepath.Dir(path)
	if err := os.MkdirAll(dir, os.ModePerm); err != nil {
		return errors.WithStack(err)
	}

	// initialize the new db file
	conn, err := sqlite.OpenConn(path, 0)
	if err != nil {
		return errors.WithStack(err)
	}
	defer conn.Close()

	if err := sqlitex.ExecScript(conn, `
		CREATE TABLE IF NOT EXISTS projects (
			id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			name TEXT NOT NULL
		)
	`); err != nil {
		return errors.WithStack(err)
	}

	return nil
}

func mustOk(err error) {
	if err == nil {
		return
	}
	fmt.Printf("%+v", err)
	os.Exit(1)
}

However, the printed result reads

Goroutine #1 costs 787.125µs
Goroutine #2 costs 1.002749959s
Goroutine #0 costs 3.004037958s
Goroutine #4 costs 8.005700375s
Goroutine #3 costs 18.007787875s

which shows that the insertion cost increasing amount of time. I believe it is due to some kind of lock, since if I use a context with timeout an error will be raised like

sqlite: step: database is locked: database is locked

Therefore, my question is, how to avoid such latency in concurrent writing? As it looks like a quite common scenario of a web server, I guess there must be a way to do so.

Look forward to any help!

[zombiezen]

What you're seeing is more an artifact of your microbenchmark, not entirely representative of a real-world workload. If you're looking for very write-contended workloads, you may want to use PostgreSQL or another RDBMS. IME though, for small-to-mid-scale workloads, SQLite works just fine with vertical scaling. See Appropriate Uses for SQLite for some rough guidelines.

Longer Explanation: The best that SQLite can do is with Write-Ahead Logging, which is enabled by default in this package. This permits reading and writing to happen concurrently, but SQLite intentionally only allows one database writer at a time. When a writer encounters a locked database, by default it will back off to avoid burning CPU, which based on the timings seems like what you're probably observing, not the actual time-to-write. If waiting for the lock isn't desirable, you can call SetBusyTimeout to return a ResultLocked error in these situations and fail quickly.

Also, bear in mind that at the end of a transaction (including implicit ones) will wait to flush the write to disk, so your benchmark is not representative of "an insert", but rather, "a transaction".

Hope that's helpful! This can definitely be a little confusing: SQLite does some pretty sophisticated stuff behind a very simple interface.

[hxhxhx88]

Thanks for your explanation! Looks like my benchmark is indeed an unfair artifact that shouldn't be expected in real world usages:)