Back to Top

MySQL Transaction tutorial for Beginners


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

CREATE TABLE product (
	sProductName varchar(255) NOT NULL,
	nQty int(5) NOT NULL,
	dDateAdded datetime NOT NULL,

Now ,create purchase_order table

CREATE TABLE purchase_order (
		nPurchaseID int(11) NOT NULL AUTO_INCREMENT,
		nProductID int(11) NOT NULL,
		nQty int(5) NOT NULL,
		PRIMARY KEY (nPurchaseID)

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:


Step 3:

Now, We will decrement the quantity of items in the product table

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.

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:


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:


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)

  1. Its great article..i was getting the same problem.oh wow!! its been solved now.
    Thanks BHUMI..

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

PHP 7 new Features at a Glance

Posted on 3 years ago


How to use jQuery UI tabs in a WordPress?

Posted on 6 years ago


Hello world

Updated 1 week ago

Nick Carter

In depth Understanding of Laravel Core

Updated 2 months ago


ECMAScript 6 New Features – 2

Updated 1 year ago


The Reader’s Poll – June 2015

Posted on 4 years ago


The Reader’s Poll – August 2014

Posted on 4 years ago


The Reader’s Poll – June 2014

Posted on 5 years ago


The Readers’ Poll – May 2014

Posted on 5 years ago


The Readers’ Poll – August 2013

Posted on 5 years ago