[meta] Background worker issues

[SchrodingersGat]

A couple of recent issues have highlighted some shortcomings of the existing background worker implementation - especially for long-running complex processes which touch the database many times.

Related Issues

• Unable to consume items #11493
• Default database backend for installer #11501

Problem Overview

In short:

• The background worker is not very stable on a sqlite backend due to db locks / concurrency issues
• There is no way for users to monitor the progress of a long-running task - or to determine if it passed or failed
• Some background processes are split into multiple other processes - which makes concurrency issues worse
• Some tasks are run in the "wrong order" - task success is saved first, before the long running processes are actually performed

Fixes

• Improve robustness of sqlite database - [db] Backend setting improvements #11500
• Improve shipment completion task - Fix complete_sales_order_shipment task #11525
• Provide monitoring of background tasks - [API] Monitor task #11527
• Improve build consume task - Build consume fix #11529
• Improve build allocation task - Build consume fix #11529
• Improve build complete task