In MySQL's InnoDB, you may have to start using SavePoints.
Sets a named transaction savepoint with a name of identifier. If the current transaction has a savepoint with the same name, the old savepoint is deleted and a new one is set.
ROLLBACK TO SAVEPOINT
Rolls back a transaction to the named savepoint without terminating the transaction. Modifications that the current transaction made to rows after the savepoint was set are undone in the rollback, but InnoDB does not release the row locks that were stored in memory after the savepoint. (For a new inserted row, the lock information is carried by the transaction ID stored in the row; the lock is not separately stored in memory. In this case, the row lock is released in the undo.) Savepoints that were set at a later time than the named savepoint are deleted.
If the ROLLBACK TO SAVEPOINT statement returns the following error, it means that no savepoint with the specified name exists:
ERROR 1305 (42000): SAVEPOINT identifier does not exist
The RELEASE SAVEPOINT statement removes the named savepoint from the set of savepoints of the current transaction. No commit or rollback occurs. It is an error if the savepoint does not exist.
All savepoints of the current transaction are deleted if you execute a COMMIT, or a ROLLBACK that does not name a savepoint.
A new savepoint level is created when a stored function is invoked or a trigger is activated. The savepoints on previous levels become unavailable and thus do not conflict with savepoints on the new level. When the function or trigger terminates, any savepoints it created are released and the previous savepoint level is restored.
Given this explanation of using SavePoints, you answer may be as simple as issuing a SavePoint in appropriate places:
SQL to Add User
SQL to Add Other Things
SQL to Add Additional Things
This may help mitigate some issues with ROLLBACKs.
Why do most CMS products not allow for such mechanisms? Most CMS products use the dreaded MyISAM storage engine.
For example, Drupal and WordPress usually install MyISAM and plugins usually expect MyISAM tables. I have written posts about recommending the switch to InnoDB.
What's even worse is that more recent plugins for both WordPress and Drupal still make table changes and leave them as MyISAM. Any CMS that uses FullText Indexes cannot convert tables from MyISAM to InnoDB in MySQL 5.5 and prior. MySQL 5.6 just become GA on Feb 05, 2013 and does support FullText Indexing. Give MySQL 5.6 some maturation time before diving in.