Uncaught Exception: Unable to execute statement: database is locked

Hey Ollie,

I’m also receiving this error (but before I updated to the most recent version). It actually prevents the post from saving correctly.

Im not clued up on SQLite as I would like to be but apparently there is a WAL mechanism. Thought it was worth mentioning as it seems it would solve this, if it’s integrated.

Thanks for these bug reports. The error message means a php instance waited more than 500 milliseconds for another instance’s write operation to complete.

My statistics show some extreme outliers in the time to read and write some cache items — hundreds of milliseconds. The lookup times are hundreds of MICROseconds. I’m not sure why this is happening, but it looks like we need a longer timeout.

@spaceling ‘s hosting service provides an older version of SQLite, 3.7.17. (So does mine, Greekgeeks.com with the “native” php setup.) That means cache-item saves require a BEGIN / UPDATE / INSERT / COMMIT operation rather than a simpler UPSERT operation. It’s possible that’s the reason for the longer waits.

@spaceling and my service also don’t, by default, enable php’s igbinary extension (for serializing php objects). That’s not optimal: it means giant cached objects like the content feeds for the dashboard front door take more space than they need to. But it affects the timeouts indirectly if at all.

I’ve been using the MEMORY journaling mode rather than the WAL journaling mode in an attempt to save SSD / HDD IO operations. I’ll switch to WAL.

In v1.1.1 we now have three optional wp-config.php variables.

• WP_SQLITE_OBJECT_CACHE_DB_FILE. sqlite file pathname. Default: …/wp-content/.ht.object_cache.sqlite .
• WP_SQLITE_OBJECT_CACHE_TIMEOUT SQLite timeout. Default: 5000 milliseconds.
• WP_SQLITE_OBJECT_CACHE_JOURNAL_MODE Default: ‘WAL’. Possible values DELETE | TRUNCATE | PERSIST | MEMORY | WAL | NONE. See https://www.sqlite.org/pragma.html#pragma_journal_mode

I’m seeing extreme outlier times on a couple of different hosting services. Even with WAL journaling. One or two operations from among thousands takes at least 100x the time of the rest, generating the timeouts.

There’s some sort of operational thing I don’t understand here. SQLite needs to memory map, then read and write, its file. I’ve confirmed that the file systems in use are ordinary locally-attached file systems, not nfs or smb-accessed file systems. Is there some intermittent delay? fsync(1) in progress?

In V1.1.1, the timeout is up, to 5 sec from 0.5 sec. This should prevent at least some of these very infrequent outliers from timing out.

But, please, any timeout reports are still very welcome. If you can let me know the hosting provider and plan you use, that will also help a lot. I’m building this for site owners who use budget hosting services, so it’s important to know at least some of the ways those hosting services can flake out, and work around them.

(And, if you’re a hosting service, I’d love to hear from you too.)

• This reply was modified 1 year, 10 months ago by OllieJones.

Hi @olliejones,

We’re using the latest version and we’re still seeing database locked. Here’s a sample of the error.

Any suggestions? Overall I think it’s working ‘correctly’. Perhaps increasing the 5 seconds to something odd like 10/15 might help here?

