Correctly handle read/write database replicas in transactions

[gasman]

Follow-up to #12825 and #11237. Wagtail does not account for multiple databases in configurations where the database router directs reads and writes to different databases - for example, a primary read/write database along with several read-only replicas. Whenever both read and write operations occur within the same transaction, those should be directed to the same database (i.e. one that accepts writes). This isn't currently guaranteed.

#12825 identifies a situation where this breaks page moves - so far this is is the only known failure case, but we'd want to test this more thoroughly before claiming multi-database support.

Describe the solution you'd like

Find a way to run the Wagtail test suite in a multi-database environment with read-only replicas, and confirm that this is able to identify failure cases such as #12825 where queries are directed to multiple databases within the same transaction. Fix these by explicitly specifying the database where relevant, e.g. Page.objects.using(write_db)

This may involve rethinking how our tests are constructed, given that they typically work in several phases:

1. write to the database to set up the necessary initial state for the test
2. make a call to Wagtail application code
3. read back from the database and verify that the results are as expected

Presumably we need to ensure that step 1 happens on all databases, and step 3 is either performed on the write database or an explicit sync is performed before it.

Describe alternatives you've considered

Conversation on #12825 suggested configuring the database router to always route to the write database when a transaction is active. However, this would mean that transactions containing only read operations would fail to take advantage of the read-only replicas, so this should only be considered a workaround.

Working on this

This would require someone with experience setting up unit tests for multi-database configurations, or willing to acquire that knowledge.

Anyone can contribute to this. View our contributing guidelines, add a comment to the issue once you’re ready to start.