How to Use MySQL Transactions on Alibaba Cloud ECS or ApsaraDB for MySQL

In this article, we will show you how to implement MySQL transactions on your Alibaba Cloud MySQL database to ensure consistency and integrity of your data.

By Francis Ndungu, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud's incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.

MySQL is one of the most popular Relational Database Management Systems (RDBMS). The open-source database server is widely adopted in production environment for data storage especially with Content Management Systems like WordPress and Magento.

Alibaba Cloud is at the forefront of offering scalable, affordable and secure MySQL services through Elastic Compute Service (ECS) and ApsaraDB for RDS.

One of the key features of MySQL is its supports for transactions and explicit locks that make it the best when it comes to ACID compliance. We will explain this later in the guide.

MySQL transactions refers to group of database manipulation operations that are performed in a single batch. A transaction is regarded complete if all the individual operations succeed. If one of them fails, the transaction is rolled back to ensure data integrity.

In this guide, we will show you how to implement MySQL transactions on your MySQL database hosted on Alibaba Cloud to ensure consistency and integrity of your data.

Prerequisites

A valid Alibaba Account(Sign up now and get up a free trial worth up to $1200 to test 40+ Alibaba Cloud products)
An ECS instance with MySQL installed or an ECS instance for ApsaraDB for Redis with MySQL.
Username and password for your MySQL database.

Step 1: Defining ACID Compliance in Database Systems

When we say that MySQL is ACID compliant, it means the database supports 4 characteristics: Atomicity, Consistency, Isolation and Durability.

Atomicity

In database systems, atomicity ensures that multi-query operations are either completed successfully or nothing is updated on the system at all if an error happens between the different grouped queries. For instance, in a bank application, the transaction should be completed only if the software is able to debit the drawer and credit the payee. Otherwise, if one of the operation fails, the partial transaction should be rolled back.

Consistency

Consistency guarantees that a data operation is changed in the allowed manner. For instance, if an operation is saving data both to the orders and orders products table, the execution plan should affect both tables. If an error occurs when writing data to the second table, the transaction must be rolled back.

Isolation

To support isolation MySQL uses locks to ensure concurrent operations and integrity of data visible to multiple users. In a multi-user environment, isolation is inevitable.

Consider a scenario where millions of customers are gambling for the last seat in a booking system. If proper isolation of data is not supported on the system, multiple customers would end up with the same seat if they run the ordering process within the same microsecond.

It may sound rare but it happens in a production environment. In an ecommerce platform, isolation allows the application to assign a different and unique order id even if millions of customers are purchasing from the shopping cart simultaneously especially during peak seasons.

Durability

Durability is the process of committing transaction in a database permanently. Durability makes sure that changes are made permanently before allowing any other transaction of the same kind. E.g. in a mobile money transaction, a module issuing transactions IDs should book another user on the system only when the first user is assigned an ID that has been successfully saved to the disk.

To implement all these features, this is where MySQL transactions and locks come in to play.

Step 2: Logging to your MySQL Server

You can implement MySQL transactions when running queries either directly on the MySQL Command Line Interface, GUI based database administration tools(e.g. MySQL), or from your favourite programming language such as PHP or Python.

To follow along with this guide, we recommend that you login to your database server through a CLI. The basic syntax is:

$ mysql -u[username] -p -h[host name]

Example:

$ mysql -uroot -p -hlocalhost

Step 3: Creating a Sample Database and Tables

First, create a database named my_store. We will use innoDB as the storage engine for the tables because it supports the standard ACID-compliant transactions:

mysql>Create database my_store CHARACTER SET utf8 COLLATE utf8_general_ci;

We will demonstrate MySQL transactions from an e-commerce perspective. We will need to create 4 tables:

Before we do this, select the database we have created above first:

mysql>use my_store;

A brief explanation about the tables we are going to use:

customers table. This table will handle information about customers buying from the store.
products table. This is our data store for our products.
orders table. Once a customer makes an order, we will record the details here with the current date/time and customer_id.
order_products table. This table will handle all products associated with a particular order_id

Creating the 'customers' Table:

