Simon Willison's Weblog: sqlite-busy

What to do about SQLITE_BUSY errors despite setting a timeout

2025-02-17T07:04:22+00:00

What to do about SQLITE_BUSY errors despite setting a timeout

Bert Hubert takes on the challenge of explaining SQLite's single biggest footgun: in WAL mode you may see SQLITE_BUSY errors even when you have a generous timeout set if a transaction attempts to obtain a write lock after initially running at least one SELECT. The fix is to use BEGIN IMMEDIATE if you know your transaction is going to make a write.

Bert provides the clearest explanation I've seen yet of why this is necessary:

When the transaction on the left wanted to upgrade itself to a read-write transaction, SQLite could not allow this since the transaction on the right might already have made changes that the transaction on the left had not yet seen.

This in turn means that if left and right transactions would commit sequentially, the result would not necessarily be what would have happened if all statements had been executed sequentially within the same transaction.

I've written about this a few times before, so I just started a sqlite-busy tag to collect my notes together on a single page.

Via lobste.rs

Tags: databases, sqlite, transactions, sqlite-busy

Supercharge the One Person Framework with SQLite: Rails World 2024

2024-10-16T22:24:45+00:00

Supercharge the One Person Framework with SQLite: Rails World 2024

Stephen Margheim shares an annotated transcript of the YouTube video of his recent talk at this year's Rails World conference in Toronto.

The Rails community is leaning hard into SQLite right now. Stephen's talk is some of the most effective evangelism I've seen anywhere for SQLite as a production database for web applications, highlighting several new changes in Rails 8:

... there are two additions coming with Rails 8 that merit closer consideration. Because these changes make Rails 8 the first version of Rails (and, as far as I know, the first version of any web framework) that provides a fully production-ready SQLite experience out-of-the-box.

Those changes: Ensure SQLite transaction default to IMMEDIATE mode to avoid "database is locked" errors when a deferred transaction attempts to upgrade itself with a write lock (discussed here previously, and added to Datasette 1.0a14 in August) and SQLite non-GVL-blocking, fair retry interval busy handler - a lower-level change that ensures SQLite's busy handler doesn't hold Ruby's Global VM Lock (the Ruby version of Python's GIL) while a thread is waiting on a SQLite lock.

The rest of the talk makes a passionate and convincing case for SQLite as an option for production deployments, in line with the Rails goal of being a One Person Framework - "a toolkit so powerful that it allows a single individual to create modern applications upon which they might build a competitive business".

