Transactions in MySQL


Share

In some applications, it is vitally important that a sequence of queries runs in the correct order and that every single query successfully completes. For example, suppose that you are creating a sequence of queries to transfer funds from one bank account to another. You would not want either of the following events to occur:

1. You add the funds to the second account, but when you try to subtract them from the first account the update fails, and now both accounts have the funds. 2. You subtract the funds from the first bank account, but the update request to add them to the second account fails, and the funds have now disappeared into thin air.

Transaction Storage Engines


In order to be able to use MySQL’s transaction facility, you have to be using MySQL’s InnoDB storage engine. This is easy to do, as it’s simply another parameter that you use when creating a table.

    CREATE TABLE accounts (
    number INT, balance FLOAT, PRIMARY KEY(number) 
    ) ENGINE InnoDB; 
    DESCRIBE accounts;



Using BEGIN


Transactions in MySQL start with either a BEGIN or a START TRANSACTION statement


    BEGIN; 
    UPDATE accounts SET balance=balance+25.11 WHERE number=12345; 
    COMMIT; 
    SELECT * FROM accounts;



Using COMMIT


When you are satisfied that a series of queries in a transaction has successfully completed, issue a COMMIT command to commit all the changes to the database. Until a COMMIT is received, all the changes you make are considered by MySQL to be merely temporary. This feature gives you the opportunity to cancel a transaction by not sending a COMMIT, but issuing a ROLLBACK command instead.



Using ROLLBACK


Using the ROLLBACK command, you can tell MySQL to forget all the queries made since the start of a transaction and to end the transaction


     BEGIN; 
     UPDATE accounts SET balance=balance-250 WHERE number=12345;
     UPDATE accounts SET balance=balance+250 WHERE number=67890;
     SELECT * FROM accounts;


The first bank account now has a value that is 250 less than before, and the second has been incremented by 250—you have transferred a value of 250 between them. But let’s assume that something went wrong and you wish to undo this transaction But let’s assume that something went wrong and you wish to undo this transaction. All you have to do is issue the commands in below example:


Example:-Canceling a transaction using ROLLBACK

    ROLLBACK;
    SELECT * FROM accounts;



Using EXPLAIN


MySQL comes with a powerful tool for investigating how the queries you issue to it are interpreted. Using EXPLAIN, you can get a snapshot of any query to find out whether you could issue it in a better or more efficient way. Below Example shows how to use it with the accounts table you created earlier.


      EXPLAIN SELECT * FROM accounts WHERE number='12345';