Transaction

A transaction in MySQL is a sequence of one or more SQL operations that are executed as a single unit of work. Transactions ensure that either all operations are completed successfully or none of them are applied, maintaining the integrity of the database. This is particularly important in scenarios where multiple operations need to be performed together, such as transferring funds between accounts.

  • DDL (Data Definition Language) statements like CREATE, ALTER, and DROP are not part of transactions and will cause an implicit commit.
  • DML (Data Manipulation Language) statements like INSERT, UPDATE, and DELETE can be included in transactions and will not take effect until the transaction is committed.
  • Truncation of a table using TRUNCATE is also not part of a transaction and will cause an implicit commit.

ACID Properties

Transactions in MySQL adhere to the ACID properties, which stand for Atomicity, Consistency, Isolation, and Durability:

  1. Atomicity: Ensures that all operations within a transaction are treated as a single unit. If any operation fails, the entire transaction is rolled back, and the database remains unchanged.
  2. Consistency: Ensures that a transaction brings the database from one valid state to another valid state, maintaining the integrity of the database.
  3. Isolation: Ensures that the operations of one transaction are isolated from those of other transactions, preventing interference and ensuring that concurrent transactions do not affect each other.
  4. Durability: Ensures that once a transaction is committed, its changes are permanent and will survive any subsequent system failures.

Transaction Control Commands

MySQL provides several commands to control transactions:

  • START TRANSACTION: Begins a new transaction.
  • COMMIT: Saves all changes made during the transaction to the database.
  • ROLLBACK: Undoes all changes made during the transaction, reverting the database to its previous state.

Example

1
2
3
4
5
6
7
8
SET autocommit = 0; -- Disable autocommit mode
START TRANSACTION; -- Start a new transaction
INSERT INTO accounts (account_id, balance) VALUES (1, 1000);
INSERT INTO accounts (account_id, balance) VALUES (2, 500);
-- Simulate a transfer of $200 from account 1 to account 2
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;
COMMIT; -- Commit the transaction to save changes

In this example, we start a transaction to transfer funds between two accounts. If any of the operations fail, we can use ROLLBACK to undo the changes and maintain the integrity of the database.

1
2
3
4
5
6
7
8
SET autocommit = 0; -- Disable autocommit mode
START TRANSACTION; -- Start a new transaction
INSERT INTO accounts (account_id, balance) VALUES (1, 1000);
INSERT INTO accounts (account_id, balance) VALUES (2, 500);
-- Simulate a transfer of $200 from account 1 to account 2
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;
ROLLBACK; -- Rollback the transaction to undo changes

Transaction Isolation Levels

MySQL supports different transaction isolation levels that determine how transactions interact with each other. The isolation levels include:

  1. Read Uncommitted: Allows transactions to read uncommitted changes made by other transactions, which can lead to dirty reads.
  2. Read Committed: Allows transactions to read only committed changes made by other transactions, preventing dirty reads but allowing non-repeatable reads and phantom reads.
  3. Repeatable Read: Ensures that if a transaction reads a row, it will see the same value for that row throughout the transaction, preventing non-repeatable reads but allowing phantom reads.
  4. Serializable: The highest isolation level that ensures complete isolation between transactions, preventing dirty reads, non-repeatable reads, and phantom reads, but can lead to reduced concurrency and performance.

To set the transaction isolation level in MySQL, you can use the following command:

1
2
3
4
-- query the current transaction isolation level
SELECT @@transaction_isolation;
-- Set the transaction isolation level for the current session
SET TRANSACTION ISOLATION LEVEL isolation_level;

Replace isolation_level with one of the isolation levels mentioned above (e.g., READ COMMITTED, REPEATABLE READ, etc.).