MySQL Transactions

Summary: in this tutorial, you will learn about MySQL transactions and how to use the START TRANSACTION, COMMIT and ROLLBACK statements to manage transactions in MySQL.

Introduction to MySQL transactions

In the database world, a transaction is a sequence of one or more SQL statements that are executed as a single unit of work.

Transactions allow you to ensure the integrity of data by enabling a set of operations to be either fully completed or fully rolled back in case of an error.

MySQL supports transactions via the START TRANSACTION, COMMIT, and ROLLBACKstatements:

START TRANSACTION – Mark the beginning of a transaction. Note that the BEGIN or BEGIN WORK are the aliases of the START TRANSACTION.
COMMIT – Apply the changes of a transaction to the database.
ROLLBACK – Undo the changes of a transaction by reverting the database to the state before the transaction starts.

By default, when you execute an SQL statement, MySQL automatically wraps it in a transaction and commits the transaction automatically.

To instruct MySQL to not start a transaction implicitly and commit the changes automatically, you set the value of the autocommit variable to 0 or OFF:

SET autocommit = OFF;Code language: SQL (Structured Query Language) (sql)

Or:

SET autocommit = 0;Code language: SQL (Structured Query Language) (sql)

To enable the auto-commit mode, you set the value of the autocommit variable to 1 or ON:

SET autocommit = 1;Code language: SQL (Structured Query Language) (sql)

Or:

SET autocommit = ON;Code language: SQL (Structured Query Language) (sql)

Basic MySQL transactions example

1) Setting up sample tables

First, connect to the MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Second, create a database called banks and a table named users:

CREATE DATABASE banks;

USE banks;

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255)
);Code language: SQL (Structured Query Language) (sql)

2) MySQL COMMIT example

First, start a transaction that inserts a new row into the users table and updates the email for the user:

START TRANSACTION;

INSERT INTO users (id, username) 
VALUES (1, 'john');


UPDATE users 
SET email = '[email protected]' 
WHERE id = 1;Code language: SQL (Structured Query Language) (sql)

Second, retrieve data from the users table:

SELECT * FROM users;Code language: SQL (Structured Query Language) (sql)

Output:

+----+----------+----------------------+
| id | username | email                |
+----+----------+----------------------+
|  1 | john     | [email protected] |
+----+----------+----------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The output shows that the users table has a row but it is only visible to the current session, not other sessions.

If you open another session and query data from the users table, you will not see any rows in the users table. The reason is that the transaction in the first session has not been committed.

Fifth, commit the transaction:

COMMIT;Code language: SQL (Structured Query Language) (sql)

The COMMIT statement applies all the changes made during the transaction, making them permanent and visible to other database sessions.

If you open the second session, you will be able to see the data from the users table.

3) MySQL ROLLBACK example

First, start a transaction:

START TRANSACTION;Code language: SQL (Structured Query Language) (sql)

Second, insert a new row into the users table and update the email for the user:

INSERT INTO users (id, username) 
VALUES (2, 'jane');


UPDATE users 
SET email = '[email protected]' 
WHERE id = 2;Code language: SQL (Structured Query Language) (sql)

Third, roll back the transaction:

ROLLBACK;Code language: SQL (Structured Query Language) (sql)

The ROLLBACK statement undoes all the changes made during the transaction, reverting the database to its state before the transaction started.

Finally, select data from the users table:

SELECT * FROM users;Code language: SQL (Structured Query Language) (sql)

Output:

+----+----------+----------------------+
| id | username | email                |
+----+----------+----------------------+
|  1 | john     | [email protected] |
+----+----------+----------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The output shows that the transaction has been rolled back so there is no new row in the users table.

Using MySQL transactions in Stored Procedures

1) Setting up sample tables

First, change the current database to banks:

CREATE DATABASE banks;Code language: SQL (Structured Query Language) (sql)

Second, create a new table called accounts to store the account holders and balances:

