Transactions in MySql

Transactions are the block of statements that executes in a way that all statements execute or not one of them. Example will be as :

Step 1 : (creating a table)

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

Step 2 : Populating the accounts table

INSERT INTO accounts(number, balance) VALUES(12345, 1025.50);
INSERT INTO accounts(number, balance) VALUES(67890, 140.00);
SELECT * FROM accounts;

Step 3 : Using BEGIN

Transactions in MySQL start with either a BEGIN or a START TRANSACTION statement. Type in the commands in following Example to send a transaction to MySQL.

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

Step 3 : (Output)

+--------+---------+
| number | balance |
+--------+---------+
| 12345  | 1050.61 |
| 67890  | 140     |
+--------+---------+
2 rows in set (0.00 sec)

As you can see, the balance of account number 12345 was increased by 25.11 and is now 1050.61. You may also have noticed the COMMIT command in Step 3, which is explained next as :

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 to be merely temporary by MySQL. This feature gives you the opportunity to cancel a transaction by not sending a COMMIT but by 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. Check this out in action by entering the funds transfer transaction in following Example.

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

Once you have entered these lines, you should see the following result:

+--------+---------+
| number | balance |
+--------+---------+
| 12345  | 800.61  |
| 67890  | 390     |
+--------+---------+
2 rows in set (0.00 sec)

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. All you have to do is issue the commands in following Example.

Cancelling a transaction using ROLLBACK

ROLLBACK;
SELECT * FROM accounts;

You should now see the following output, showing that the two accounts have had their previous balances restored, due to the entire transaction being cancelled using the ROLLBACK command:

+--------+---------+
| number | balance |
+--------+---------+
| 12345  | 1050.61 |
| 67890  | 140     |
+--------+---------+
2 rows in set (0.00 sec)