Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

MySQL transaction: BEGIN, COMMIT and ROLLBACK – Querychat

When you need to ensure an “all or none” execution while executing a set of statements in MySQL, then you need to look into Transactions. MySQL Transactions help in making sure that the database never contains the result of a partially completed operation (‘set of statements’ ->  ‘operation’).

MySQL Transaction Example: SignUp Form with Address Details

Consider a SignUp form which asks for UserName, Email, Permanent Address and Temporary Address details to Register.

SignUp Form with Address details

SignUp Form with Address details

Since each user is associated with two addresses, the address detail needs to be stored in a separate table and the table structure for user and address table is created as follows:

CREATE TABLE User

(

 ID INT PRIMARY KEY IDENTITY(1,1),

 user_name VARCHAR(30),

 email VARCHAR(100)

)

CREATE TABLE Address

(

 ID INT PRIMARY KEY IDENTITY(1,1),

 street_address VARCHAR(200),

 city VARCHAR(100),

 zip_code VARCHAR(20),

 user_id INT FOREIGN KEY REFERENCES User(ID)

)

Table structure of User and Address table

The operation of inserting details from SignUp form into the tables involves two steps:

Step 1. Insert user_name, email into User table and get the user_id.

Step 2. Insert the Permanent and Temporary address details into Address table with the user_id retrieved from the table.

So, why do we need transactions here?

Flow – Without MySQL Transactions               

Flow without Transactions

Flow without Transactions

State 0    – Initial state of the database (Users and Address table are empty)

State 1    – Final desired state of the database (User table has one entry and Address table has two entries corresponding to the User)

State 0.1 – Undesired Intermediate State

Case 1. Step 1 fails

INSERT INTO Users (ID, user_name, email) VALUES

('asdf','bbb','bbb@gmail.com');

INSERT INTO Address (street_address, city, zip_code,permanent, user_id) VALUES

('98  Old Chapel Road','GARWAY','HR2 7XH',1,1),

('101  Lake View Road','GARWAY','HR2 7XH',0,1);

Error

Incorrect integer value: 'asdf' for column 'ID' at row 1

Users Table

Users Table

Address Table

Address Table

Case 2. Step 2 fails

INSERT INTO Users (ID, user_name, email) VALUES

(1,'bbb','bbb@gmail.com');

INSERT INTO Address (street_address, city, zip_code,permanent,user_id) VALUES

('98  Old Chapel Road','GARWAY','HR2 7XH',1,9),

('101  Lake View Road','GARWAY','HR2 7XH',0,9);

Error

Error mysql
Users Table

Users TableAddress Table

Address Table

As we see here, In the Flow without transactions, Case 2 leaves the database in an undesired state where the User table gets populated without its corresponding address in the Address table. Let’s see how we can resolve this by introducing Transactions here.

Flow – With MySQL Transactions             

Flow with Transactions Flow with Transactions

MySQL BEGIN/ START TRANSACTION  

– Marks the start of a transaction

MySQL COMMIT                                      

 – Marks the end of a transaction

Case 1. Step 1 fails

START TRANSACTION;

INSERT INTO Users (ID, user_name, email) VALUES

('asdf','bbb','bbb@gmail.com');

INSERT INTO Address (street_address, city, zip_code,permanent, user_id) VALUES

('98  Old Chapel Road','GARWAY','HR2 7XH',1,1),

('101  Lake View Road','GARWAY','HR2 7XH',0,1);

COMMIT;

Error

Error MySQL

Users Table

Users Table

Address Table

Address Table

Case 2. Step 2 fails

START TRANSACTION;

INSERT INTO Users (ID, user_name, email) VALUES

(1,'bbb','bbb@gmail.com');

INSERT INTO Address (street_address, city, zip_code,permanent,user_id) VALUES

('98  Old Chapel Road','GARWAY','HR2 7XH',1,9),

('101  Lake View Road','GARWAY','HR2 7XH',0,9);

COMMIT;

Error

mysql Error

Users Table

Users Table
Address Table

Address Table

Now, we clearly see that in both cases, Transactions ensure that there is no partial completion being recorded in the database.

Also, for any reason, if we didn’t want to save any changes made by the transaction, we can use MySQL ROLLBACK in place of COMMIT to revert back the database to State 0.

Points to Note

  • Check whether AUTOCOMMIT is disabled or not before starting with Transactions. If not, disable it using the command SET AUTOCOMMIT=0.
  • DDL commands should not be used within Transactions. If used, they will commit the ongoing Transaction as soon as a DDL command is executed..
  • We can have SAVEPOINTs within Transaction and can ROLLBACK to any SAVEPOINT within the transaction without terminating it.

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc