How can i unlock the Database in Go-CodePudding

Im a newbie in go and not the best in sql.

I have a simple Table in my Database with the name of users. I store the SAM, First Name and Last Name in the table. When i now try to change something in the database, i get the error database is locked. Thats my code:

func createNewUser(w http.ResponseWriter, r *http.Request) {
    var user User
    err := decodeJSONBody(w, r, &user)
    if checkError(w, err) {
        return
    }
    rows, err := mainDB.Query("SELECT * FROM users WHERE SAM = ?", user.Sam)
    if checkError(w, err) {
        return
    }
    defer rows.Close()
    if rows.Next() {
        http.Error(w, "User already exists", http.StatusConflict)
        return
    }
    _, err = mainDB.Exec("INSERT INTO users (SAM, Vorname, Nachname) VALUES (?, ?, ?)", user.Sam, user.Vorname, user.Nachname)
    if checkError(w, err) {
        return
    }
    json.NewEncoder(w).Encode(user)
}

decodeJSONBody and checkError work and have nothing to do with the database. And as far as I've learned, rows.Close should close the columns so that I can write something back in

CodePudding user response：

As per the comments SQLite has some limitations around locking/concurrency which means you need to take care when running multiple statements concurrently. Unfortunately I had not reviewed your code in detail when posting my comment so, despite seemingly solving the issue, it was in error.

You had added a defer rows.Close(); this will free up the database connection used to run the query but, due to the defer, this will only happen when the surrounding function returns. Normally this is not a big issue because looping through a result set in its entirety automatically closes the rows. The documentation states:

If Next is called and returns false and there are no further result sets, the Rows are closed automatically and it will suffice to check the result of Err.

In your code you do return if rows.Next() is true:

if rows.Next() {
    http.Error(w, "User already exists", http.StatusConflict)
    return
}

This means that adding an extra rows.Close() should not be needed. However as you say "added rows.Close() multiple times, and now it works" I suspect that your full code may have been a bit more complicated than that presented (and one of the added rows.Close() was needed).

So adding extra calls to rows.Close() should not be needed; it will not cause an issue (other than an unnecessary function call). However you should check for errors:

rows, err := mainDB.Query("SELECT * FROM users WHERE SAM = ?", user.Sam)
if checkError(w, err) {
    rows.Close()
    return
}
if rows.Next() {
    http.Error(w, "User already exists", http.StatusConflict)
    return
}
if err = rows.Err(); err != nil {
   return // It's worth checking fort an error here
}

Note that the FAQ for go-sqlite3 includes information on dealing with "Error: database is locked" (and it's worth ensuring you follow the recommendations).

Note2: Consider using EXISTS instead of running the query and then attempting to fetch a row - it is likely to be faster and allows you to use QueryRow which simplifies your code.