MySQL is a relational database, provides many essential features and some features are very useful.In this article, I am going to explain one of the most useful features in MySQL.
What Are Transactions?
The transaction is a database management system (DBMS) feature which helps for data recovery on internal errors.Database transactions are sets of queries that must be executed so that if one of the query execution gets fails, your query execution will return back completely.The transaction is a mechanism which allows to interpret the multiple changes to the database with a single operation.It will accept all the changes or reject.
For now, Let have a set of three queries and the second query is depends on the result of the first query and like this third query depends on the result of the second query. so If the second query fails, you need to have a way to negate the results of the first query similarly, if the third query fails you need to deny the results of the first and second queries as well.
With the use of Transaction process in the database, you can be confirmed with the data integrity with the data stored in the database.
Generally, transactions become more useful in situations where you want an all-or-nothing commit on a group of inserts.
Here, I will describe the process of using transactions with PHP functions.
Basic Syntax of Transaction
Let’s see Basic syntax about transactions with MySQL:
- COMMIT: COMMIT command appear at the end of the sequence of queries in your transaction and is issued only if all the required queries have executed successfully.
- ROLLBACK: ROLLBACK command is used when one or more of the sequence of queries in your transaction fails and want to resets the affected tables to their pre-transaction state.
NOTE: MySQL was not a transactional database, but now InnoDB offers foreign key constraints, multi-statement transactions with ROLLBACK support also its support locking mechanism so have more concurrent SELECT queries than MyISAM
The following example creates a table with transaction:
Step 1:
First of all, create one table product
1 2 3 4 5 6 7 8 9 | CREATE TABLE product ( nProductID int(11) NOT NULL AUTO_INCREMENT, sProductName varchar(255) NOT NULL, nQty int(5) NOT NULL, dDateAdded datetime NOT NULL, PRIMARY KEY (nProductID) |
Now ,create purchase_order table
1 2 3 4 5 6 7 8 | CREATE TABLE purchase_order ( nPurchaseID int(11) NOT NULL AUTO_INCREMENT, nProductID int(11) NOT NULL, nQty int(5) NOT NULL, PRIMARY KEY (nPurchaseID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
Step 2:
The user completes an online registration and attempts to pay for the purchase order so apply a BEGIN command for the transaction that would be part of the checkout script:
1 2 3 | BEGIN; |
Step 3:
Now, We will decrement the quantity of items in the product table
1 2 3 | UPDATE product SET nQty = nQty-product_purchase_qty WHERE id = 1; // replace id with your desire id and replace product_purchase_qty with desire quantity |
Step 4:
Insert a data into the purchase_order table.This inserts the data and then starts a transaction in which the data is deleted and finally the transaction is rolled back if any error occur.
1 2 3 | INSERT INTO purchase_order (nProductID, nQty) VALUES (1, product_purchase_qty); |
Step 5:
If Inserting the record fails, issue a ROLLBACK command to reset the available quantity of the items:
1 2 3 | ROLLBACK; |
Step 6:
If adding the records succeeds and the subsequent charging of a credit card or other payment method also succeeds, use a COMMIT command to ensure all the changes are stored and the transaction ends:
1 2 3 | COMMIT; |
Here are few other articles which you might like to read, MySQL FLUSH Commands and TimeStamp Type in MySQL.
Here I am ending this article. Have you used transaction in your application? How is it helpful to you? Share your experience with me via the comment section.
Comments (1)