Home News Feeds Planet MySQL
Planet MySQL
Planet MySQL -

  • MySQL: InnoDB transactions, a practical example
    Only the MySQL storage engine InnoDB supports transactions. A major benefit of transactions is that it’s possible to roll back changes until a transaction is committed. This example show you how to write multi-statement transaction, how to perform a rollback in case of errors and how to commit a transaction. Starting transactions In autocommit mode (the InnoDB default behavior), MySQL does a commit after each SQL statement that was executed without errors. That makes it impossible to roll back an committed statement. When we start a new transaction, the autocommit mode is disabled implicitly by MySQL and enabled again once we commit the transaction: START TRANSACTION; -- ... -- add all insert & update statements here -- to save the received order -- ... -- on success: COMMIT; -- in case of failure: ROLLBACK; Adding savepoints for partial rollbacks InnoDB supports a feature called savepoints. A savepoints makes partial rollbacks of a transaction possible: START TRANSACTION; -- -- add all insert & update statements here -- to save the received order -- -- in case something went wrong: ROLLBACK; -- otherwise set a savepoint called "order_added" SAVEPOINT order_added; -- -- add a success message somewhere: -- insert into logging values (123, 'no errors occured') -- -- -- execute other statements... -- in case of problems, rollback to savepoint: -- ROLLBACK TO SAVEPOINT order_added; -- -- the success message is not logged, -- but the order was saved -- -- on success: COMMIT; Implicit commits / rollbacks When writing InnoDB transactions, it’s important to keep in mind that there are several statements that cause InnoDB to commit the current transaction. Afterwards, a new transaction is started automatically. Here are a few of them, for a more complete list see the “additional information” section below: ALTER TABLE CREATE INDEX DROP … TRUNCATE … LOCK / UNLOCK Additionally important: If you don’t commit a transaction that you have started using START TRANSACTION before you close the connection, InnoDB performs a implicit rollback. Additional information Since this example is just a introduction, make sure to take a look at the useful information about InnoDB and transactions in the MySQL documentation: InnoDB: transaction model InnoDB: implicit transaction commit / rollback InnoDB: statements that cause an implicit commit InnoDB: available isolation levels

  • MySQL Outer Join: ON vs. WHERE condition

  • MySQL Left Join – ON vs. WHERE condition
    In MySQL it’s possible to filter the result set of a join operation using a WHERE condition. If there is more than one condition, the Another way is to specify an ON condition (which can be extended using AND as well).  I stumbled upon a very interesting link that explains a few differences

  • learn mysql join

  • Testing MySQL repository packages: how we make sure they work for you
    Around nine months ago, we launched the MySQL yum repositories for Enterprise Linux and Fedora, followed by apt repos for Debian and Ubuntu back in May. We’re extremely happy that the repos have proved to be a big hit with the community: the monthly number of downloads hit 100K recently and it is still growing […]



Copyright © 2001 - 2013 K-Factor Technologies, Inc.

Site Meter

a href=