[14]Implement mySQL transactions

[RoscoHead]

This probably comes under the category of minor changes, but I'd like to see transactions implemented for the mySQL database objects. This will need to take into account the mySQL version, they were introduced in version 4 but I'm not sure exactly which sub-version.

It would need to implement the BeginTrans(), RollbackTrans(), and CommitTrans() functions for JDatabaseMySQL and JDatabaseMySQLi.

ROSCO

[willebil]

Hi Rosco,

Somehow I expected you to put several things into this forum. Great, but can you please read this post:

http://forum.joomla.org/viewtopic.php?f=500&t=265654

and adjust your proposal. Thank you very much in advance.

[RoscoHead]

Ummm, I don't seem to be able to find the "Edit post" button. Am I blind?

Joomla! Community Forum • View topic - Implement mySQL transactions.jpg

[RoscoHead]

Hmmm weird, this post does have an edit button. I thought maybe it disappeared when the post got a reply, but none of my posts in this forum are editable. Anyway, that's a side-track, here's a more flamboyant version of this white paper:

2. Introduction
   Many SQL transaction involve updates to multiple tables, for example a purchase needs to create an order and update stock levels. If an error occurs during the transaction, there needs to be a way to ensure all updates that may have already occurred are rolled back. Currently this must be done manually by the programmer, even though mySQL supports database transactions, and the JDatabase class has dummy methods to handle it.
   
   
3. Scope
   Implement transaction handling functions in JDatabase derived classes.
   
   This will need to take into account the mySQL version, they were introduced in version 4.0 and some versions of 3.23. It would also require the option of creating the Joomla! tables using a transaction-safe storage engine, such as InnoDB.
   
   
4. Technical Implementation
   Implement the BeginTrans(), RollbackTrans(), and CommitTrans() functions for JDatabaseMySQL and JDatabaseMySQLi. These classes already have a queryBatch() method, which surrounds the supplied query with START TRANSACTION and COMMIT, but this doesn't allow for SELECT or explicit ROLLBACK within the transaction, and it is not always easy to generate a single multiple table update query string using the MVC model.
   
   
5. Impact
   Would impact core and 3PD extensions that already call these JDatabase methods.
   Would impact installation code, to allow for different storage engines.
   
   
6. Dependencies
   Internal - None.
   External - Needs to be conditional on mySQL version.
   
   
7. References
   mySQL transactions: http://dev.mysql.com/doc/refman/4.1/en/ ... tions.html

ROSCO

[ircmaxell]

I am 100% whole heartedly against this. The problem comes in when you have a non-transactional environment (Such as MyISAM) in use. So now, as a dev, you can't rely on the transactions. So if you can't rely on the transactions, I think it's WORSE having transactions suported by J... Otherwise, we would need to check the backend, look it up to see if it supports transactions (easy for MySQL, but what about PG, or Oracle, or MSSQL, etc), and then "fake" a transaction (perhaps using temp tables)... It's got bad news written all over it IMHO...

[spacemonkey]

This is all IMHO, but I see a couple issues that make this a non-trivial proposal:

1) Breaks compatibility with non-transaction environments. This is bad because that is the default environment MySQL comes packaged in.

2) Requires refactoring of all SQL in the core package. Holy smokes, that's a lotta SQL dude.

However there are very easy ways to provide that functionality for stuff you write, assuming you are working with 1.5. At my present project we needed prepared statements, and just copied the mysqli.php driver as mysqlie.php and then added our methods that were needed. Would be just as easy to do the same for transaction support, and then all the code you write could reference that driver's API.

I think the closest you could come to what you want for the entire core codebase would be to setup auto_commit for your database, and set all your tables to be InnoDB or what not. However that doesn't make multiple updates atomic, you still have to hunt through the core to encapsulate them with BEGIN and COMMIT statements.

If you're building something that really needs transactions, I'd strongly suggest the hot-rodded database driver approach over trying to redo the entire core.