MySQL Transactions & Why They Can’t Be Emulated in PHP

Tweet

My recent article, “Top 10 MySQL Mistakes Made by PHP Developers” was far more controversial than I expected. Several of the more intriguing responses were from PHP coders who considered transactions to be an unnecessary overhead. With good-quality PHP code, they argued, you don’t need transactions or MySQL’s InnoDB storage engine.

It’s certainly true you don’t always need transactions. However, they are useful—and not just for banking or financial applications. A transaction should be used whenever you need to update two or more records and want to ensure they all succeed.

For example, assume we’re creating a simple shopping cart system. Three updates are required following a purchase:

  1. add the customer’s details to the database
  2. mark that the contents of a cart have been purchased
  3. reduce the quantity of items in stock accordingly

In SQL code, this could resolve to:

INSERT INTO customers (name,email,cartid) VALUES ('Customer1','customer@email.com',123);
UPDATE cart SET status='paid' WHERE id=123;
UPDATE product SET stock=stock-1 WHERE id=567;

We don’t want any of those updates to break. If the last statement failed, our stock levels would not be reduced accordingly and customers could order items which weren’t available.

Now I won’t doubt the quality of your PHP code. You can run those SQL updates and validate they’re successful. You could even query the existing data so you can revert back to the original record if a failure occurs.

Unfortunately, it’s not enough. Your code may be perfect, but the PHP interpreter certainly isn’t. Nor is the web server it’s running on. Nor is the OS you’re using (yes, even Linux). MySQL could also disappear. And let’s not forget the hardware … processors die. Hard disks crash. Memory fails. Servers explode. IT administrators kick networking cables. Power grids shut down. Hosting companies go bankrupt.

Putting it simply, you can never be certain a PHP program will run successfully and execute all SQL commands as expected. However, you can prevent your database being polluted in the event of catastrophic failure.

ACID is the Cure

Transactions offer Atomicity, Consistency, Isolation and Durability. In essence, a set of SQL updates wrapped in a transaction is guaranteed to be applied to the database when you COMMIT it. If you don’t commit, the updates never occur. You can also undo all the updates if an error is caught.

Contrary to popular opinion, transactions do not necessarily make your application slower. Updates are batched and applied at once so they can improve efficiency. In some cases, it may be reasonable to start a transaction at the beginning of your web page and run a COMMIT at the end.

InnoDB or Bust

MySQL’s default MyISAM storage engine does not support transactions, so it’s not an option. If you want to use transactions, ensure all your tables are defined as InnoDB. Even if you’re not using transactions now, you never know how your application will evolve — if in doubt, InnoDB is the best choice.

Using Transactions

Despite being shrouded in mystery, transactions are remarkably simple. The following SQL statement starts a transaction:

START TRANSACTION;

Alternatively, you can use:

SET autocommit=0;

By default, every SQL statement runs within it’s own transaction. Switching off autocommit cancels this action so all subsequent SQL updates form part of a single transaction.

note: Alternative PHP transaction methods

PHP offers a few other alternatives when starting a transaction. If you’re using the PDO library, you can execute the beginTransaction() method. For mysqli, pass false to the autocommit() method.

Now the transaction has been started, we can run any number of SQL queries, insertions, updates and deletions. None of the commands will permanently affect the database until we’re sure it’s correct. At that point, we run:

COMMIT;

Assuming that single command runs successfully, we can be sure our database was updated. Both the PHP PDO and mysqli libraries also provide commit() methods.

But what if something went wrong? Perhaps the customer was permitted to order an item which wasn’t in stock? We probably don’t want our stock level being set to -1, so we could cancel the whole transaction and alert an administrator.

If we don’t execute a COMMIT, none of the data updates are applied when the PHP page ends. However, since we’re all good programmers, we should specifically state we want to undo the transaction using:

ROLLBACK;

Alternatively, the PHP PDO and mysqli libraries provide rollback() methods.