[02-Feb-2023 14:24:14 UTC] PHP Fatal error:  Uncaught Exception: Unable to execute statement: database is locked in /var/www/a491e9f6-a9d9-4cf0-a98f-e4fb3c41594e/public_html/wp-content/object-cache.php:1337
Stack trace:
#0 /var/www/a491e9f6-a9d9-4cf0-a98f-e4fb3c41594e/public_html/wp-content/object-cache.php(1337): SQLite3Stmt->execute()
#1 /var/www/a491e9f6-a9d9-4cf0-a98f-e4fb3c41594e/public_html/wp-content/object-cache.php(1304): WP_Object_Cache->handle_put('wp_query:22ff6f...', Array, 'posts', 0)
#2 /var/www/a491e9f6-a9d9-4cf0-a98f-e4fb3c41594e/public_html/wp-content/object-cache.php(2029): WP_Object_Cache->set('wp_query:22ff6f...', Array, 'posts', 0)
#3 /var/www/a491e9f6-a9d9-4cf0-a98f-e4fb3c41594e/public_html/wp-includes/class-wp-query.php(3292): wp_cache_set('wp_query:22ff6f...', Array, 'posts')
#4 /var/www/a491e9f6-a9d9-4cf0-a98f-e4fb3c41594e/public_html/wp-includes/class-wp-query.php(3749): WP_Query->get_posts()
#5 /var/www/a491e9f6-a9d9-4cf0-a98f-e4fb3c41594e/public_html/wp-includes/post.php(2415): WP_Query->query(Array)
#6 /var/www/a491e9f6-a9d9-4cf0-a98f-e4fb3c41594e/public_html/wp-content/themes/XXX/core/class-membership-jobs.php(13): get_posts(Array)
#7 /var/www/a491e9f6-a9d9-4cf0-a98f-e4fb3c41594e/public_html/wp-content/themes/XXX/core/class-membership-jobs.php(562): MembershipJobs->__construct()
#8 /var/www/a491e9f6-a9d9-4cf0-a98f-e4fb3c41594e/public_html/wp-content/themes/XXX/functions.php(27): require_once('/var/www/a491e9...')
#9 /var/www/a491e9f6-a9d9-4cf0-a98f-e4fb3c41594e/public_html/wp-settings.php(585): include('/var/www/a491e9...')
#10 /var/www/a491e9f6-a9d9-4cf0-a98f-e4fb3c41594e/public_html/wp-config.php(87): require_once('/var/www/a491e9...')
#11 /var/www/a491e9f6-a9d9-4cf0-a98f-e4fb3c41594e/public_html/wp-load.php(50): require_once('/var/www/a491e9...')
#12 /var/www/a491e9f6-a9d9-4cf0-a98f-e4fb3c41594e/public_html/wp-admin/admin-ajax.php(22): require_once('/var/www/a491e9...')
#13 {main}
  thrown in /var/www/a491e9f6-a9d9-4cf0-a98f-e4fb3c41594e/public_html/wp-content/object-cache.php on line 1337

A thought:

SQLite doesn’t work properly on network-attached storage (NFS, CIFS, stuff like that) disk volumes. Looking at your pathnames makes me wonder if your site’s files are networked-attached.

Try putting something like this in your wp-config.php file.

define ('WP_SQLITE_OBJECT_CACHE_DB_FILE', '/var/tmp/sullivan.sqlite' );

That puts the sqlite file into the os’s /var/tmp folder, which ordinarily is rigged to be a local HDD/ SSD.

I would suggest /tmp instead of /var/tmp if I knew for certain it wasn’t a very small RAMdisk as it is on some machines. The SQLite file will be a few tens of megabytes in size on a typical (not vast) site.

And, thanks for helping track this down. I wonder if it’s possible for a php program to find out whether a file is local or NAS? If so, maybe the plugin should warn the user.

• This reply was modified 1 year, 10 months ago by OllieJones.

Hi @olliejones,

I’m going to try shortly. My entire /var folder is an NVME block storage. I’ll see what happens when I place it in /tmp (local NVME) and report back.

No news is ….. (good news, I hope?)

Hi @olliejones,

It seems /tmp is not available to me in my containerized environment. I did try a few things but in the end, it didn’t work, so now my SQLite file is always on the network attached storage. I read here under recommendations that WAL should be OK (i think?) because the read/writes are on the same machine. Assuming that’s the case, what else would you recommend?

If you have access to containers, it might make sense to stand up a redis server in a container and use that instead of SQLite for your cache. It’s less dependent on real-time file system access.

There are also ways to tell a container to access a file system on the host. I’m no expert on configuring containers, so I hesitate to tell you how to do that.