CREATE TABLE accounts (
    account_id INT AUTO_INCREMENT  PRIMARY KEY ,
    account_holder VARCHAR(255) NOT NULL,
    balance DECIMAL(10, 2) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Third, create a table called transactions to store the transactions between accounts:

CREATE TABLE transactions (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY,
    account_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    transaction_type ENUM('DEPOSIT', 'WITHDRAWAL') NOT NULL,
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);Code language: SQL (Structured Query Language) (sql)

Finally, insert two rows into the accounts table:

INSERT INTO accounts (account_holder, balance) 
VALUES ('John Doe', 1000.00),
       ('Jane Doe', 500.00);Code language: SQL (Structured Query Language) (sql)

Transferring money between two accounts

The following creates a stored procedure that transfers money between two accounts:

DELIMITER //

CREATE PROCEDURE transfer(
    IN sender_id INT,
    IN receiver_id INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE rollback_message VARCHAR(255) DEFAULT 'Transaction rolled back: Insufficient funds';
    DECLARE commit_message VARCHAR(255) DEFAULT 'Transaction committed successfully';

    -- Start the transaction
    START TRANSACTION;

    -- Attempt to debit money from account 1
    UPDATE accounts SET balance = balance - amount WHERE account_id = sender_id;

    -- Attempt to credit money to account 2
    UPDATE accounts SET balance = balance + amount WHERE account_id = receiver_id;

    -- Check if there are sufficient funds in account 1
    -- Simulate a condition where there are insufficient funds
    IF (SELECT balance FROM accounts WHERE account_id = sender_id) < 0 THEN
        -- Roll back the transaction if there are insufficient funds
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = rollback_message;
    ELSE
        -- Log the transactions if there are sufficient funds
        INSERT INTO transactions (account_id, amount, transaction_type) VALUES (sender_id, -amount, 'WITHDRAWAL');
        INSERT INTO transactions (account_id, amount, transaction_type) VALUES (receiver_id, amount, 'DEPOSIT');
        
        -- Commit the transaction
        COMMIT;
        SELECT commit_message AS 'Result';
    END IF;
END //

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

The transfer stored procedure transfers an amount between two accounts: sender and receiver. It has three parameters:

sender_id – the sender id.
receiver_id – the receiver id.
amount – the amount that will be transferred between the two accounts.

How it works.

First, start the transaction using the START TRANSACTION statement:

START TRANSACTION;Code language: SQL (Structured Query Language) (sql)

Second, increase the balance of the sender and decrease the balance of the receiver:

UPDATE 
  accounts 
SET 
  balance = balance - amount 
WHERE 
  account_id = sender_id;
  
UPDATE 
  accounts 
SET 
  balance = balance + amount 
WHERE 
  account_id = receiver_id;Code language: SQL (Structured Query Language) (sql)

Third, roll back the transaction if the balance of the sender’s account is not sufficient and also issue an error message:

IF (SELECT balance FROM accounts WHERE account_id = sender_id) < 0 THEN
   ROLLBACK;
   
   SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = rollback_message;Code language: SQL (Structured Query Language) (sql)

Otherwise, add two rows to the transactions table and apply the changes to the database by committing the transaction (in the ELSE branch):

INSERT INTO transactions (account_id, amount, transaction_type) VALUES (1, -amount, 'WITHDRAWAL');
INSERT INTO transactions (account_id, amount, transaction_type) VALUES (2, amount, 'DEPOSIT');

COMMIT;
SELECT commit_message AS 'Result';Code language: SQL (Structured Query Language) (sql)

Calling the transfer stored procedure

First, retrieve the balances of the accounts:

SELECT * FROM accounts;Code language: SQL (Structured Query Language) (sql)

Output:

+------------+----------------+---------+
| account_id | account_holder | balance |
+------------+----------------+---------+
|          1 | John Doe       | 1000.00 |
|          2 | Jane Doe       |  500.00 |
+------------+----------------+---------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Second, transfer 100 from account id 1 to account id 2:

CALL transfer(1,2,100);Code language: SQL (Structured Query Language) (sql)

Output:

+------------------------------------+
| Result                             |
+------------------------------------+
| Transaction committed successfully |
+------------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)Code language: SQL (Structured Query Language) (sql)

Since account id 1 has sufficient funds, the transaction succeeded.

Third, review the balances of the accounts:

SELECT 
  * 
FROM 
  accounts 
WHERE 
  account_id IN (1, 2);Code language: SQL (Structured Query Language) (sql)

Output:

+------------+----------------+---------+
| account_id | account_holder | balance |
+------------+----------------+---------+
|          1 | John Doe       |  900.00 |
|          2 | Jane Doe       |  600.00 |
+------------+----------------+---------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The output indicates that account id 1 has a balance of 900 and account id 2 has a balance of 600, which is correct.

SELECT * FROM transactions;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------+------------+---------+------------------+
| transaction_id | account_id | amount  | transaction_type |
+----------------+------------+---------+------------------+
|              1 |          1 | -100.00 | WITHDRAWAL       |
|              2 |          2 |  100.00 | DEPOSIT          |
+----------------+------------+---------+------------------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The transactions table also has two rows that record the transfer.

Fourth, attempt to transfer 1000 from account id 1 to account id 2:

CALL transfer(1,2,1000);Code language: SQL (Structured Query Language) (sql)

Output:

ERROR 1644 (45000): Transaction rolled back: Insufficient fundsCode language: SQL (Structured Query Language) (sql)

Because account id 1 does not have sufficient funds, the transaction was rolled back. Also, the balances of both accounts were reverted:

SELECT * FROM accounts;Code language: SQL (Structured Query Language) (sql)

Output:

+------------+----------------+---------+
| account_id | account_holder | balance |
+------------+----------------+---------+
|          1 | John Doe       |  900.00 |
|          2 | Jane Doe       |  600.00 |
+------------+----------------+---------+
2 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

Summary

A transaction is a sequence of SQL statements that is executed as a single unit of work.
Use the START TRANSACTION statement to start a transaction.
Use the COMMIT statement to apply the changes made during the transaction to the database.
Use the ROLLBACK statement to roll back the changes made during the transaction and revert the state of the database before the transaction starts.

Was this tutorial helpful?