mysql> create table customers ( customer_id INT PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;

Load Sample Data to 'customers' Table

mysql> insert into customers (customer_id, customer_name) values ('1 ', 'John Doe');
mysql> insert into customers (customer_id, customer_name) values ('2 ', 'Baby James');
mysql> insert into customers (customer_id, customer_name) values ('3 ', 'Peter Smith');

You may run the query below to confirm the customers list:

mysql> select * from customers ;
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|           1 | John Doe      |
|           2 | Baby James    |
|           3 | Peter Smith   |
+-------------+---------------+
3 rows in set (0.00 sec)

Creating the 'products' Table:

mysql> create table products ( product_id BIGINT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL, price DOUBLE NOT NULL) Engine = InnoDB;

Loading Sample Data to 'products' Table

mysql>insert into products (product_id, product_name, price) values ('60023','Bluetooth Speaker', '23.99');
mysql> insert into products (product_id, product_name, price) values ('60024' ,'240 GB SSD', '52.43');
mysql> insert into products (product_id, product_name, price) values ('60025','Wireless Mouse', '18.50');

Confirming the Products

mysql> select * from products ;
+------------+-------------------+-------+
| product_id | product_name      | price |
+------------+-------------------+-------+
|      60023 | Bluetooth Speaker | 23.99 |
|      60024 | 240 GB SSD        | 52.43 |
|      60025 | Wireless Mouse    |  18.5 |
+------------+-------------------+-------+
3 rows in set (0.00 sec)

Creating the 'orders' Table:

The order_id field will be the primary key. However, we won't use the auto increment feature here. We will find the maximum order number from the previous transactions and add 1 to get the new order number. This is to demonstrate the principle of isolation.

Since our transaction will be isolated with locks, there is no chance of two customers getting the same order number even if millions are buying from our sites. Our database will only allow a new order to be executed once we are through with the first order and released the table locks.

So let's create the orders table first:

mysql> create table orders (order_id BIGINT PRIMARY KEY, order_date DATETIME NOT NULL, customer_id BIGINT NOT NULL) Engine = InnoDB;

Creating the 'order_products' Table:

The order_products table must contain the product_id so that it can log the product sold through a particular order. It must also have the order_id to identify the order and a order_product_id which is an auto generated field acting as the primary key for the table.

mysql> create table order_products ( order_product_id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT, product_id BIGINT NOT NULL, quantity DOUBLE NOT NULL) Engine = InnoDB;

Step 3: Creating a Sales Order Transactions

With our database schema and sample data in place, we can run an ACID compliant transaction. When a customer makes an order, 2 tables should be affected. That is, a new order should be registered on the orders table and the products purchased by the customer should be added to the order_products table.

If any of the operation fails, the transaction should be rolled back. The correct way to do this is to begin a transaction with SET autocommit =0 followed by lock tables, then do the two transactions, commit the changes and finally unlock the tables as shown below:

mysql>SET autocommit=0;
mysql>LOCK TABLES orders WRITE;
mysql>... save changes to order_products and orders table ...
mysql>COMMIT;
mysql>UNLOCK TABLES;

So this is the correct syntax. In the following ACID compliant SQL statement we will create a transaction to signify an order made by customer 1 (John Doe). He bought 2 Bluetooth Speakers, 4 pieces of the 240GB SSD and 6 pieces of Wireless Mouse.

Please note, we will just record the product_id on the order_products table. In case we need to know the names of the merchandise that the customer bought, we can just run a join with the products table.

Also, we are acquiring a WRITE lock to ensure that other MySQL connections/sessions won't be able to read or write to the table before we complete the transaction. If a new sessions tries to create an order before we complete ours, it will be waitlisted until we unlock the tables. This is where the beauty of transactions comes in.

To create our order, run the SQL commands one by one. If any of the transaction fails, issue the ROLLBACK; and UNLOCK TABLES; command after each other:

mysql> SET autocommit=0;
mysql> LOCK TABLES orders WRITE, order_products WRITE;
mysql> SELECT @order_id:= (ifnull (max(order_id), 0) +1) from orders;
mysql> INSERT INTO orders (order_id, order_date, customer_id) Values (@order_id, NOW(), '1' );
mysql> INSERT INTO order_products (order_id, product_id, quantity) Values (@order_id, '60023', '2');
mysql> INSERT INTO order_products (order_id, product_id, quantity) Values (@order_id, '60024', '4');
mysql> INSERT INTO order_products (order_id, product_id, quantity) Values (@order_id, '60025', '6');
mysql> COMMIT;
mysql> UNLOCK TABLES;

Confirming the Transactions:

The orders table:

mysql> select * from orders ;
+----------+---------------------+-------------+
| order_id | order_date          | customer_id |
+----------+---------------------+-------------+
|        1 | 2018-08-23 01:10:08 |           1 |
+----------+---------------------+-------------+
1 row in set (0.00 sec)

The order_products table:

mysql> select * from order_products;
+------------------+----------+------------+----------+
| order_product_id | order_id | product_id | quantity |
+------------------+----------+------------+----------+
|                1 |        1 |      60023 |        2 |
|                2 |        1 |      60024 |        4 |
|                3 |        1 |      60025 |        6 |
+------------------+----------+------------+----------+
3 rows in set (0.00 sec)

Remember if you were using a programming language like PHP to run the transaction, you would evaluate whether the transaction was successful before issuing the commit operation, otherwise, you would roll back the transaction.

Irrespective of whether the transaction is successful or not, always issue the unlock tables command to avoid holding other transactions on the queue.

If (all transactions were successful)
{
COMMIT;
}
else
{
ROLLBACK;
}
UNLOCK TABLES;

If you have followed our tutorial up to this point and everything worked as explained, congratulations! You have just created the first ACID compliant transaction on your MySQL database hosted on Alibaba Cloud.

Conclusion

In this guide, we have taken you through the steps of creating transactions on your MySQL database hosted on Alibaba Cloud ECS instance or ApsaraDB for Redis. Remember, while this is a demonstration to prove the concept of transactions in MySQL, you can expand it further and apply it in a production environment to avoid race conditions on your applications and enhance database integrity in a multi-user environment.

Community

How to Use MySQL Transactions on Alibaba Cloud ECS or ApsaraDB for MySQL

Prerequisites

Step 1: Defining ACID Compliance in Database Systems

Atomicity

Consistency

Isolation

Durability

Step 2: Logging to your MySQL Server

Step 3: Creating a Sample Database and Tables

Creating the 'customers' Table:

Load Sample Data to 'customers' Table

Creating the 'products' Table:

Loading Sample Data to 'products' Table

Confirming the Products

Creating the 'orders' Table:

Creating the 'order_products' Table:

Step 3: Creating a Sales Order Transactions

Confirming the Transactions:

Conclusion

Read previous post:

Read next post:

francisndungu

You may also like

Comments

francisndungu

Related Products

ApsaraDB RDS for MySQL

PolarDB for MySQL

Database for FinTech Solution

ApsaraDB RDS for PostgreSQL