[14]Implement mySQL transactions

Locked
RoscoHead
Joomla! Explorer
Joomla! Explorer
Posts: 318
Joined: Mon Jul 30, 2007 11:01 pm
Location: Melbourne, Australia
Contact:

[14]Implement mySQL transactions

Post by RoscoHead » Sat Feb 16, 2008 12:21 am

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

User avatar
willebil
Joomla! Guru
Joomla! Guru
Posts: 764
Joined: Thu Aug 18, 2005 12:06 pm
Location: Netherlands

Re: Implement mySQL transactions

Post by willebil » Sat Feb 16, 2008 10:49 am

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
Joomla! Explorer
Joomla! Explorer
Posts: 318
Joined: Mon Jul 30, 2007 11:01 pm
Location: Melbourne, Australia
Contact:

Re: Implement mySQL transactions

Post by RoscoHead » Sat Feb 16, 2008 11:53 am

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
You do not have the required permissions to view the files attached to this post.

RoscoHead
Joomla! Explorer
Joomla! Explorer
Posts: 318
Joined: Mon Jul 30, 2007 11:01 pm
Location: Melbourne, Australia
Contact:

Re: Implement mySQL transactions

Post by RoscoHead » Sat Feb 16, 2008 9:55 pm

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:
  1. 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.

  2. 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.

  3. 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.

  4. Impact
    Would impact core and 3PD extensions that already call these JDatabase methods.
    Would impact installation code, to allow for different storage engines.

  5. Dependencies
    Internal - None.
    External - Needs to be conditional on mySQL version.

  6. References
    mySQL transactions: http://dev.mysql.com/doc/refman/4.1/en/ ... tions.html
ROSCO

User avatar
ircmaxell
Joomla! Ace
Joomla! Ace
Posts: 1926
Joined: Thu Nov 10, 2005 3:10 am
Location: New Jersey, USA
Contact:

Re: [14]Implement mySQL transactions

Post by ircmaxell » Mon Apr 07, 2008 9:18 pm

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...
Anthony Ferrara - Core Team - Development Coordinator - Bug Squad - JSST

http://moovum.com/ - The Bird is in the air! Get Mollom Anti-Spam on your Joomla! website with Moovur...
http://www.joomlaperformance.com For All Your Joomla Performance Needs

User avatar
spacemonkey
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 201
Joined: Fri Aug 12, 2005 7:50 pm
Location: Turin, Italy
Contact:

Re: [14]Implement mySQL transactions

Post by spacemonkey » Mon Apr 07, 2008 9:30 pm

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.


Locked

Return to “Under Review - Archived”