Gorm: 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: https://github.com/mattn/go-sqlite3/issues/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 https://github.com/go-gorm/gorm.io/pull/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:

• https://github.com/mattn/go-sqlite3/issues/274
• https://github.com/mattn/go-sqlite3/issues/569
• https://github.com/mattn/go-sqlite3/issues/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.