Start transaction, commit, and rollback

[KT Walrus]

Please consider changing to transactional db interface in 4.0 (at least for public modules). When an app needs to issue more than one insert/update/delete query for an operation (like post or registration), use START TRANSACTION, COMMIT, and/or ROLLBACK. Also, INNODB should be the default STORAGE_ENGINE (especially if MySQL 5.6+). And, it would help to make sure all db tables had a PRIMARY key (required for some forms of db replication). This will help db integrity and efficiency (in some configurations).

[bfarber]

Default table engine aside, what about MyISAM which doesn't support transactions (at least last I checked)? We can't exactly rely on something we can't guarantee will be available (*this is one of the perpetual problems you face when you release software to the world and don't control the environment it is run on).

[KT Walrus]

Default table engine aside, what about MyISAM which doesn't support transactions (at least last I checked)? We can't exactly rely on something we can't guarantee will be available (*this is one of the perpetual problems you face when you release software to the world and don't control the environment it is run on).

Non-transactional engines would simply ignore the START TRANSACTION/COMMIT statements. Since they aren't transactional, these engines are subject to leaving the database inconsistent. But, that is the price you pay for not using a tranactional backend.

MyISAM is really being deprecated by Oracle in MySQL and all development seems to be focused on INNODB. It always bothers me a bit that things like topic or reply counts can be wrong because there could have been some sort of DB/PHP failure between the insertion of the topic or reply and the updating of the forum or topic row.

I admit that database corruption due to not using transactions properly is probably very rare, but the fact that the Admin CP has tools to fix these situations seems to indicate that it does happen. It is even more important if the database is replicated.

Also, I like seeing the START/COMMIT points in the code. It naturally kind of groups the queries in transactions, which is the way we think of them anyway. So, it adds to the structure and clarity of the PHP code.