That’s transactions in a nutshell. There are a few catches and exceptions, but we’ll save those for my next post. For now, have fun transactionalizing your applications!

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Miguel

    How to emulate transactions in PHP:

    alter table purchase add is_valid boolean default 0;

    Have your PHP code set it to 1 when you are satisfied that all the transaction’s dependencies are met.

    Treat entries with a 0 in that column as invalid. Periodically prune them, study them to find problems in your code, or whatever.

    Presto, article headline undermined.

    • http://www.optimalworks.net/ Craig Buckler

      You’re seriously going to do that for every table? Why make extra work for yourself? And how would it help you find or recover deleted rows?

      Presto, article comment undermined!

      • Miguel

        I’m not saying it’s always optimal, I just take issue with “can’t”, when that flatly isn’t the case.

        You can recover deleted rows by not deleting them, and instead marking them for later pruning.

        As for whether I’m going to do it for every table, no, not by hand. But then again I rarely create databases by hand; I define the schema in my objects and then the object from which they inherit creates the tables if necessary. It’s all automatic.

        Why would I do this? Because sometimes I can’t control which MySQL engine is being used, or the advantages of MyISAM outweigh the drawbacks. Or I need to be able to target databases without transaction support, like SQLite.

      • http://www.optimalworks.net/ Craig Buckler

        Actually, in this case “can’t” is absolutely correct. You can’t emulate transactions in PHP.

        I’d also question why you’d want to implement transaction-like behavior in a database/system which doesn’t support them? If you need transactions, why not use the right tool for the job? Don’t use a spanner to hammer it into shape.

        Finally, what advantages does MyISAM have over InnoDB? Speed is rarely better (if ever). FULLTEXT search is the only obvious benefit, but even that’s not very good and it’s more than outweighed by InnoDB’s foreign key and transaction support.

      • slawek22

        Well Craig, very strange comment. Foregin keys are foregin keys and full text search is full text search.
        How you can say FK’s outweight fulltext search. That’s completely DIFFERENT feature, you don’t get it? It’s like saying “cars have more features than bananas, so i’ll drive my car to work and then have it for lunch”.

        “can’t”… the word used by uneducated developers too much. Obviously behaviour of transaction CAN be emulated. It’s not “magic”. It is harder, may be not optimal and innoDB will probably be better for it in 99% of the cases.

        You just write “can’t” without even thinking about the problem. You don’t wonder why “can’t”, what should be changed so it was possible, you don’t test alternative solutions… and when someone gives it to you… you simply DENY it.

        But it CAN BE DONE… and i don’t know what you don’t get? Don’t just write “can’t” because _you_ don’t know how to do this.

        Emulating a transaction on myisam isn’t maybe the best idea but at least tells that the developer have a CLUE what is going inside the DB and what transactions are.

        If you like buzzwords so much – single query is ACID even on myisam (that’s why you can emulate noSQL or do caching on it… and it’s faster for Read access patterns than innoDB… what a suprise!).

        This kinda religious-fanatic mindset is very bad for a developer :) If Miguel is telling you that emulating transaction is possible, rather than deny the reality you should use the time to educate yourself, maybe you’ll find some usage for that knowledge in the future (ok i know you’ll say no, you’ll just read my post without understanding what i’m talking about and paste some holy-buzzword :) )

        >I’d also question why you’d want to implement
        >transaction-like behavior in a database/system which
        >doesn’t support them?

        mySQL haven’t always supported transactions. Then someone CAME and IMPLEMENTED it in innoDB plugin (myisam was NATIVE). HELLOOOOO!

        I can make ACID money-transfer system on myisam (actually on any database that doesn’t support transactions and single operation is ACID). Certainly innoDB would be better but it CAN be done. Wanna bet?

      • http://www.optimalworks.net/ Craig Buckler

        I wasn’t comparing FKs and FULLTEXT — I was saying that, given a choice, FKs should be a far higher priority.

        Transactions are not magic, but that doesn’t mean you can implement them using PHP alone. It cannot be done: that’s the whole point of this article!

        And yes, I’d quite happily accept your bet. I’ve given my reasons — where are yours?

      • slawek22

        Ok Craig, if i prove i’m right you’ll publish article saying (in title) “I was wrong, transactions can be emulated!” or “I was wrong, transactions are not the only option for data integrity”.

        I’ll send you code implementing shopping cart backend. 2 Methods => add goods, sell goods that’ll create user shipping details in the database + remove the required items from the inventory.

        $storage->addGoods(name, count)
        $storage->sellGoods(name, count, buyer_address)

        Proving that data (orders / item count) can be correct on databases that doesnt support transactions (eg. maria/aria/myisam). Eg. shopping cart items and order number will be correct.

        Shopping Order will contain 4 mysql queries, you can switch the queries order, remove any of them and the item numbers / orders to send will still be correct (so it’ll prove that transaction can be emulated if single query is ACID)

        Ok you agreen on that bet? If yes what i need to do if you prove me wrong? :)

      • http://www.lunadesign.org awasson

        Ok Craig, if i prove i’m right you’ll publish article saying (in title) “I was wrong, transactions can be emulated!” or “I was wrong, transactions are not the only option for data integrity”.
        I’ll send you code implementing shopping cart backend. 2 Methods => add goods, sell goods that’ll create user shipping details in the database + remove the required items from the inventory.

        Better yet slawek22, why don’t you write and submit your own article discussing it?

        Particularly, I’m interested in how your approach deal with interruptions or outages mid stream… Will the data get corrupted if the database stays up and http goes down. That sort of thing.

      • http://www.optimalworks.net/ Craig Buckler

        Ok Craig, if i prove i’m right you’ll publish article saying (in title) “I was wrong, transactions can be emulated!”

        Absolutely — I can’t wait to see your solution!

        If you can properly implement transactions for a combination of at least two INSERTs, UPDATEs or DELETEs in separate SQL commands, please publish it somewhere so I — and developers who are better than me — can see how you’ve solved a fundamental IT problem. Ideally, I’d like to see a generic solution which can be applied to any number of SQL actions.

        If it truly implements transactional behavior in PHP, I’ll endeavor to get your article reproduced on SitePoint so you’ll be paid for it too.

    • Anonymous

      wow, seriously, wow

    • Tim

      Surely it makes more sense to use a tool that was purpose built rather than write all that extra code just because you can? We’re long past the days where InnoDB isn’t widely available on hosted mysql servers.

      Miguel, how is what you’ve proposed easier than:

      try
      {
      $db->beginTransation();
      $db->exec($sql1);
      $db->exec($sql2);
      ….
      $db->commit();
      }
      catch (PDOException $ex)
      {
      $db->rollback();
      }

      It’s clean, it’s easy to read, and the vendor solves the problem much better than we can via user land code.

      • Anonymous

        Pro tip: rollbacks are automatically execute from PDO when an exception occurs, thus no need for the rollback statement in the catch block.

      • http://www.optimalworks.net/ Craig Buckler

        A rollback should occur whenever a COMMIT isn’t run, however, I still prefer to add the command. It’s cleaner and more obvious when reading the code.

    • http://www.lunadesign.org awasson

      Miguel, you’re assuming that yours is the only activity will take place on the DB server at a given time.

      What happens if someone else runs a query that modifies one of the tables sometime between the start and end of your code. There are no guarantees that your database activities will be completed accurately and as a result no guarantee that an error will be detected.

      That’s why I would look at my requirements and choose the best tool for the job rather than assume that I can code around any inadequacies.

    • Arkh

      - php code set the boolean to 0
      – php code do some things
      – the db server crash
      – php code tries to come back to the previous state. It fails
      – your data is in a f***ed up state
      Now, with db driver transactions :
      – php starts a transaction
      – php code do some things
      – the db server crash
      – php code tries to rollback. Does not work
      – the db server restarts, clean his own mess thanks to his history
      – your data is still coherent

      • Morgan

        I agree with you here. What’s also interesting about InnoDB, is that it is even transactional during crash recovery. If it crashes at this point, no changes are applied.

    • Morgan

      The problem with this idea, is that you can’t do it atomically and crash safe across multiple records. My transaction may modify 5 rows, and all should be changed at once or none at all.

      [theoretically] you could almost do it with two columns:
      trx_version, is_valid.

      trx_version is a monotonic counter. If there are any for a trx_version where is_valid =0, then all the other transactions need to be rolled back.

      This is a really stupid idea though. MyISAM also doesn’t protect against partially written rows, so the is_valid bit might have been set to 1, but some modifications may not have been made.

    • Roebie

      Reactions like yours only shows you’re not used to working with “serious” database applications. I’m talking about ‘100 million records per table’ databases. And I’m talking about databases where changes to one table trigger multiple changes to multiple other tables. While your approach is theoretically possible, in practice it is not. Transactions are the right way to go in a normalized database design.
      Also I have always found innodb to be slightly faster than myisam. Maybe not enough for small databases, but certainly noticeable for large databases.

  • MetalCat

    Let alone what happens when you’re in a multi server environment and the front end web machine(s) blow up half way though a fake-transaction.

    I’m all for using the transactions in MySQL, lots of people who are far smarter than me have spent the time making sure that the data will be left in a consistent state.

    If you “data layer” needs different behaviours, some being transactional and others high speed distributed NoSQL type things …….. then make it that, hybrid it, and then abstract that from the logic layer as needed.

    As for adding more SQL afterwards to “check the state” …… I’d expect to have my keyboard taken away from me and sent to the naughty corner ………

    • slawek22

      I don’t know why you can’t use myISAM or mySQL memory tables instead?

      eg. in couchDB all data is required to reside in memory anyway, and field names are taking space for each record. Record data overhead is enormous (data taking up 10-40x more space than in conventional databases)

      [speed] noSQL -> memory tables -> myisam -> innoDB [features]

      Beside in noSQL durability is not guaranteed (data will just go to dev/null if you’re out of memory).

      For a temporary processing (eg. building reports) you should use myISAM too… at least your tablespace won’t fragment, and your innoDB cache won’t get unusable keys in…

  • http://htmlblox.com samanime

    Great article.

    I’m actually just getting started on a project that could benefit from transactions, so it’s great timing. Not sure what engine we’re running, but I don’t think there is any reason we can’t switch if we aren’t already using InnoDB.

    You could technically emulate this in PHP, but like Craig said “seriously?” Why would you want to reinvent the wheel just so you could avoid learning more than the SELECT, UPDATE, INSERT and DELETE commands?

    You wouldn’t build a robot finger to push the power button on your television just so you wouldn’t have to learn where the power button is on your remote, would you?

    • slawek22

      That’s good point… people that know simple SELECT, UPDATE, INSERT and DELETE will be better using innoDB, all of the time.

  • http://www.reich-consulting.net/ coffee_ninja

    :) Nice Craig. And what about updates? Using Craig’s example from the article, what if the server flakes out between marking the cart “paid” and subtracting the quantity from stock? I don’t understand how flagging individual records as valid helps you in this situation, or most any situation for that matter. And as Craig said… given the fact that we have transactions, WHY BOTHER?

  • cmhatte

    Very Nice. I think of everything I’ve read today, this is the most useful piece of information. Projects I’ve worked on in the past could have benefited from this greatly and I’ll be sure to look use this for future ones.

  • Ren

    SQLite has transaction support.

    The comment suggesting that you can emulate transactions using some sort of flag is bogus.

    Rows that are newly inserted can be deleted before the transaction is complete. So no, you CANNOT emulate transactions in PHP.

  • PCSpectra

    Craig: Clearly as an article writer/contributor you have to chose your words carefully. Can’t is a strong word when we as developers are so pedantic about detail. :)
    That being said, in all practical situations, if transactions are required/desired it would make far more sense to find a host which supported InnoDB table type. The argument “you don’t know what RDBMS is going to be used” is acceptable but as a project developer you sometimes have to make executive decisions and limit the platforms your applications can run.
    Most users would take comfort in your decision to support a “true” transactional database if the information is mission critical, than something ‘fudged’ in PHP.
    Cheers,
    Alex

  • kaf

    Interesting article. I personally don’t have much need of transactions. It seems like people over-worrying and over-thinking the issue. And also applying it to cases where it is not needed. Unless you work for a bank or something you shouldn’t have too many cases where they are needed.
    For example, the article provided the case of a shopping cart where three queries would have to be done. The first was:

    “add the customer’s details to the database”

    I would certainly not want this operation reversed if the other queries failed. This will give me the customer details so I can get in touch with them later. My cms will have this order flagged as incomplete or with error. We need to follow up with the customer!

    Sometimes incomplete data is desired so that you know exactly where the problem was. eg If the customer details and order were saved but the order not marked as purchased then there may have been a problem with your payment gateway.
    It is useful to think about the steps involved in your checkout (or any app) and use the saved data to determine the status of the order.

    Then again it is certainly useful to know about transactions for those rare occasions where it is necessary. I haven’t had to use a transaction in years, but they certainly have their place. That place just doesn’t have to be everywhere.

    • http://www.optimalworks.net/ Craig Buckler

      Sometimes incomplete data is desired

      …but it doesn’t need to be stored in your database. In the event of a failure, you rarely want to store orphaned records or anything which could affect the normal operation of your system. You need to investigate the issue — it could be a serious error or a hacking attempt.

      A better alternative would be to log the error and customer details in a file then alert the administrator.

      • kaf

        Logging the customer details in a file seems like unnecessary work to me. We have a database, it is great at logging data, why not use it? Having incomplete orders (or whatever) in your db does not mean “orphaned records” its just that the state of the order at that particular time is incomplete. And we can use that. “affect the normal operation of your system” ? that should never be the case if you designed your app well. Which I’m sure you would.

        And McBenny (below) I agree with you, it doesn’t need to be sensitive data. Whatever works best as a solution for the problem in front of you. You’re the developer, its your prerogative.

      • Kyu

        That’s funny, since you’re the one who considers emulating transactions with php as useless additional work (which i partly agree with btw) and then go on to advocate logging customer details (which at that point would be complete correct data, not an orphanned record) into a file and alerting the administrator. who will then do what? copy the perfectly ok customer data into your table? nice one on useless work overhead.

      • http://www.optimalworks.net/ Craig Buckler

        @Kyu
        It’ll depend on your system and what you want to achieve. In this particular case, many shops will add the customer record before the financial transaction occurs. I’ve only done it in one stage to illustrate transactions.

        However, the reason for logging and alerting an administrator remains valid. If the failure has been caused by a MySQL crash, you’ve got no other option: you need to record the details somewhere so they can be copied into the database following a successful restore. That’s hardly a useless overhead: the alternative is to lose the data forever.

    • http://www.yacare.fr McBenny

      You don’t need to work on sensitive data to need transactions :
      I developped a small cms for my personal needs and I deal with multilingual contents in such way that elements (pages, pieces of news…) are stored in a table and the text contents are in another table. This way I can share non-textual elements through all languages and separate things that need to be separated. If something fails between the storage operation of the element and its contents, it creates a real problem. In this simple case, transactions are very useful.

  • Cookies

    Indeed transaction in MySQL is handy, why code a couple of conditional procedure, when it is already been created to solve that problem.

    But again its up to the coder, the author point is how to make your life easy by using MySQL transaction feature, I myself use that feature especially in important business logic.

  • basvanmeurs

    That was a great article! I never realised that I could use transactions so easily and will surely make use of them in the future, now that I know how easy it is. On the other hand there are still situations in practice that DB transactions alone are not enough.

    For instance when we want to create a ‘photo’ and use the file system for storage and the database for saving information about the photo. A user can enter some information and upload a picture. In PHP code, we check the validity before saving, but we can never be sure that the saving of the file or that database record will work (because of disk space, for instance). So in practice we still need some coding to check if a photo record has a file associated with it on disk, and otherwise ignore it.

    Reminds me of another question I’ve always had: should you save (uploaded) files on disk or in a database?

  • Michael

    I couldn’t agree more with you on this topic Craig.
    Usually, ecommerce websites have the option for card payment straight away after you checkout the product. I cannot imagine how I’d feel personally if one of my queries failed, especially the one updating quantity of the product after the sale. The thought of displaying a product as in stock when in reality it’s out of stock is so terrifying. Imagine such a thing happening while you’re shopping for a brand new monitor – you buy it, the site says it’s arriving but then you get an email or a call explaining that there was an error with the system and that the monitor isn’t available. I won’t mention the hassle of actually checking if your money came back or not and what a nightmare it is for the shop owner.
    And all of it could have been prevented by a simple block of code, using simple and open-source database. I won’t even try to argue whether transactions are possible to emulate in php, because everything is possible. Question is – why bother reinventing the wheel? Why bother with “pruning” techniques and so on when everything can be done by using transactions.

    Nicely written article, I liked it.

  • basvanmeurs

    As a matter of fact, MySQL 5.5 was released today, which has InnoDB storage as the default engine instead of MyIsam, and has a hughe performance increase in terms of transactions over connections!

  • MatSays

    Absolutely you should be using transactions for the same reason one should use stored procedures on MsSQL. And especially in an e-Comm environment where the standard schema would have an orders and an orders_items table structure – both parts need to be effectuated on the return from the payment gateway. But there’s a ton of other places it’s both necessary and efficient.

    By the same token, however, you should have script (PHP) to validate each step and provide catch blocks when items manage to not complete process.

    • http://www.optimalworks.net/ Craig Buckler

      Unfortunately, many open source shopping cart systems still use MyISAM without transactions or data integrity checks. I always find that a little worrying.

  • Roebie

    @ Craig
    I know every article writer likes his/her article to cause some controverse. But it in this case I wouldn’t call it that. You had one commentor contesting your opinion and lots of commentors telling him he was wrong. And that’s it. Nothing more.
    Nevertheless, your point in the article should be taken seriously. One should never interact with a non-transactional database for anything more serious that registering how much breaths taken since getting up.
    I even think I heard that the latest release of mysql sets innodb as the default table type instead of myisam. That being said I must add that I don’t like mysql at all. PostgreSQL is (imho) far better. As my company deals with rather large databases (ie several 100 million records per table and self-joins on such tables) performance is quite an important issue. Regular perfomance test show time and time again that mysql is not a serious competitor (as isn’t mssql).

    • http://www.optimalworks.net/ Craig Buckler

      Hi Roebie,

      There’s one “transactions aren’t necessary” comment on this page, but at least 5 on the previous article. Also, take a look at the current SitePoint poll: 40% of coders have never used a transaction or don’t know what they are.

      MyISAM’s simplicity was one reason it became popular. But it also hid some of the fundamental database benefits from developers who coded around it’s shortcomings (and continue to do so).

    • Michael

      This is a little bit offtopic but I can’t bring myself not to ask – do you have some tests you made with MySQL not operating fast enough with so many records?
      By chance, my company deals also with quite large databases and we’re using MySQL and we haven’t had any performance issues causing us to rethink our choice of RDBMS.
      I’m not a “fanboy”, I’m always interested in the best tool for the job so my question is purely from curiosity point of view.

    • slawek22

      >You had one commentor contesting your opinion and lots of
      >commentors telling him he was wrong. And that’s it.

      If you say “inno is good for everything and myisam for nothing” + “you can’t emulate transactions”… in an example as simple as selling an item from shopping cart – it unfortunately prooves how bad the understanding of databases, concurrency and data consistency really is.

      There are many things that shouldn’t be done on production enviroment… but if you don’t know how to do them you don’t really understand how the thing really works and what it does.

      If you understand what needs to be done to emulate a transaction on myisam then you really know what could go wrong in the given set of queries. That you should use CASCADE for FK’s, for example. Like some of innoDB fanboys (:D) pointed out, you’re not using the DB alone. Transactions alone won’t quarantee ANY integrity.

      • http://www.optimalworks.net/ Craig Buckler

        No, transactions don’t necessarily provide integrity, but FKs do. They’re available in InnoDB — not in MyISAM.

        So, what point are you making?

  • http://www.cemerson.co.uk Stormrider

    Good article. I’m amazed there are still some people who believe you can simulate transactions in PHP. You simply can’t meet the ACID requirements in PHP, it’s logically impossible!

    A point with the poll though… ‘Never used a Transaction’ could just mean they haven’t needed to yet, but doesn’t mean they wouldn’t if the need arose! That would be quite rare though. It’s those who don’t know what it is who are committing the biggest travesty!

  • http://www.cemerson.co.uk Stormrider

    I have a question though now….

    Is it possible to use SELECT statements inside a transaction? What would it return – the state of the mid-transaction, or the state before the transaction started?

    Consider the following hypothetical example:

    query(“START TRANSACTION;”);

    $dbConn->query(“UPDATE accounts SET amount = amount – 10 WHERE account_holder = 1″);

    $dbResults = $dbConn->query(“SELECT amount FROM accounts WHERE account_holder = 1″);

    $arrResult = $dbResults->fetch_assoc();

    if ($arrResult['amount'] query(“ROLLBACK;”);
    } else {
    $dbConn->query(“UPDATE accounts SET amount = amount – 10 WHERE account_holder = 1″);
    $dbConn->query(“COMMIT;”);
    }//if
    ?>

    Would this work, or would the account balance in the query be the value it was before the transaction started? How does the SELECT manage to read the state of the table mid-transaction, but also guarantee that noone else can change the table values in the meantime (eg someone else taking out 100 from the account after we have done the SELECT check)

    • http://www.cemerson.co.uk Stormrider

      My code appears to have been corrupted in the code above, grr

    • basvanmeurs

      Good point. And what about insert ids? What if I need the insert id of the first query in a transaction in one of the next queries in the transaction?

      I still doubt if it’s always possible to use transactions.

      • slawek22

        You can always assign insert_id to user defined variable, same thing can be used to do atomic update/select (another user can’t interfere with result if you want to update one field and select another, from the same row)

        UPDATE tab
        WHERE … AND (@x:=field) IS NOT NULL
        SELECT @x

  • davidcroda

    I am not arguing that InnoDB shouldn’t be used for its transaction support if necessary.

    But there are downsides to InnoDB.

    I believe recovering from a database crash is MUCH more difficult then MyISAM. You also can’t backup the database with a nice quick mysqlhotcopy.

    But who cares about this stuff anyway. I want to read about the new Falcon storage engine in MySQL 6

    • http://www.optimalworks.net/ Craig Buckler

      In theory, InnoDB should recover from a database crash better than MyISAM? It’s designed to handle it.

      You can still use mysqldump to backup databases with InnoDB tables. It’s not as fast, but it’s reliable. There will doubtless be many commercial solutions too.

  • Jonathon Hibbard

    Once again, the author is blind to the fact that InnoDB is “not” the best answer by default. MyISAM is. I am stating this from numerous articles, proof of concepts, and personal experience. InnoDB is great for “very specific needs”. But, again, you do NOT just throw a table into InnoDB simply because you want transaction support.

    • http://www.optimalworks.net/ Craig Buckler

      I disagree that MyISAM is the best default choice. So does Oracle and the MySQL team!

      If you need transactions, how else would you implement them without an InnoDB table? It’s not just transactions either: foreign keys are essential for any relational data.

      • slawek22

        Well the original creator of mySQL took maria (variation of myISAM) to be default engine for their product. You create an impression that “the whole world is switching” or should switch ASAP, that’s simply untrue :)

        You may say that myISAM days are outnumbered but the fact is that it’s much simpler => easier to manage (from the mysql dev team perspective) and it plays better with SSD disks (it doesn’t reallocate data based on PRI key order).

        Faster for reads (read access patterns), backup… and the features are adequate for most of small websites. Of course innoDB won’t hurt small websites… neither it’ll help.

      • http://www.optimalworks.net/ Craig Buckler

        Maria is a fork of MySQL — not MyISAM. Maria uses the XtraDB engine rather than InnoDB, but they’re fully compatible (InnoDB is an Oracle project). MyISAM remains the default, but that’s because Maria 5.1 is based on MySQL 5.1. It may change in future versions.

        I’ve never said the whole world was switching to InnoDB or MyISAM should be scrapped? But, for the majority of applications, InnoDB remains a better choice. The difference in read speeds is almost negligible in the latest editions.

        I am beginning to wonder why you’re so anti-InnoDB? MyISAM is simpler but, if you really want simplicity, why not use SQLite?

      • slawek22

        Maria is default engine of MariaDB (renamed Aria, as it was causing confusion).

        We’ll im not pro or anti. But if you say “innoDB” is best because oracle / mysql team switched you just fail to give the other part of the story.

        The whole story is:
        1. Oracle switched
        2. Oracle’s mysql team switched (that’s logic:)
        3. MariaDB have not switched (default engine is maria/aria)
        4. They state that Aria based on myisam will be default (they’ll add full ACID/Transaction support)

        Ah for the new reply: for MariaDB the default engine is myisam fork named Maria, later renamed Aria to avoid confusion.

        Cite (from MariaDB project FAQ):

        “”Aria is a new storage engine for MySQL® and MariaDB which is developed with the goal of being the default transactional AND non-transactional storage engine for MariaDB and MySQL.””

        “”The engine was originally called Maria after Monty’s younger daughter. Monty named MySQL after his first child My. His second child, Max, gave his name to MaxDB and the MySQL-Max distributions.

        Having both MariaDB the database server and Maria the storage engine proved confusing””

        http://kb.askmonty.org/v/aria-faq

      • slawek22

        LOL SQlite? That doesn’t even support concurrent read/writes and have no real server daemon? And you must re-open files on every page load… that’s good for client side apps, not the web… are you serious? :)

        >if you really want simplicity, why not use SQLite?
        Why you’re so anti-myisam :P

    • Ren

      InnoDB is a far better default choice than MyISAM. Period.

      Not only does InnoDB have transaction support, but it also has RI.

      The only reason as far as I can see to use MyISAM is for full text indexing, but arguably that could be done better with other tools.

    • http://www.lunadesign.org awasson

      …you do NOT just throw a table into InnoDB simply because you want transaction support.

      Actually yes you do use InnoDB precisely because you want transaction support.

      The days of MyISAM are becoming numbered as a preferred database engine choice and this is being reinforced by MySQL’s decision to ship with InnoDB as the default engine. Read the introduction notes for MySQL 5.5 on the MySQL site about “Improved performance and Scalability“.

  • agendogget

    i think not all of php programmers use RDBMS. this is a great article, thanks for sharing

  • Ivo

    I’m not sure if my post was submitted (I lost connection) so I’m trying again.
    There was a question from #basvanmeurs that didn’t get an answer from anyone and I’m curious about it too.
    How can you get the inserted id (which you can get with mysql_insert_id() ) from the first query in a transaction if you want to use it in some subsequent query in same transaction?
    e.g.
    start transaction
    insert into customers (id, name) values (NULL, ‘george’);
    insert into sells (id, customer_id) values (NULL, mysql_insert_id()?)

    • Arkh

      Just use PDO instead of the mysql_* functions.
      SQL to create the test table :

      CREATE TABLE `test`.`test` (
      `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
      `name` VARCHAR( 255 ) NOT NULL
      ) ENGINE = InnoDB;

      Php to insert a row, then a second row which name contain the first one’s ID.

      // Connect to the mysql server
      $db = new PDO(‘mysql:host=127.0.0.1;dbname=test;’, $user, $pass);
      // Set PDO to raise exceptions when a query fails
      $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      $db->beginTransaction();
      $stmt = $db->prepare(‘INSERT INTO test (name) VALUES(:name)’);
      // Insert first row
      $stmt->execute(array(‘:name’=>’first data’));
      // Get the inserted row’s ID
      $lastId = $db->lastInsertId();
      // Insert the second row
      $stmt->execute(array(‘:name’ => ‘First data ID was : ‘.$lastId));
      $db->commit();

    • Michael

      You get it the same way as if there was no transaction involved.
      Wrap your code with “BEGIN WORK” and “COMMIT” statements and the part between it stays the same. You’ll get appropriate return values from mysql_insert_id().

  • http://www.cemerson.co.uk Stormrider

    What about from SELECTing as well?