Introduction
In this lab, you will learn how to use MySQL transactions to ensure data integrity. Transactions group a series of SQL operations into a single, all-or-nothing unit of work. This is essential for tasks like financial transfers, where all steps must complete successfully.
You will practice starting a transaction with BEGIN, making changes permanent with COMMIT, undoing changes with ROLLBACK, and performing partial rollbacks using SAVEPOINT. By the end of this lab, you will understand how to manage database operations safely and reliably.
Set Up the Database and Tables
Before working with transactions, you need to set up a database and a table. In this step, you will create a database named bank_db and an accounts table to simulate banking operations.
First, open the terminal from your desktop.
Connect to the MySQL server as the root user. This lab environment does not require a password for the root user.
sudo mysql -u root
Once connected, you will see the MySQL prompt (mysql>), indicating you can now run SQL commands.
Create the bank_db database and switch to it for the current session. The IF NOT EXISTS clause prevents an error if the database already exists.
CREATE DATABASE IF NOT EXISTS bank_db;
USE bank_db;
Next, create the accounts table. This table will store account information, including an ID, account number, and balance.
CREATE TABLE accounts (
id INT AUTO_INCREMENT PRIMARY KEY,
account_number VARCHAR(20) UNIQUE NOT NULL,
balance DECIMAL(10, 2) NOT NULL
);
Now, insert two sample accounts into the accounts table. These will serve as our starting data.
INSERT INTO accounts (account_number, balance) VALUES
('ACC001', 1000.00),
('ACC002', 500.00);
To confirm that the table and data were created correctly, run the following query:
SELECT * FROM accounts;
You should see the two accounts you just inserted. The output will look like this:
+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
| 1 | ACC001 | 1000.00 |
| 2 | ACC002 | 500.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)
With the database and table prepared, you are ready to learn about transactions.
Create a Successful Transaction with BEGIN and COMMIT
A transaction is a sequence of operations performed as a single logical unit. To make changes permanent, you must COMMIT them. In this step, you will simulate a successful bank transfer from ACC001 to ACC002.
You should still be in the MySQL shell from the previous step.
First, start a new transaction using the BEGIN command. All subsequent commands will be part of this transaction until you COMMIT or ROLLBACK.
BEGIN;
Now, perform the two operations for the transfer: deduct $100 from ACC001 and add $100 to ACC002.
UPDATE accounts SET balance = balance - 100.00 WHERE account_number = 'ACC001';
UPDATE accounts SET balance = balance + 100.00 WHERE account_number = 'ACC002';
At this point, the changes are only visible within your current session. You can check the temporary state of the accounts:
SELECT * FROM accounts;
The output will show the updated balances, but they are not yet saved permanently.
+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
| 1 | ACC001 | 900.00 |
| 2 | ACC002 | 600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)
Since both operations were successful, you can make the changes permanent by committing the transaction.
COMMIT;
The COMMIT command saves all changes made during the transaction. The transfer is now complete and visible to all other database connections. You can verify this by querying the table again. The balances will remain at $900 and $600.
Undo a Transaction with ROLLBACK
Sometimes, an operation within a transaction fails, or you may decide to cancel it. The ROLLBACK command discards all changes made during the current transaction, restoring the database to its state before the transaction began.
Let's start a new transaction to add a new account.
BEGIN;
Now, insert a new account ACC003 into the accounts table.
INSERT INTO accounts (account_number, balance) VALUES ('ACC003', 150.00);
Check the table to see the new account within the transaction.
SELECT * FROM accounts;
You will see three accounts listed.
+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
| 1 | ACC001 | 900.00 |
| 2 | ACC002 | 600.00 |
| 3 | ACC003 | 150.00 |
+----+----------------+---------+
3 rows in set (0.00 sec)
Now, imagine you realize this account should not have been added. Instead of committing, you can cancel the entire transaction using ROLLBACK.
ROLLBACK;
This command undoes the INSERT operation. To verify this, query the accounts table again.
SELECT * FROM accounts;
The ACC003 account is gone, and the table is back to the state it was in before you typed BEGIN.
+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
| 1 | ACC001 | 900.00 |
| 2 | ACC002 | 600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)
ROLLBACK is a critical tool for maintaining data integrity when errors occur.
Use SAVEPOINT for Partial Rollbacks
For long transactions, you might want to undo only a part of the work instead of the entire transaction. SAVEPOINT allows you to set a marker within a transaction that you can later roll back to.
Let's start a new transaction.
BEGIN;
First, update the balance of ACC001, increasing it by $50.
UPDATE accounts SET balance = balance + 50.00 WHERE account_number = 'ACC001';
Now, create a savepoint. This acts as a bookmark for the current state of the transaction.
SAVEPOINT after_update;
Next, perform another operation: insert a new account ACC004.
INSERT INTO accounts (account_number, balance) VALUES ('ACC004', 300.00);
Let's assume the insertion of ACC004 was a mistake, but the update to ACC001 was correct. You can roll back to the savepoint to undo only the INSERT statement.
ROLLBACK TO after_update;
This command reverts the transaction to the state it was in when you created the after_update savepoint. The INSERT of ACC004 is undone, but the UPDATE to ACC001 remains.
Query the table to confirm. ACC004 should be gone, but the balance of ACC001 should be updated.
SELECT * FROM accounts;
The output should show ACC001 with a balance of $950.00 and no ACC004.
+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
| 1 | ACC001 | 950.00 |
| 2 | ACC002 | 600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)
Since you want to keep the update to ACC001, you can now commit the remaining part of the transaction.
COMMIT;
You have successfully used a savepoint to perform a partial rollback. You can now exit the MySQL shell.
exit;
Summary
In this lab, you have learned the fundamentals of MySQL transactions for maintaining data integrity. You practiced the complete transaction lifecycle, from starting a transaction with BEGIN to finalizing it with COMMIT or canceling it with ROLLBACK.
You successfully simulated a bank transfer, ensuring that multiple operations were treated as a single atomic unit. You also learned how to use SAVEPOINT to gain finer control over complex transactions by enabling partial rollbacks. These skills are crucial for developing robust and reliable database applications where data consistency is paramount.
