Make all write db transaction asynchronous in IPS5

[KT Walrus]

Consider re-organizing PHP code that updates the database so that these updates can be performed asynchronously and not by HTTP while the user waits for the page to load. For example, the _createMember() function called during registration could be executed asynchronously (including sending the verification email). Some existing code may have to change to make all database writes done asynchronously (for example, showing the post immediately after queuing the database updates might not have been committed yet to the database, so the UX might have to change slightly to show a "post is being moderated" and should be approved in a few minutes).

This reorganization of database updates has the following advantages:

1. These PHP functions can be executed by a system task and doesn't slow down the page loads waiting on all the updates to complete.
2. If all database writes are off-loaded to a system task (except maybe those in the ACP), the mysql database user needs only read-only permissions to the database (making IPS5 even less vulnerable to unauthorized database writes and read-only MySQL slaves may be utilized to add reliability and application performance).
3. The admin could configure their installation to use RabbitMQ (or even Redis) to queue these system tasks for execution by one or more PHP workers.
4. Each individual task could be marked as "may be executed in parallel" or "must be executed serially".  Parallel execution probably wouldn't be necessary except for the busiest sites.
5. The PHP workers would normally execute these tasks within a few seconds of being queued (especially if multiple PHP workers configured).
6. The entire set of database writes can be applied to the database in a single SQL transaction (using BEGiN/COMMIT statements surrounding the call to the PHP function) instead of using AUTOCOMMIT for each individual query.
7. Using SQL transactions properly is better for database integrity and for database replication (used by sites that require High Availability).
8. Many other minor advantages not detailed above.

Using asynchronous tasks to perform all database updates also would permit doing other functions asynchronously (like sending notifications, queuing outgoing emails, even possibly invalidating a Redis cache only when data in the cache needs to be updated due to database changes to particular rows in a table). More and more features in the suite might be done asynchronously over time (like video and audio transcoding, etc).

[bfarber]

Without getting into the rest of the suggestion yet, I did want to point out related to #2 that we do already support read/write separation.

[KT Walrus]

5 hours ago, bfarber said:

Without getting into the rest of the suggestion yet, I did want to point out related to #2 that we do already support read/write separation.

Read/write separation isn't the same as read-only access to the database from user HTTP requests. Separating the writes asynchronously wouldn't help much unless you separate out the whole transaction (e.g., run _createMember()) asynchronously in an admin task on behalf of the currently logged-in member. There is still a possibility for a hacker to find a hole to execute an update query they craft, but it does make it more unlikely (since only the admin task has permission to update tables in the database). You could extend this further to have multiple admin mysql users so the specific mysql user that only has update permissions on the tables the transaction is supposed to execute. This would provide even greater security as most transactions would not allow promoting a member to the admin group (which is the holy grail for hackers, I'd think).

So, @bfarber, I argue that the benefits of this proposal to make IPS5 transaction safe and asynchronous (out of the HTTP request processing) out-weigh the small re-factoring of PHP code required.

[bfarber]

I understand, and explicitly said I wasn't getting into the rest of the suggestion right now. I was only responding to this bit, because it is not a highly publicized or obvious feature, but is one that is present already for those who want to use it:

Quote

read-only MySQL slaves may be utilized to add reliability and application performance