Unexpected error: "migrate database: sqlite: step: database is locked"

[bradenrayhorn]

Hi, thanks for the great package, it's been very useful for me.

I'm using the sqlitemigration package to create the pool and run migrations. However, when running some automated end-to-end tests that do work on the same sqlite database concurrently, I'm occasionally running into an unexpected error, migrate database: sqlite: step: database is locked. I have not configured any non-default busy timeout options.

For more context on when this error is triggered in the automated tests.

• A single web-server go process starts up that is connected to the sqlite database for the duration of the tests
• Before each test runs, a go program starts up, connects to the database, seeds some data, then closes
  • This program is the one that occasionally runs into the above error.

Reading the docs, the default busy option appears to be SetBlockOnBusy(), so I would have thought I'd never run into this error and the connection would wait until it could acquire a lock.

Reading through the package code I'm unsure why I'd be seeing this error either, but I'm hoping you have some idea. Is there something special with the migration startup itself that might make this error appear?

For reference: my code that is establishing a pool

Thank you.

[zombiezen]

I suspect the problem is with the code you've sent over. There may be transient errors before OnReady is called. That said, my expectation aligns with yours that the migration shouldn't be encountering a busy error.

[bradenrayhorn]

@zombiezen Thank you. I've updated my logic to wait for pool.Get to successfully grab a connection as a way to detect pool readiness, following what I see in the sqlitemigration package tests. I am no longer using OnReady. Reference code here.

Unfortunately, this error is still occurring. Below is an example of the error from one of my runs.

ced: error: failed to create sqlite pool: get sqlite connection: migrate database: sqlite: step: database is locked
            migrate database: sqlite: step: database is locked

I may try setting a busy timeout instead of depending on the default busy handler, and see if that avoids this database is locked error. I'm still unsure why this happens in the first place.

Let me know if you'd like further details, or have further suggestions.

Thanks again.

[bradenrayhorn]

Well that was a quick experiment, using a busy timeout instead of the busy handler had no effect, the pool still bumps into the database is locked error.

[bradenrayhorn]

@zombiezen I've discovered a few things, with some good news.

First, the issue appears to be fixable in sqlitemigration ensureAppID function. The function opens a deferred transaction that will later be upgraded to a write transaction. At the point of upgrading to a write transaction, if the database is locked, SQLite will fail immediately with SQLITE_BUSY [1] [2] and the busy timeout will have no effect, causing the pool to fail to start.

If ensureAppID is switched use an immediate transaction, that tells SQLite this is a write transaction. SQLite will attempt to lock the database immediately. If a lock cannot be acquired, the busy timeout is used allowing the transaction to wait until it can get a lock, thus allowing the pool to start successfully.

Second, even without the change to ensureAppID, setting PRAGMA synchronous = NORMAL; [3] appears to resolve the issue, or at least greatly reduce its frequency. I think it would still be nice to fix the issue with the ensureAppID code change though.

I've opened PR #98 for your consideration.

Thank you.