Why transactions ensure data integrity in MySQL - See It in Action

Understanding Why Transactions Ensure Data Integrity

📖 Scenario: You are managing a simple bank database where users can transfer money between accounts. It is important that the money is deducted from one account and added to another account correctly, without losing or creating money accidentally.

🎯 Goal: Build a small MySQL transaction that transfers money between two accounts safely, ensuring data integrity by using transactions.

📋 What You'll Learn

Create a table called accounts with columns id (integer primary key) and balance (integer).

Insert two accounts with ids 1 and 2, with balances 1000 and 500 respectively.

Create a variable transfer_amount set to 200.

Write a transaction that deducts transfer_amount from account 1 and adds it to account 2.

Commit the transaction to save changes or rollback if any error occurs.

💡 Why This Matters

🌍 Real World

Banking systems and financial applications use transactions to ensure money transfers are accurate and reliable.

💼 Career

Understanding transactions is essential for database administrators and backend developers to maintain data integrity in multi-step operations.

1

Create the accounts table and insert initial data

Create a table called accounts with columns id as primary key and balance as integer. Insert two rows: (1, 1000) and (2, 500).

MySQL

-- Create the accounts table and insert two accounts
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Set the transfer amount variable

Create a variable called transfer_amount and set it to 200.

MySQL

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  balance INT
);

INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 500);

-- Set the transfer amount variable
-- Your code here

Need a hint?

Use SET @transfer_amount = 200; to create a user variable in MySQL.

3

Write the transaction to transfer money

Start a transaction with START TRANSACTION;. Deduct @transfer_amount from account with id = 1 and add @transfer_amount to account with id = 2. Use UPDATE accounts SET balance = balance - @transfer_amount WHERE id = 1; and UPDATE accounts SET balance = balance + @transfer_amount WHERE id = 2; inside the transaction.

MySQL

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  balance INT
);

INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 500);

SET @transfer_amount = 200;

-- Start transaction and update balances
-- Your code here

Need a hint?

Use START TRANSACTION; to begin and UPDATE statements to change balances.

4

Commit the transaction to save changes

Add COMMIT; at the end to save the changes made in the transaction.

MySQL

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  balance INT
);

INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 500);

SET @transfer_amount = 200;

START TRANSACTION;
UPDATE accounts SET balance = balance - @transfer_amount WHERE id = 1;
UPDATE accounts SET balance = balance + @transfer_amount WHERE id = 2;

-- Commit the transaction
-- Your code here

Need a hint?

Use COMMIT; to save all changes made in the transaction.