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()
}
Comment From: xmlking
have same issue. "file:foobar?mode=memory&cache=shared" was working fine before with Sqlite 1.x
Comment From: DerThorsten
@xmlking do you think the issue is related to gorm or sqlite itself?
Comment From: 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
Comment From: DerThorsten
So whats the protocol here? Shall I make A PR with the failing test?
Comment From: cnf
Any update on this?
Comment From: 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.
Comment From: cnf
Thanks for the reply, I'll test as soon as I can!
Comment From: aQuaYi
Thanks for the reply, I'll test as soon as I can!
@cnf any result?
Comment From: mitar
Using file::memory:?cache=shared solves the problem for me. This was really surprising.
Comment From: mitar
I made https://github.com/go-gorm/gorm.io/pull/230 to update documentation here.
Comment From: cnf
My apologies, RL got in the way, have not had time to test this yet.
Comment From: 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.
Comment From: 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)