Transactions in MySQL
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;
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;
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 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;
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';