Sqlite3 error: database is locked in golang

Question

I'm using sqlite3 database in golang and I got the error: "database is locked."

I know there can't be multiple threads using same database file.

Although I get only one connection in my program I close all query results, but it always creates 2 or 3 handles of the database file.

I could check this out by using the Opendfileview program.

The following code creates two database file handles.

func main() {
    database, tx, err := getDatabaseHandle()
    if err != nil {
        log.Fatal(err)
    }
    defer database.Close()
    dosomething(database, tx)
}
func dosomething(database *sql.DB, tx *sql.Tx) error {
    rows, err := database.Query("select * from sometable where name=?","some")
    if err != nil {
        return err
    }
    if rows.Next() {
        ...
    }
    rows.Close()
    //some insert queries
    tx.Commit()
}
func getDatabaseHandle() (*sql.DB, *sql.Tx, error) {
    database, err := sql.Open("sqlite3", dbPath)
    if err != nil {
        fmt.Println("Failed to create the handle")
        return nil, nil, err
    }
    if err2 := database.Ping(); err2 != nil {
        fmt.Println("Failed to keep connection alive")
        return nil, nil, err
    }
    tx, err := database.Begin()
    if err != nil {
        return nil, nil, err
    }
    return database, tx, nil
}

No database file handle before my program runs, but 2 handles after my program runs. So I think just my program itself lock the database file. — superuser123
– superuser123, Commented Sep 9, 2015 at 13:24
You begin a transaction in getDatabaseHandle but I can't see tx.Commit or tx.Rollback anywhere. Could this be the issue? — Ainar-G
– Ainar-G, Commented Sep 9, 2015 at 14:17

Pablo Fernandez · Accepted Answer · 2015-09-09 15:05:38Z

30

Try defering the rows.Close():

if err != nil {
    return err
}
defer rows.Close()
if rows.Next() {
    ...
}

answered Sep 9, 2015 at 15:05

Pablo Fernandez

106k59 gold badges196 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Asbjørn Ulsberg · Accepted Answer · 2025-06-13 08:05:33Z

For anyone coming here from Google: in short, SQLite locks the DB every time you write, and if an unlucky reader tries to start a transaction, it'll get SQLITE_BUSY.

SQLite tuning:

Parameter	Description
`_journal_mode=WAL`	WAL will allow one writer and many readers
max connections=1	Only allow one connection (not necessary if you use `busy_timeout`)
`_busy_timeout=500`	If a transaction cannot start due to a lock, it won't throw SQLITE_BUSY but wait this many milliseconds first
`_txlock=deferred`	"deferred" means write lock will only start on a Tx when a write statement is executed "immediate" will start a WRITE transaction every time you do "BEGIN": this may give SQLITE_BUSY on other connections
`_synchronous=NORMAL`	The engine will still fsync at the most critical moments, but less often than in `FULL` mode.

Here's what your DSN may look like:

file:db.sqlite?_journal_mode=WAL&_busy_timeout=500&_synchronous=NORMAL&_txlock=deferred

Sources:

Collectives™ on Stack Overflow

Sqlite3 error: database is locked in golang

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related