MySQL Transactions

Introduction

In this chapter, we will learn about transactions in MySQL. Transactions are a fundamental concept in database management systems that allow you to execute a series of SQL statements as a single unit of work. Transactions help ensure data integrity and consistency, especially in the context of concurrent access and system failures. We will cover the syntax for managing transactions, examples of their usage, and important considerations for using transactions in MySQL.

What is a Transaction?

A transaction is a sequence of one or more SQL statements that are executed as a single unit of work. A transaction has four key properties, commonly referred to as ACID properties:

• Atomicity: Ensures that all operations within the transaction are completed successfully. If any operation fails, the entire transaction is rolled back.
• Consistency: Ensures that the database remains in a consistent state before and after the transaction.
• Isolation: Ensures that transactions are isolated from each other, preventing concurrent transactions from interfering with each other.
• Durability: Ensures that once a transaction is committed, its changes are permanent, even in the event of a system failure.

Syntax

The basic syntax for managing transactions in MySQL is:

START TRANSACTION;
-- SQL statements
COMMIT;

-- or

START TRANSACTION;
-- SQL statements
ROLLBACK;

Example

Here is an example of how to use transactions in MySQL:

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

COMMIT;

In this example, two SQL statements are executed as a single transaction. If both statements succeed, the changes are committed. If any statement fails, the transaction can be rolled back to its previous state.

Full Example

Let’s go through a complete example where we create a database and a table, and use transactions to manage data consistency.

1. Create a Database:

CREATE DATABASE bank;

2. Select the Database:

USE bank;

3. Create the Accounts Table:

CREATE TABLE accounts (
    account_id INT PRIMARY KEY AUTO_INCREMENT,
    account_holder VARCHAR(100) NOT NULL,
    balance DECIMAL(10, 2) NOT NULL
);

4. Insert Data into the Accounts Table:

INSERT INTO accounts (account_holder, balance) VALUES
('Rahul Sharma', 1000.00),
('Priya Singh', 1500.00);

5. Transfer Funds Using a Transaction:

START TRANSACTION;

UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;

COMMIT;

6. Query the Accounts Table:

SELECT * FROM accounts;

Output

In this example, the transaction ensures that the transfer of funds between accounts is completed successfully. If any part of the transaction fails, the entire transaction is rolled back.

Rollback Example

Let’s see an example where a transaction is rolled back due to an error:

START TRANSACTION;

UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 200 WHERE account_id = 3; -- This will fail as account_id 3 does not exist

ROLLBACK;

Output

SELECT * FROM accounts;

In this example, the transaction is rolled back because the second UPDATE statement fails.

Important Considerations

• Isolation Levels: MySQL supports different isolation levels to control the visibility of changes made by one transaction to other transactions. The default isolation level is REPEATABLE READ.

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

• Autocommit Mode: By default, MySQL operates in autocommit mode, where each SQL statement is treated as a separate transaction. You can disable autocommit mode to manage transactions manually.

  SET autocommit = 0;
  

• Savepoints: You can use savepoints within a transaction to create intermediate points that you can roll back to without affecting the entire transaction.

  SAVEPOINT savepoint_name;
  -- SQL statements
  ROLLBACK TO SAVEPOINT savepoint_name;
  

• Error Handling: Proper error handling is crucial when working with transactions. Always check for errors and decide whether to commit or roll back based on the outcome.

Conclusion

Transactions are essential for ensuring data integrity and consistency in MySQL. This chapter covered the syntax for managing transactions, provided full examples of their usage, and discussed important considerations.