Go-sqlite3: database is locked

[tcurdt]

I am iterating a resultset of a query and try to update some rows while doing so.
When try the update I am getting a database is locked.
This is all single threaded.

A stripped down version:

  db, err := sql.Open("sqlite3", database)

  rows, err := db.Query()
  defer rows.Close()

  stmt, err := db.Prepare() // update

  for rows.Next() {
    err = rows.Scan()

    res, err := stmt.Exec() // LOCKED
    affect, err := res.RowsAffected()

  }

  err = rows.Err()

I must be missing something. Why does the resultset lock the database/table?
Any pointers?

[tcurdt]

OK, so it looks like that's a limitation of sqlite an no way around this?

[tcurdt]

Reading https://github.com/mattn/go-sqlite3/issues/39 and specifically http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked it sounds like it should be possible now.

Appending ?cache=shared&mode=rwc did not make a difference with sqlite 3.19.3 though.

[tcurdt]

I also tried using a transaction for each update to no avail.

  rows, err := db.Query()
  defer rows.Close()

  for rows.Next() {
    err = rows.Scan()


    tx, err := db.Begin()
    stmt, err := tx.Prepare()
    res, err := stmt.Exec()
    tx.Commit()

    affect, err := res.RowsAffected()
  }

  err = rows.Err()

This is a single thread, a single db handle, a single writer - shouldn't this work?

[tcurdt]

To quote from the sqlite website:

Here are other reasons for getting an SQLITE_LOCKED error:
...

1. Trying to write to a table while a SELECT is active on that same table.
   As of check-in [3355] (2006-08-16 after version 3.3.7) this is now allowed.

[tcurdt]

I don't fully understand why exactly but it seems this makes it work:

db.Exec("PRAGMA journal_mode=WAL;")

[mattn]

OK, so it looks like that's a limitation of sqlite an no way around this?

Yes

I don't fully understand why exactly but it seems this makes it work:

WAL mode record update information sequencially. So this might fix your problem. But AFAIK, your stmt must be created via another connection.

[tcurdt]

Well, it doesn't really seem to be a limitation of sqlite since version 3.3.7. See the references above.

It seems to work OK in WAL mode and no other connection is necessary - but might be worth verifying this. I have opened a thread on the sqlite list.

[GJRTimmer]

@tcurdt Any update ?

[tcurdt]

@GJRTimmer Not sure it's completely authoritative but according to the people on the user list WAL mode is totally fine with one connection.

What I am still a little fuzzy about is whether this should also work without WAL mode or not. But that's purely out of curiosity. Works for me now with WAL enabled.

So from my end it would be OK to close the issue. That said I think it would be good to either document this better and/or be able to pass this as option on Open.

[GJRTimmer]

@tcurdt This looks like an other issue with database is locked.
Could you try the following:

• WAL
• db.SetMaxOpenConns(1)

This might resolve your database is locked

[tcurdt]

@GJRTimmer Please see above. As I said: with WAL it works OK.

[GJRTimmer]

Sorry missed it, solved.

[duod4o]

Well, it doesn't really seem to be a limitation of sqlite since version 3.3.7. See the references above.

It seems to work OK in WAL mode and no other connection is necessary - but might be worth verifying this. I have opened a thread on the sqlite list.

could you paste your code please? Because i encounter this problem recently....

[rittneje]

@duod4o Are you trying to modify the database while iterating through result rows? If so, the correct solution is actually to do everything in a single transaction. Using WAL mode or a shared cache is NOT necessary. (A shared cache in particular can actually cause worse problems depending on what you are doing.)

tx, err := db.Begin()
defer tx.Close()

stmt, err := tx.Prepare("UPDATE ...")

rows, err := tx.Query("SELECT ...")
defer rows.Close()
for rows.Next() {
    err := rows.Scan(...)
    res, err := stmt.Exec(...)
}
err := rows.Err()

return tx.Commit()

In case you are wondering, the reason for the error is because Go and SQLite don't mix well. By default (without using a transaction), Go tries to do the read and the update on separate database connections. But SQLite puts a read lock on the database while you are iterating through the SELECT results. Consequently the second connection cannot acquire a write lock, so the UPDATE fails. By using a transaction, you force Go to do everything on a single connection. In this case, SQLite simply upgrades the read lock to a write lock when you go to modify the database. The lock is released when the transaction is committed or rolled back.