13

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
}
tshepang
  • 12,111
  • 21
  • 91
  • 136
superuser123
  • 151
  • 1
  • 1
  • 12
  • 1
    You will have to provide more informations, especially code which demonstrates your problem. – Volker Sep 09 '15 at 12:42
  • Are you sure [something else is not locking your DB](http://stackoverflow.com/questions/151026/how-do-i-unlock-a-sqlite-database)? – Ainar-G Sep 09 '15 at 12:46
  • 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 Sep 09 '15 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 Sep 09 '15 at 14:17

1 Answers1

26

Try defering the rows.Close():

if err != nil {
    return err
}
defer rows.Close()
if rows.Next() {
    ...
}
Pablo Fernandez
  • 103,170
  • 56
  • 192
  • 232
  • You hit the point. I solved my problem by closing all "rows" in my code. Although your answer is not the exact solution to my problem, but I will check your answer. Thank you for answering. – superuser123 Sep 10 '15 at 02:49
  • 3
    Excellent! Thank you so much! I wasn't even aware that you ought to close down rows besides the database handler; I thought that this would be 'automatic', but clearly it isn't. Also, the same issue happens with _statements_; I'm using https://godoc.org/github.com/mattn/go-sqlite3 and there is a func to close statements as well, after they're used. Once I did that, the dreadful 'database is locked' error disappeared, after giving me a headache for days! – Gwyneth Llewelyn Jun 13 '17 at 20:14