Simon Willison's Weblog: transactions

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

Datasette 1.0a10

2024-02-18T05:10:13+00:00

Datasette 1.0a10

The only changes in this alpha release concern the way Datasette handles database transactions. The database.execute_write_fn() internal method used to leave functions to implement transactions on their own—it now defaults to wrapping them in a transaction unless they opt out with the new transaction=False parameter.

In implementing this I found several places inside Datasette—in particular parts of the JSON write API—which had not been handling transactions correctly. Those are all now fixed.

Tags: projects, sqlite, transactions, datasette

How to implement a “dry run mode” for data imports in Django

2022-10-13T16:22:09+00:00

How to implement a “dry run mode” for data imports in Django

Adam Johnson describes in detail a beautiful pattern for implementing a dry-run mode for a Django management command, by executing ORM calls inside an atomic() transaction block, showing a summary of changes that are made and then rolling the transaction back at the end.

Via @adamchainz

Tags: django, transactions, adam-johnson

The trouble with transaction.atomic

2020-11-20T15:57:03+00:00

The trouble with transaction.atomic

David Seddon provides a detailed explanation of Django’s nestable transaction.atomic() context manager and describes a gotcha that can occur if you lose track of whether your code is already running in a transaction block, since you may be working with savepoints instead—along with some smart workarounds.

Via @adamchainz

Tags: django, transactions

Django now has fast tests

2009-01-16T11:40:20+00:00

Django now has fast tests

Changeset 9756 switched Django’s TestCase class to running tests inside a transaction and rolling back at the end (instead of doing a full dump and reload). “Ellington’s test suite, which was taking around 1.5-2 hours to run on Postgres, has been reduced to 10 minutes.”

Tags: django, ellington, eric-holscher, python, testing, transactions

Django Unit Tests and Transactions

2008-07-07T14:14:00+00:00

Django Unit Tests and Transactions

If you’re using a transactional database engine (MySQL with InnoDB, Postgres or SQLite) you can speed things up by running each of your unit tests inside a transaction and rolling back in tearDown().

Tags: django, innodb, mysql, postgresql, python, sqlite, testing, transactions