Sqlite3 not threadsave when db is in memory

[DerThorsten]

Go Version: 1.13
Database: sqlite3 :memory:

Accessing a sqlite memory database concurrently gives strange results.
(no such table panic). This in only triggered when the db is accessed from multiple go routines simultaneous.

package main

import (
    "log"
    "sync"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
)


type Input struct {
    gorm.Model
    TaskInput string `gorm::type:json"`
}
func (b *Input) TableName() string {
    return "inputs"
}


func main() {
    log.Print("test")

    db, err := gorm.Open("sqlite3", ":memory:")
    if err!= nil{
        panic(err)
    }
    db.AutoMigrate(&Input{})

    wg := sync.WaitGroup{}


    // THIS WORKS FINE
    for i:=0; i<10; i++{
        if err := db.Create(&Input{TaskInput:`{"foo":"bar"}`}).Error; err != nil{
            panic(err)
        }
    }
    // THIS CRASHES
    for i:=0; i<20; i++{
        wg.Add(1)
        go func()  {
            // here it fails 
            // panic: no such table: inputs
            err := db.Create(&Input{TaskInput:`{"foo":"bar"}`}).Error
            if err!=nil{
                panic(err)
            }
            wg.Done()
        }()
    }
    wg.Wait()
}

Using the lock does not panic

package main

import (
    "log"
    "sync"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
)


type Input struct {
    gorm.Model
    TaskInput string `gorm::type:json"`
}
func (b *Input) TableName() string {
    return "inputs"
}


func main() {
    log.Print("test")

    db, err := gorm.Open("sqlite3", ":memory:")
    if err!= nil{
        panic(err)
    }
    db.AutoMigrate(&Input{})

    wg := sync.WaitGroup{}


    // THIS WORKS FINE
    for i:=0; i<10; i++{
        if err := db.Create(&Input{TaskInput:`{"foo":"bar"}`}).Error; err != nil{
            panic(err)
        }
    }


    mux := sync.Mutex{}

    // THIS DOES NOT CRASH
    for i:=0; i<20; i++{
        wg.Add(1)
        go func()  {
            mux.Lock()
            err := db.Create(&Input{TaskInput:`{"foo":"bar"}`}).Error
            mux.Unlock()
            if err!=nil{
                panic(err)
            }
            wg.Done()
        }()
    }
    wg.Wait()
}

Using a non :memory: sqlite database does also NOT crash

package main

import (
    "log"
    "sync"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
)


type Input struct {
    gorm.Model
    TaskInput string `gorm::type:json"`
}
func (b *Input) TableName() string {
    return "inputs"
}


func main() {
    log.Print("test")

    db, err := gorm.Open("sqlite3", "someFile.db")
    if err!= nil{
        panic(err)
    }
    db.AutoMigrate(&Input{})

    wg := sync.WaitGroup{}


    // THIS WORKS FINE
    for i:=0; i<10; i++{
        if err := db.Create(&Input{TaskInput:`{"foo":"bar"}`}).Error; err != nil{
            panic(err)
        }
    }
    // THIS DOES NOT CRASHES
    for i:=0; i<20; i++{
        wg.Add(1)
        go func()  {

            err := db.Create(&Input{TaskInput:`{"foo":"bar"}`}).Error

            if err!=nil{
                panic(err)
            }
            wg.Done()
        }()
    }
    wg.Wait()
}

[xmlking]

have same issue. "file:foobar?mode=memory&cache=shared" was working fine before with Sqlite 1.x

[DerThorsten]

@xmlking do you think the issue is related to gorm or sqlite itself?

[camathieu]

Indeed

import (
	"fmt"
	"sync"
	"testing"

	"github.com/jinzhu/gorm"

	_ "github.com/jinzhu/gorm/dialects/sqlite"
	"github.com/stretchr/testify/require"
)

func TestGormConcurrent(t *testing.T) {

	type Object struct {
		gorm.Model
		Foo string
	}

	db, err := gorm.Open("sqlite3", ":memory:")
	require.NoError(t, err, "DB open error")

	err = db.AutoMigrate(&Object{}).Error
	require.NoError(t, err, "schema update error")

	count := 30
	var wg sync.WaitGroup
	errors := make(chan error, count)
	for i := 0 ; i < count ; i++ {
		wg.Add(1)
		go func(i int) {
			defer wg.Done()

			errors <- db.Create(&Object{Foo:fmt.Sprintf("%d", i)}).Error
		}(i)
	}

	wg.Wait()
	close(errors)
	for err := range errors {
		require.NoError(t, err, "unexpected error")
	}
}

=== RUN   TestGormConcurrent

(/home/cam/git/go/src/github.com/root-gg/plik/server/metadata/metadata_test.go:62) 
[2020-02-06 23:00:18]  no such table: objects 

(/home/cam/git/go/src/github.com/root-gg/plik/server/metadata/metadata_test.go:62) 
[2020-02-06 23:00:18]  no such table: objects 

(/home/cam/git/go/src/github.com/root-gg/plik/server/metadata/metadata_test.go:62) 
[2020-02-06 23:00:18]  no such table: objects 

Work fine with a file

[DerThorsten]

So whats the protocol here? Shall I make A PR with the failing test?

[cnf]

Any update on this?

[orishoshan]

See this issue in go-sqlite3: mattn/go-sqlite3#204

This is likely caused by database/sql connection pooling, whereas opening a DB with :memory: gives you a different DB for each connection, see the official sqlite3 docs: https://www.sqlite.org/inmemorydb.html

If you want multiple connections to share the same DB, open the DB with file::memory:?cache=shared as the filename.

[cnf]

Thanks for the reply, I'll test as soon as I can!

[aQuaYi]

Thanks for the reply, I'll test as soon as I can!

@cnf any result?

[mitar]

Using file::memory:?cache=shared solves the problem for me. This was really surprising.

[mitar]

I made go-gorm/gorm.io#230 to update documentation here.

[cnf]

My apologies, RL got in the way, have not had time to test this yet.

[mitar]

Sadly, this didn't fully address my issues. After starting using shared cache, database locking and database table locking errors started to randomly popping up, breaking our tests randomly. I have to also call db.DB().SetMaxOpenConns(1) to disable connection pooling. See the following related issues:

• database is locked mattn/go-sqlite3#274
• database is locked mattn/go-sqlite3#569
• database is locked regression mattn/go-sqlite3#607

I have also read that those lock issues might be happening if you are not closing rows using defer rows.Close().

Moreover, I ended up using file::memory:?cache=shared&_busy_timeout=5000, which enables lock retry for 5 seconds in most cases one gets an lock error.

[zls3201]

db, err := gorm.Open(sqlite.Open(":memory:"), &gorm.Config{})

assert.Nil(err, "open db error")

// SetMaxOpenConns 1
sqlDb, err := db.DB()
assert.Nil(err, "open sqlDb error")
sqlDb.SetMaxOpenConns(1)