Go-sqlite3: Database is locked -- Why do we inform people to enabled shared cache mode?

[bvinc]

I think that this project is suffering from a misunderstanding of SQLITE_LOCK vs SQLITE_BUSY. I'd like to start a discussion about this, and why anyone has ever encouraged to enabled shared cache mode to solve a SQLITE_LOCK error.

The SQLITE_BUSY result code indicates that the database file could not be written (or in some cases read) because of concurrent activity by some other database connection, usually a database connection in a separate process.

The SQLITE_LOCKED result code indicates that a write operation could not continue because of a conflict within the same database connection or a conflict with a different database connection that uses a shared cache.

https://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked

Without shared cache mode:

SQLITE_BUSY is expected to happen with any multithreaded application. This just means that two connections are conflicting with each other. You need to set up a busy timeout so that SQLite can automatically retry in this case. Problem solved.

SQLITE_LOCKED only happens if a single SQLite connection is doing something that doesn't make sense, such as dropping a table while still being in the middle of a SELECT statement. If someone is getting this error, either they're doing something crazy, or a connection was returned to the pool without being ready to be returned to the pool, because statements are still executing without being finalized.

Without shared cache mode, these are the only problems that you should expect.

But we've encouraged everyone to enable shared cache mode, so now the problem has changed. In shared cache mode, there is really only ever a single connection to the database at a time, and all "connections" are just sharing the same connection.

With shared cache mode:

SQLITE_BUSY pretty much doesn't happen. You can't conflict with other connections if there is only a single connection.

SQLITE_LOCKED will happen much more often. Now, normal, seemingly separate connections will get SQLITE_LOCKED if they happen to run conflicting statements at the same time. Worse, you can't set a busy timeout to fix it. If a lock error happens, it's failed. The only way around this now is to support the unlock_notify API, which this driver currently does not support. So we dig our heels in even further and limit the connection pool to only a single connection.

So basically, it seems incorrect to me that you should ever suggest people turn on shared cache mode to fix SQLITE_LOCKED errors. It should only increase the frequency of SQLITE_LOCKED. Is there something that I'm misunderstanding? Is there a reason I'm not understanding why SQLITE_LOCKED errors are unavoidable without shared cache mode?

[rittneje]

So we dig our heels in even further and limit the connection pool to only a single connection.

Also of note is the fact that if you only have a single connection to a given database, using a shared cache is pointless and just adds unnecessary overhead.

No doubt adding to the confusion is the fact that when getting the error message, the SQLite library itself turns SQLITE_BUSY into "database is locked" and SQLITE_LOCKED into "database table is locked". It's very easy to see the former error message and mistake it for SQLITE_LOCKED.

As for why shared cache is recommended, one of the earliest bug reports about "database is locked" I can find is #39. The reason turning on the shared cache fixes the problem is:

1. The Go standard library assumes you want isolation between the result fetching and the modification. Consequently, it tries to establish a second database connection in this case.
2. Until you call sqlite3_reset on a prepared statement for which you are fetching results, that connection maintains a read lock on the database.
3. When a connection tries to write to the database, it must first acquire the write lock. If there is at least one active reader, you get SQLITE_BUSY. (If you have a busy timeout, it will retry until the timeout before returning.)
4. Since two connections that are using a shared cache work together as a single connection, SQLite will allow one connection to write in the middle of the other connection's read, as if they were both the same connection.

Taking these points all together, it seems like people were trying to do something that, while allowed by SQLite, is disallowed by the standard library. The standard library then opens a second connection behind the scenes, and the user gets a mysterious SQLITE_BUSY error.

The driver does seem to support the unlock notify API now, provided you build with the sqlite_unlock_notify tag.

It seems to me that there a few ways to use this library correctly:

1. Force the connection pool to a single connection with a private cache. You can never try to modify the database while iterating through result rows, as doing so would result in a deadlock.
2. Allow multiple connections in the pool with each having a private cache. This will require setting an appropriate busy timeout. You can never try to modify the database while iterating through result rows, as doing so would result in SQLITE_BUSY.
3. Allow multiple connections in the pool with each having a shared cache. You can modify the database while iterating through result rows. However, you cannot modify the table(s) from which you are reading. (If using the unlock notify API, this is because the connection doing the modification will wait for the connection doing the reading to finish, which never happens, resulting in a deadlock. If the unlock notify API were not being used, this would instead result in SQLITE_LOCKED.)
4. Allow multiple connections in the pool with each having a private cache, and use write-ahead logging. You can modify the database while iterating through result rows, including the table(s) from which you are reading. Note that this requires private caches; using a shared cache will result in the same issue mentioned in (3).
5. Always use the same transaction for fetching result rows and modifying the database if you need to modify the database within a rows.Next() loop. In this case, it does not matter whether you use WAL or a rollback journal, nor does it matter whether you use a private or shared cache.

tx, err := db.Begin()
if err != nil {
    return err
}
defer tx.Rollback()

rows, err := tx.Query(...)
if err != nil {
    return err
}
defer rows.Close()

for rows.Next() {
    if err := rows.Scan(...); err != nil {
        return err
    }
    if err := tx.Exec(...); err != nil {
        return err
    }
}

if err := rows.Err(); err != nil {
    return err
}

return tx.Commit()

1. Same as (5), but use the new DB.Conn method instead of a transaction.

c, err := db.Conn(ctx)
if err != nil {
    return err
}
defer c.Close()

rows, err := c.QueryContext(ctx, ...)
if err != nil {
    return err
}
defer rows.Close()

for rows.Next() {
    if err := rows.Scan(...); err != nil {
        return err
    }
    if err := c.ExecContext(ctx, ...); err != nil {
        return err
    }
}

if err := rows.Err(); err != nil {
    return err
}

return c.Close()

[GJRTimmer]

No activity / response; closed.