Why Does My Golang Sqlite3 Code Get a 'Database is Locked' Error, and How Can I Fix It?

How to Fix the Sqlite3 Error: Database is Locked in Golang

In Golang, when working with Sqlite3 databases, you may encounter the error, "database is locked." This error indicates that multiple threads cannot use the same database file concurrently.

Root of the Problem

Even though you may only have a single connection in your program and close all query results, you may still end up with multiple handles to the database file. This issue can be verified using the Opendfileview program.

Code Creating Multiple Database File Handles

The following code demonstrates how two database file handles are created:

import "log"

import (
    "database/sql"
    "fmt"
)

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
    }
    defer rows.Close() // Missing defer
    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
}

Solution

To resolve the issue, you can defer the rows.Close() call as shown below:

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

By deferring the rows.Close() call, you ensure that the rows are closed even if a panic or error occurs during the iteration. This helps prevent additional database file handles from being created and resolves the "database is locked" error.

The above is the detailed content of Why Does My Golang Sqlite3 Code Get a 'Database is Locked' Error, and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!