Read-only transactions seem to pollute DB object #335
Description
I'm finding that if I do a read-only transaction, my next, non-RO transaction fails with the error sqlite3: attempt to write a readonly database.
Here's a reproducer:
package main
import (
"context"
"database/sql"
"fmt"
_ "github.com/ncruces/go-sqlite3/driver"
_ "github.com/ncruces/go-sqlite3/embed"
// _ "github.com/mattn/go-sqlite3"
"os"
"path/filepath"
)
func main() {
dir, _ := os.MkdirTemp("", "sqlite")
path := filepath.Join(dir, "test.db")
db, err := sql.Open("sqlite3", fmt.Sprintf("file:%s", path))
if err != nil {
panic(err)
}
defer os.RemoveAll(dir)
if _, err := db.Exec("CREATE TABLE foo (a TEXT, b INTEGER);"); err != nil {
panic(err)
}
// Write something
if _, err := db.Exec("INSERT INTO foo VALUES ('test', 1);"); err != nil {
panic(err)
}
// Now do a read-only transaction to pull it back
tx, err := db.BeginTx(context.Background(), &sql.TxOptions{ReadOnly: true})
if err != nil {
panic(err)
}
rows, err := tx.Query("SELECT * from foo;")
if err != nil {
panic(err)
}
for rows.Next() {
var a string
var b int
if err := rows.Scan(&a, &b); err != nil {
panic(err)
}
fmt.Println(a, b)
}
if err := rows.Err(); err != nil {
panic(err)
}
tx.Commit()
// Now attempt to write
tx, err = db.BeginTx(context.Background(), &sql.TxOptions{})
if err != nil {
panic(err)
}
if _, err := tx.Exec("INSERT INTO foo VALUES ('xyzzy', 2);"); err != nil {
panic(err)
}
tx.Commit()
}
If you change over to the mattn driver, it executes correctly.
I don't know if this is strictly a bug or what, but it feels weird that if I do a RO transaction, that connection goes back in the pool and "pollutes" it for future use.