Back in April Stephen published SQLite on Rails: The how and why of optimal performance describing some of these challenges in more detail (including the best explanation I've seen anywhere of BEGIN IMMEDIATE TRANSACTION) and promising:

Unfortunately, running SQLite on Rails out-of-the-box isn’t viable today. But, with a bit of tweaking and fine-tuning, you can ship a very performant, resilient Rails application with SQLite. And my personal goal for Rails 8 is to make the out-of-the-box experience fully production-ready.

It looks like he achieved that goal!

Via Hacker News

Tags: gil, rails, ruby, scaling, sqlite, sqlite-busy

What's New in Ruby on Rails 8

2024-10-07T19:17:47+00:00

What's New in Ruby on Rails 8

Rails 8 takes SQLite from a lightweight development tool to a reliable choice for production use, thanks to extensive work on the SQLite adapter and Ruby driver.

With the introduction of the solid adapters discussed above, SQLite now has the capability to power Action Cable, Rails.cache, and Active Job effectively, expanding its role beyond just prototyping or testing environments. [...]

Transactions default to IMMEDIATE mode to improve concurrency.

Also included in Rails 8: Kamal, a new automated deployment system by 37signals for self-hosting web applications on hardware or virtual servers:

Kamal basically is Capistrano for Containers, without the need to carefully prepare servers in advance. No need to ensure that the servers have just the right version of Ruby or other dependencies you need. That all lives in the Docker image now. You can boot a brand new Ubuntu (or whatever) server, add it to the list of servers in Kamal, and it’ll be auto-provisioned with Docker, and run right away.

At 37signals, we're building a growing suite of apps that use SQLite in production with ONCE. There are now thousands of installations of both Campfire and Writebook running in the wild that all run SQLite. This has meant a lot of real-world pressure on ensuring that Rails (and Ruby) is working that wonderful file-based database as well as it can be. Through proper defaults like WAL and IMMEDIATE mode. Special thanks to Stephen Margheim for a slew of such improvements and Mike Dalessio for solving a last-minute SQLite file corruption issue in the Ruby driver.

Via Hacker News

Tags: 37signals, rails, ruby, sqlite, docker, sqlite-busy

Datasette 1.0a14: The annotated release notes

2024-08-05T23:20:01+00:00

Released today: Datasette 1.0a14. This alpha includes significant contributions from Alex Garcia, including some backwards-incompatible changes in the run-up to the 1.0 release.

Metadata now lives in a database
datasette-remote-metadata 0.2a0
SQLite isolation_level="IMMEDIATE"
Updating the URLs
Everything else
Tricks to help construct the release notes

Metadata now lives in a database

The biggest change in the alpha concerns how Datasette's metadata system works.

Datasette can record and serve metadata about the databases, tables and columns that it is serving. This includes things like the source of the data, the license it is made available under and descriptions of the tables and columns.

Historically this has been powered by a metadata.json file. Over time, this file grew to include all sorts of things that weren't strictly metadata - things like plugin configuration. Cleaning this up is a major breaking change for Datasette 1.0, and Alex has been working on this across several alphas.

The latest alpha adds a new upgrade guide describing changes plugin authors will need to make to support the new metadata system.

The big change in 1.0a14 is that metadata now lives in Datasette's hidden _internal SQLite database, in four new tables called metadata_instance, metadata_databases, metadata_resources and metadata_columns. The schema for these is now included in the documentation (updated using this Cog code), but rather than accessing those tables directly plugins are encouraged to use the new set_*_metadata() and get_*_metadata() methods on the Datasette class.

I plan to use these new tables to build a new performant, paginated homepage that shows all of the databases and tables that Datasette is serving, complete with their metadata - without needing to make potentially hundreds of calls to the now-removed get_metadata() plugin hook.

datasette-remote-metadata 0.2a0

When introducing new plugin internals like this it's always good to accompany them with a plugin that exercises them. datasette-remote-metadata is a few years old now, and provides a mechanism for hosting the metadata for a Datasette instance at a separate URL. This means you can deploy a stateless Datasette instance with a large database and later update the attached metadata without having to re-deploy the whole thing.

I released a new alpha of that plugin which switches over to the new metadata mechanism. The core code ended up looking like this, imitating code Alex wrote for Datasette Core:

async def apply_metadata(datasette, metadata_dict):
    for key in metadata_dict or {}:
        if key == "databases":
            continue
        await datasette.set_instance_metadata(key, metadata_dict[key])
    # database-level
    for dbname, db in metadata_dict.get("databases", {}).items():
        for key, value in db.items():
            if key == "tables":
                continue
            await datasette.set_database_metadata(dbname, key, value)
        # table-level
        for tablename, table in db.get("tables", {}).items():
            for key, value in table.items():
                if key == "columns":
                    continue
                await datasette.set_resource_metadata(dbname, tablename, key, value)
            # column-level
            for columnname, column_description in table.get("columns", {}).items():
                await datasette.set_column_metadata(
                    dbname, tablename, columnname, "description", column_description
                )

SQLite isolation_level="IMMEDIATE"

Sylvain Kerkour wrote about the benefits of IMMEDIATE transactions back in February. The key issue here is that SQLite defaults to starting transactions in DEFERRED mode, which can lead to SQLITE_BUSY errors if a transaction is upgraded to a write transaction mid-flight. Starting in IMMEDIATE mode for Datasette's dedicated write connection should help avoid this.

Frustratingly I failed to replicate the underlying problem in my own tests, despite having anecdotally seen it happen in the past.

After spending more time than I had budgeted for on this, I decided to ship it as an alpha to get it properly exercised before the 1.0 stable release.

Updating the URLs

Here's another change that was important to get out before 1.0.

Datasette's URL design had a subtle blemish. The following page had two potential meanings:

/databasename - list all of the tables in the specified database
/databasename?sql= - execute an arbitrary SQL query against that database

This also meant that the JSON structure returned by /database.json v.s. /database.json?sql= was different.

Alex and I decided to fix that. Alex laid out the new design in issue #2360 - there are quite a few other changes, but the big one is that we are splitting out the SQL query interface to a new URL: /databasename/-/query?sql= - or /databasename/-/query.json?sql= for the JSON API.

We've added redirects from the old URLs to the new ones, so existing links should continue to work.

Everything else

Fix for a bug where canned queries with named parameters could fail against SQLite 3.46. (#2353)

This reflects a bug fix that went out in Datasette 0.64.7.

Datasette now serves E-Tag headers for static files. Thanks, Agustin Bacigalup. (#2306)

There's still more to be done making Datasette play well with caches, but this is a great, low-risk start.

Dropdown menus now use a z-index that should avoid them being hidden by plugins. (#2311)

A cosmetic bug that showed up on Datasette Cloud when using the datasette-cluster-map plugin.

Incorrect table and row names are no longer reflected back on the resulting 404 page. (#2359)

This was reported as a potential security issue. The table names were correctly escaped, so this wasn't an XSS, but there was still potential for confusion if an attacker constructed a URL along the lines of /database-does-not-exist-visit-www.attacker.com-for-more-info. A similar fix went out in Datasette 0.64.8.

Improved documentation for async usage of the track_event(datasette, event) hook. (#2319)

Fixed some HTTPX deprecation warnings. (#2307)

Datasette now serves a <html lange="en"> attribute. Thanks, Charles Nepote. (#2348)

Datasette's automated tests now run against the maximum and minimum supported versions of SQLite: 3.25 (from September 2018) and 3.46 (from May 2024). Thanks, Alex Garcia. (#2352)

Fixed an issue where clicking twice on the URL output by datasette --root produced a confusing error. (#2375)

Tricks to help construct the release notes

I still write the Datasette release notes entirely by hand (aside from a few words auto-completed by GitHub Copilot) - I find the process of writing them to be really useful as a way to construct a final review of everything before it goes out.

I used a couple of tricks to help this time. I always start my longer release notes with an issue. The GitHub diff view is useful for seeing what's changed since the last release, but I took it a step further this time with the following shell command:

git log --pretty=format:"- %ad: %s %h" --date=short --reverse 1.0a13...81b68a14

This outputs a summary of each commit in the range, looking like this (truncated):

- 2024-03-12: Added two things I left out of the 1.0a13 release notes 8b6f155b
- 2024-03-15: Fix httpx warning about app=self.app, refs #2307 5af68377
- 2024-03-15: Fixed cookies= httpx warning, refs #2307 54f5604c
...

Crucially, the syntax of this output is in GitHub Flavored Markdown - and pasting it into an issue comment causes both the issue references and the commit hashes to be expanded into links that look like this:

It's a neat way to get a quick review of what's changed, and also means that those issues will automatically link back to the new issue where I'm constructing the release notes.

I wrote this up in a TIL here, along with another trick I used where I used LLM to get Claude 3.5 Sonnet to summarize my changes for me:

curl 'https://github.com/simonw/datasette/compare/1.0a13...2ad51baa3.diff' \
  | llm -m claude-3.5-sonnet --system \
  'generate a short summary of these changes, then a bullet point list of detailed release notes'

Tags: projects, releases, sqlite, datasette, annotated-release-notes, llm, sqlite-busy

Optimal SQLite settings for Django

2024-06-13T05:04:36+00:00

Optimal SQLite settings for Django

Giovanni Collazo put the work in to figure out settings to make SQLite work well for production Django workloads. WAL mode and a busy_timeout of 5000 make sense, but the most interesting recommendation here is "transaction_mode": "IMMEDIATE" to avoid locking errors when a transaction is upgraded to a write transaction.

Giovanni's configuration depends on the new "init_command" support for SQLite PRAGMA options introduced in Django 5.1alpha.

Via Lobste.rs

Tags: django, sqlite, sqlite-busy

Optimizing SQLite for servers

2024-03-31T20:16:23+00:00

Optimizing SQLite for servers

Sylvain Kerkour's comprehensive set of lessons learned running SQLite for server-based applications.

There's a lot of useful stuff in here, including detailed coverage of the different recommended PRAGMA settings.

There was also a tip I haven't seen before about BEGIN IMMEDIATE transactions:

By default, SQLite starts transactions in DEFERRED mode: they are considered read only. They are upgraded to a write transaction that requires a database lock in-flight, when query containing a write/update/delete statement is issued.

The problem is that by upgrading a transaction after it has started, SQLite will immediately return a SQLITE_BUSY error without respecting the busy_timeout previously mentioned, if the database is already locked by another connection.

This is why you should start your transactions with BEGIN IMMEDIATE instead of only BEGIN. If the database is locked when the transaction starts, SQLite will respect busy_timeout.

Via lobste.rs

Tags: databases, performance, sql, sqlite, sqlite-busy