The author selected Open Sourcing Mental Illness to receive a donation as part of the Write for DOnations program.
A MySQL transaction is a group of logically related SQL commands that are executed in the database as a single unit. Transactions are used to enforce ACID (Atomicity, Consistency, Isolation, and Durability) compliance in an application. This is a set of standards that govern the reliability of processing operations in a database.
Atomicity ensures the success of related transactions or a complete failure if an error occurs. Consistency guarantees the validity of the data submitted to the database according to defined business logic. Isolation is the correct execution of concurrent transactions ensuring the effects of different clients connecting to a database do not affect each other. Durability ensures that logically related transactions remain in the database permanently.
SQL statements issued via a transaction should either succeed or fail altogether. If any of the queries fails, MySQL rolls back the changes and they are never committed to the database.
A good example to understand how MySQL transactions work is an e-commerce website. When a customer makes an order, the application inserts records into several tables, such as: orders
and orders_products
, depending on the business logic. Multi-table records related to a single order must be atomically sent to the database as a single logical unit.
Another use-case is in a bank application. When a client is transferring money, a couple of transactions are sent to the database. The sender’s account is debited and the receiver’s party account is credited. The two transactions must be committed simultaneously. If one of them fails, the database will revert to its original state and no changes should be saved to disk.
In this tutorial, you will use the PDO PHP Extension, which provides an interface for working with databases in PHP, to perform MySQL transactions on an Ubuntu 18.04 server.
Before you begin, you will need the following:
You’ll first create a sample database and add some tables before you start working with MySQL transactions. First, log in to your MySQL server as root:
When prompted, enter your MySQL root password and hit ENTER
to proceed. Then, create a database, for the purposes of this tutorial we’ll call the database sample_store
:
You will see the following output:
OutputQuery OK, 1 row affected (0.00 sec)
Create a user called sample_user
for your database. Remember to replace PASSWORD
with a strong value:
Issue full privileges for your user to the sample_store
database:
Finally, reload the MySQL privileges:
You’ll see the following output once you’ve created your user:
OutputQuery OK, 0 rows affected (0.01 sec)
. . .
With the database and user in place, you can now create several tables for demonstrating how MySQL transactions work.
Log out from the MySQL server:
Once the system logs you out, you will see the following output:
OutputBye.
Then, log in with the credentials of the sample_user
you just created:
Enter the password for the sample_user
and hit ENTER
to proceed.
Switch to the sample_store
to make it the currently selected database:
You’ll see the following output once it is selected:
OutputDatabase Changed.
Next, create a products
table:
This command creates a products
table with a field named product_id
. You use a BIGINT
data type that can accommodate a large value of up to 2^63-1. You use this same field as a PRIMARY KEY
to uniquely identify products. The AUTO_INCREMENT
keyword instructs MySQL to generate the next numeric value as new products are inserted.
The product_name
field is of type VARCHAR
that can hold up to a maximum of 50
letters or numbers. For the product price
, you use a DOUBLE
data type to cater for floating point formats in prices with decimal numbers.
Lastly, you use the InnoDB
as the ENGINE
because it comfortably supports MySQL transactions as opposed to other storage engines such as MyISAM
.
Once you’ve created your products
table, you’ll get the following output:
OutputQuery OK, 0 rows affected (0.02 sec)
Next, add some items to the products
table by running the following commands:
You’ll see output similar to the following after each INSERT
operation:
OutputQuery OK, 1 row affected (0.02 sec)
. . .
Then, verify that the data was added to the products table:
You will see a list of the four products that you have inserted:
Output+------------+-------------------+-------+
| product_id | product_name | price |
+------------+-------------------+-------+
| 1 | WINTER COAT | 25.5 |
| 2 | EMBROIDERED SHIRT | 13.9 |
| 3 | FASHION SHOES | 45.3 |
| 4 | PROXIMA TROUSER | 39.95 |
+------------+-------------------+-------+
4 rows in set (0.01 sec)
Next, you’ll create a customers
table for holding basic information about customers:
As in the products
table, you use the BIGINT
data type for the customer_id
and this will ensure the table can support a lot of customers up to 2^63-1 records. The keyword AUTO_INCREMENT
increments the value of the columns once you insert a new customer.
Since the customer_name
column accepts alphanumeric values, you use VARCHAR
data type with a limit of 50
characters. Again, you use the InnoDB
storage ENGINE
to support transactions.
After running the previous command to create the customers
table, you will see the following output:
OutputQuery OK, 0 rows affected (0.02 sec)
You’ll add three sample customers to the table. Run the following commands:
Once the customers have been added, you will see an output similar to the following:
OutputQuery OK, 1 row affected (0.02 sec)
. . .
Then, verify the data in the customers
table:
You’ll see a list of the three customers:
Output+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1 | JOHN DOE |
| 2 | ROE MARY |
| 3 | DOE JANE |
+-------------+---------------+
3 rows in set (0.00 sec)
Next, you’ll create an orders
table for recording orders placed by different customers. To create the orders
table, execute the following command:
You use the column order_id
as the PRIMARY KEY
. The BIGINT
data type allows you to accommodate up to 2^63-1 orders and will auto-increment after each order insertion. The order_date
field will hold the actual date and time the order is placed and hence, you use the DATETIME
data type. The customer_id
relates to the customers
table that you created previously.
You will see the following output:
OutputQuery OK, 0 rows affected (0.02 sec)
Since a single customer’s order may contain multiple items, you need to create an orders_products
table to hold this information.
To create the orders_products
table, run the following command:
You use the ref_id
as the PRIMARY KEY
and this will auto-increment after each record insertion. The order_id
and product_id
relate to the orders
and the products
tables respectively. The price
column is of data type DOUBLE
in order to accommodate floating values.
The storage engine InnoDB
must match the other tables created previously since a single customer’s order will affect multiple tables simultaneously using transactions.
Your output will confirm the table’s creation:
OutputQuery OK, 0 rows affected (0.02 sec)
You won’t be adding any data to the orders
and orders_products
tables for now but you’ll do this later using a PHP script that implements MySQL transactions.
Log out from the MySQL server:
Your database schema is now complete and you’ve populated it with some records. You’ll now create a PHP class for handling database connections and MySQL transactions.
In this step, you will create a PHP class that will use PDO (PHP Data Objects) to handle MySQL transactions. The class will connect to your MySQL database and insert data atomically to the database.
Save the class file in the root directory of your Apache web server. To do this, create a DBTransaction.php
file using your text editor:
Then, add the following code to the file. Replace PASSWORD
with the value you created in Step 1:
Toward the beginning of the DBTransaction
class, the PDO will use the constants (DB_HOST
, DB_NAME
, DB_USER
, and DB_PASSWORD
) to initialize and connect to the database that you created in step 1.
Note: Since we are demonstrating MySQL transactions in a small scale here, we have declared the database variables in the DBTransaction
class. In a large production project, you would normally create a separate configuration file and load the database constants from that file using a PHP require_once
statement.
Next, you set two attributes for the PDO class:
ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION
: This attribute instructs PDO to throw an exception if an error is encountered. Such errors can be logged for debugging.ATTR_EMULATE_PREPARES, false
: This option disables emulation of prepared statements and allows the MySQL database engine to prepare the statements itself.Now add the following code to your file to create the methods for your class:
Save and close the file by pressing CTRL
+ X
, Y
, then ENTER
.
To work with MySQL transactions, you create three main methods in the DBTransaction
class; startTransaction
, insertTransaction
, and submitTransaction
.
startTransaction
: This method instructs PDO to start a transaction and turns auto-commit off until a commit command is issued.
insertTransaction
: This method takes two arguments. The $sql
variable holds the SQL statement to be executed while the $data
variable is an array of the data to be bound to the SQL statement since you’re using prepared statements. The data is passed as an array to the insertTransaction
method.
submitTransaction
: This method commits the changes to the database permanently by issuing a commit()
command. However, if there is an error and the transactions have a problem, the method calls the rollBack()
method to revert the database to its original state in case a PDO exception is raised.
Your DBTransaction
class initializes a transaction, prepares the different SQL commands to be executed, and finally commits the changes to the database atomically if there are no issues, otherwise, the transaction is rolled back. In addition, the class allows you to retrieve the record order_id
you just created by accessing the public property last_insert_id
.
The DBTransaction
class is now ready to be called and used by any PHP code, which you’ll create next.
You’ll create a PHP script that will implement the DBTransaction
class and send a group of SQL commands to the MySQL database. You’ll mimic the workflow of a customer’s order in an online shopping cart.
These SQL queries will affect the orders
and the orders_products
tables. Your DBTransaction
class should only allow changes to the database if all of the queries are executed without any errors. Otherwise, you’ll get an error back and any attempted changes will roll back.
You are creating a single order for the customer JOHN DOE
identified with customer_id 1
. The customer’s order has three different items with differing quantities from the products
table. Your PHP script takes the customer’s order data and submits it into the DBTransaction
class.
Create the orders.php
file:
Then, add the following code to the file:
You’ve created a PHP script that initializes an instance of the DBTransaction
class that you created in Step 2.
In this script, you include the DBTransaction.php
file and you initialize the DBTransaction
class. Next, you prepare a multi-dimensional array of all the products the customer is ordering from the store. You also invoke the startTransaction()
method to start a transaction.
Next add the following code to finish your orders.php
script:
Save and close the file by pressing CTRL
+ X
, Y
, then ENTER
.
You prepare the command to be inserted to the orders table via the insertTransaction
method. After this, you retrieve the value of the public property last_insert_id
from the DBTransaction
class and use it as the $order_id
.
Once you have an $order_id
, you use the unique ID to insert the customer’s order items to the orders_products
table.
Finally, you call the method submitTransaction
to commit the entire customer’s order details to the database if there are no problems. Otherwise, the method submitTransaction
will rollback the attempted changes.
Now you’ll run the orders.php
script in your browser. Run the following and replace your-server-IP
with the public IP address of your server:
http://your-server-IP/orders.php
You will see confirmation that the records were successfully submitted:
Your PHP script is working as expected and the order together with the associated order products were submitted to the database atomically.
You’ve run the orders.php
file on a browser window. The script invoked the DBTransaction
class which in turn submitted the orders
details to the database. In the next step, you will verify if the records saved to the related database tables.
In this step, you’ll check if the transaction initiated from the browser window for the customer’s order was posted to the database tables as expected.
To do this, log in to your MySQL database again:
Enter the password for the sample_user
and hit ENTER
to continue.
Switch to the sample_store
database:
Ensure the database is changed before proceeding by confirming the following output:
OutputDatabase Changed.
Then, issue the following command to retrieve records from the orders
table:
This will display the following output detailing the customer’s order:
Output+----------+---------------------+-------------+-------------+
| order_id | order_date | customer_id | order_total |
+----------+---------------------+-------------+-------------+
| 1 | 2020-01-11 00:00:00 | 2 | 157.8 |
+----------+---------------------+-------------+-------------+
1 row in set (0.00 sec)
Next, retrieve the records from the orders_products
table:
You’ll see output similar to the following with a list of products from the customer’s order:
Output+--------+----------+------------+-------+----------+
| ref_id | order_id | product_id | price | quantity |
+--------+----------+------------+-------+----------+
| 1 | 1 | 1 | 25.5 | 1 |
| 2 | 1 | 2 | 13.9 | 3 |
| 3 | 1 | 3 | 45.3 | 2 |
+--------+----------+------------+-------+----------+
3 rows in set (0.00 sec)
The output confirms that the transaction was saved to the database and your helper DBTransaction
class is working as expected.
In this guide, you used the PHP PDO to work with MySQL transactions. Although this is not a conclusive article on designing an e-commerce software, it has provided an example for using MySQL transactions in your applications.
To learn more about the MySQL ACID model, consider visiting the InnoDB and the ACID Model guide from the official MySQL website. Visit our MySQL content page for more related tutorials, articles, and Q&A.
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
I learned a lot from this tutorial. I arrived to it knowing very little and now feel better about the PHP Data Object!
I humbly fixed several small typos in the code. Here are my three working files:
and
and since I made an extra file, it has just the password in it.
Francis, thank you for the great article.
I have a tip to share: If folks have this code fail silently, with no error message, it is good to watch the browser dev-tools network tab, to see a 500 error or not.
Even better tip, stick these two lines at the top of your php code:
These two lines helped me see some mistakes in my SQL config.
I think you also forgot to call startTransaction()
There are a couple issues in this tutorial. First, in orders.php this function is incorrect and the order_id needs to be included in the array.
$transaction->insertQuery($order_query, [ ‘customer_id’ => $customer_id, ‘order_date’ => “2020-01-11”, ‘order_total’ => 157.8 ]);
should be $transaction->insertTransaction($order_query, [ ‘order_id’ => 1,
‘customer_id’ => $customer_id, ‘order_date’ => “2020-01-11”, ‘order_total’ => 157.8 ]);
Second,the insert in the for loop should also be insertTransaction. Third, $result = $transaction->submit(); should be $result = $transaction->submitTransaction();