Results 1 to 17 of 17
  1. #1

    PHP/mySQL Atomic Transactions (commit, rollback, etc)

    I have a series of 10 individual INSERT queries where ALL need to be successful. If even 1 query fails, I want to abandon the code and rollback the queries that already ran.

    I think the technical term is called an "atomic transaction". Does anyone know of a good tutorial on how to try this out? I've heard of mySQL commit and rollback, is that what I should use in this case?

    I'm using PHP/mySQL.

    Thanks.
    TextAdMarket - http://www.TextAdMarket.com
    Supply & Demand Advertising

  2. #2
    Join Date
    Mar 2007
    Location
    Phoenix, AZ
    Posts
    132
    MySQL does support atomic transaction with InnoDB. Checkout this basic tutorial to get started
    http://mysqldump.azundris.com/archiv...-Tutorial.html
    Cheers,
    Sivanandhan, P. (a.k.a. apsivam)
    My Blog Site

  3. #3
    This won't work with MyISAM? Is there any other way to go about solving this problem?
    TextAdMarket - http://www.TextAdMarket.com
    Supply & Demand Advertising

  4. #4
    Join Date
    Mar 2007
    Location
    Phoenix, AZ
    Posts
    132
    No. MyISAM doesn't support atomic operations or transactions.
    Cheers,
    Sivanandhan, P. (a.k.a. apsivam)
    My Blog Site

  5. #5
    I'm not exactly familiar with InnoDB. If I switch my MyISAM tables over to it, should they function the same? Will this affect my code at all?
    TextAdMarket - http://www.TextAdMarket.com
    Supply & Demand Advertising

  6. #6
    Join Date
    Sep 2004
    Location
    Flint, Michigan
    Posts
    5,765
    Quote Originally Posted by Goldfiles View Post
    I'm not exactly familiar with InnoDB. If I switch my MyISAM tables over to it, should they function the same? Will this affect my code at all?
    I am not an expert on all things MySQL but I believe everything should work with the exception of full text searching. Switching to InnoDB will also allow you to force relations between tables.

    Here's some conversion information from MySQL: http://dev.mysql.com/doc/refman/5.0/...to-innodb.html
    Mike from Zoodia.com
    Professional web design and development services.
    In need of a fresh hosting design? See what premade designs we have in stock!
    Web design tips, tricks, and more at MichaelPruitt.com

  7. #7
    Join Date
    Oct 2005
    Location
    UK
    Posts
    552
    If you want to take advantage of the transaction functions for MySQL while working with PHP you will need to use the PDO_MySQL or MySQLi extensions.

    The MySQLi section on rollbacks provides some information on how to go about using the rollback function, as well as the commit function.

    Best Regards,

  8. #8
    I'm too nervous about switching from MyISAM to Innodb. I'll think of another way around this problem. Maybe a bunch of if/else statements for each of the queries...with manually coded rollbacks...yikes
    TextAdMarket - http://www.TextAdMarket.com
    Supply & Demand Advertising

  9. #9
    Join Date
    Jul 2003
    Location
    Olean, NY
    Posts
    143
    Just bite the bullet and switch to Innodb. It is slower, but it has a bunch more features that are quite useful for more complicated databases.

    If you absolutely must use MyISAM, you can use it through adodb and that'll support transactions for you.

    Also, have a look at my blog entry touching some of the issues we're talking about, at http://erek.blumenthals.com/blog/200...tabase-design/

    Cheers,
    Erek
    System administration, application development, and project management.
    http://erek.blumenthals.com/blog/

  10. #10
    I found an example on http://www.devarticles.com/c/a/MySQL...L-4.0-and-PHP/

    Transactions work with MyISAM...not sure how or why.

    I'm not familiar with this type of coding. What is the difference between @mysql_query vs. mysql_query the usual way without the @ sign?
    Last edited by Goldfiles; 02-21-2008 at 01:11 AM.
    TextAdMarket - http://www.TextAdMarket.com
    Supply & Demand Advertising

  11. #11
    ignore that post. the example I found was something different.

    I'll try switching over to innodb where I can. I'm hoping it doesn't affect any of the other code.
    TextAdMarket - http://www.TextAdMarket.com
    Supply & Demand Advertising

  12. #12
    Join Date
    Feb 2003
    Location
    AR
    Posts
    2,381
    @ simply suppresses any error messages.

    Kevin

  13. #13
    Join Date
    Jul 2003
    Location
    Olean, NY
    Posts
    143
    Quote Originally Posted by Goldfiles View Post
    Transactions work with MyISAM...not sure how or why.
    Don't be so sure about that. You can run a mysql query that affects a myisam table while in a transaction, but the changes are committed immediately and aren't rolled back with a rollback. Only innodb and berkeleyDB tables are actually part of the transacation.

    You may be able to accomplish some of what you're working on with careful condition-checking to ensure that every insert will succeed, and table locking so you don't have any reads while you're writing. This wouldn't protect you against a thread dying while you're writing to the table though.

    Have a look at http://dev.mysql.com/doc/refman/5.0/...nsactions.html for a more in-depth discussion.

    Cheers,
    Erek
    System administration, application development, and project management.
    http://erek.blumenthals.com/blog/

  14. #14
    Join Date
    Sep 2004
    Location
    Flint, Michigan
    Posts
    5,765
    Quote Originally Posted by Goldfiles View Post
    ignore that post. the example I found was something different.

    I'll try switching over to innodb where I can. I'm hoping it doesn't affect any of the other code.
    This is where a beta as well as a production site works wonders I say bite the bullet and I'm sure it will all be fine.
    Mike from Zoodia.com
    Professional web design and development services.
    In need of a fresh hosting design? See what premade designs we have in stock!
    Web design tips, tricks, and more at MichaelPruitt.com

  15. #15
    Join Date
    Feb 2005
    Location
    Poland
    Posts
    248
    like other said, im sure you can convert to innoDb if you dont use fulltext indexes - or even better swich to postgresql from mysql (its far superior database and its faster and scales better), you will soon discover that most of your db problems are gone
    http://www.linkedin.com/in/marcinlulek - my linkedIn profile
    webdeveloper for hire - XHTML, CSS3 PYTHON ,PostgreSQL, Ajax & Javascript, I build apps with Pyramid/Pylons Web frameworks using dojo toolkit for js work

  16. #16
    Join Date
    Apr 2002
    Location
    Hollywood, CA
    Posts
    3,046
    Quote Originally Posted by ergo View Post
    like other said, im sure you can convert to innoDb if you dont use fulltext indexes - or even better swich to postgresql from mysql (its far superior database and its faster and scales better), you will soon discover that most of your db problems are gone
    I'd be interested in seeing some recent benchmarks that pit MySQL 5.X against against PGSQL 8.X. I'm not saying your wrong in any capasity, I just see a lot of conflicting information.

    According to spec.org:

    The MySQL and Sun combination attained a result of 712.87 SPECjAppServer2004 [email protected] running a 64-bit version of MySQL 5.0 and SJSAS 9.0 on Sun Microsystems' Sun Fire(TM) X4100 servers powered by Dual-Core AMD Opteron(TM) processors(1). The result demonstrates superb scalability of the whole solution, as compared to the previous result of 266 SPECjAppServer2004 [email protected] that was achieved with Single-Core AMD Opteron processors (2). This solution also demonstrated the best database performance, measured in SPECjAppServer2004 [email protected] per database core (SPECjAppServer2004 [email protected] /DB core), of any competitive submission using less than 20 total cores in database and application tiers. MySQL's SPECjAppServer2004 [email protected] /DB core metric surpassed an Oracle-powered result by over 30 percent (3).

  17. #17
    Join Date
    Feb 2005
    Location
    Poland
    Posts
    248
    http://tweakers.net/reviews/657/6
    http://tweakers.net/reviews/657/5/da...-pagina-5.html

    less scientific.


    more official:

    http://www.spec.org/jAppServer2004/r...606-00065.html

    No more "slow elephant." For ages a reputation of sluggish performance has dogged the PostgreSQL project, due to both unfavorable comparisons with MySQL back in 1998 and due to our ongoing lack of auto-configuration (yes, yes, I'm working on it!). This publication shows that a properly tuned PostgreSQL is not only as fast or faster than MySQL, but almost as fast as Oracle (since the hardware platforms are different, it's hard to compare directly). This is something we've been saying for the last 2 years, and now we can prove it.

    Why pay more? As I said, almost as fast as Oracle. While the list of Spec publications on affordable commodity hardware is sparse, there are some. For example, the 874 [email protected] on Oracle 10+Itanium+HP-UX. That's less than 15% faster than our PostgreSQL publication.

    (And before you ask, all benchmark runs were extensively optimized by the Sun performance team, with the help of performance experts from the databases represented.)

    and here :

    http://7thguard.net/news.php?id=5646


    In my opinion mysql is a very good database for simple data fetching and stuff like that , but if you add few joins, things start to shift considerably.

    Not to mention innoDb was bought by oracle, and we STILL cant use fulltext indexing WITH transactions.
    http://www.linkedin.com/in/marcinlulek - my linkedIn profile
    webdeveloper for hire - XHTML, CSS3 PYTHON ,PostgreSQL, Ajax & Javascript, I build apps with Pyramid/Pylons Web frameworks using dojo toolkit for js work

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •