MySQL Transaction

MySQL Transaction

MySQL Transactions

A transaction in MySQL is a sequence of one or more SQL operations (such as INSERT, UPDATE, DELETE, etc.) that are executed as a single unit. The main purpose of using transactions is to ensure the ACID properties (Atomicity, Consistency, Isolation, and Durability) are maintained, ensuring that your database operations are reliable, consistent, and free of errors even in case of system failures or power outages.


ACID Properties of Transactions

  1. Atomicity: A transaction is atomic, meaning it is treated as a single unit. Either all operations within the transaction are completed successfully, or none of them are applied.
  2. Consistency: A transaction ensures that the database transitions from one valid state to another, maintaining database integrity.
  3. Isolation: The operations of a transaction are isolated from other transactions. Even if multiple transactions are running concurrently, the results will be as if each transaction ran sequentially.
  4. Durability: Once a transaction is committed, the changes are permanent, even if the system crashes after the transaction is completed.

Transaction Control Statements in MySQL

MySQL provides several SQL statements to control transactions:

  • START TRANSACTION or BEGIN: Begins a new transaction.
  • COMMIT: Saves all changes made in the current transaction.
  • ROLLBACK: Undoes the changes made in the current transaction.
  • SAVEPOINT: Sets a point within a transaction to which you can roll back later.
  • RELEASE SAVEPOINT: Releases a savepoint within a transaction.
  • SET TRANSACTION: Sets the isolation level for the current transaction.

Transaction Syntax

START TRANSACTION; -- SQL queries like INSERT, UPDATE, DELETE, etc. COMMIT; -- If everything is successful

If something goes wrong during the transaction and you need to undo the changes:

START TRANSACTION; -- SQL queries ROLLBACK; -- If something goes wrong, rollback the changes

Transaction Example

Example 1: Basic Transaction

Imagine a simple bank transfer between two accounts, account_A and account_B. A transaction ensures that both the withdrawal from one account and the deposit into the other happen together.

START TRANSACTION; -- Deduct money from account_A UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A'; -- Add money to account_B UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B'; -- Commit the transaction if both updates are successful COMMIT;

If something goes wrong (e.g., insufficient funds), you can undo the transaction:

START TRANSACTION; -- Deduct money from account_A UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A'; -- Add money to account_B UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B'; -- Something went wrong, rollback ROLLBACK;

In this example:

  • START TRANSACTION begins the transaction.
  • COMMIT makes the changes permanent.
  • ROLLBACK undoes the changes if an error occurs.

Isolation Levels in MySQL Transactions

MySQL supports different isolation levels that control how transactions interact with each other, which can affect consistency and performance. The isolation levels define the visibility of changes made by one transaction to other concurrent transactions.

1. Read Uncommitted (Lowest Isolation)

  • Transactions can see changes made by other transactions, even if they haven’t been committed yet.
  • Risk: Dirty reads (i.e., reading uncommitted data that may later be rolled back).

2. Read Committed

  • A transaction can only see committed changes from other transactions.
  • Risk: Non-repeatable reads (i.e., data might change if read again within the same transaction).

3. Repeatable Read (Default in MySQL)

  • Ensures that any data read during the transaction is consistent throughout the transaction, meaning other transactions cannot change it.
  • Risk: Phantom reads (i.e., new rows matching a query condition may appear or disappear during the transaction).

4. Serializable (Highest Isolation)

  • Transactions are executed serially (one after the other) to avoid all concurrency issues.
  • Risk: Reduced performance due to the high degree of locking.

Setting Transaction Isolation Level

You can set the isolation level for a transaction using the SET TRANSACTION command.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

This command sets the isolation level for the next transaction. To view the current isolation level:

SELECT @@global.tx_isolation;

Using Savepoints in Transactions

A savepoint allows you to create a point within a transaction that you can roll back to, rather than rolling back the entire transaction. This is useful for handling errors more granularly.

Syntax:

SAVEPOINT savepoint_name; -- Operations ROLLBACK TO SAVEPOINT savepoint_name; -- Rollback to specific savepoint RELEASE SAVEPOINT savepoint_name; -- Release the savepoint

Example with Savepoints:

START TRANSACTION; -- Deduct money from account_A UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A'; -- Create a savepoint SAVEPOINT before_transfer; -- Add money to account_B UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B'; -- Something goes wrong, rollback to the savepoint before transfer ROLLBACK TO SAVEPOINT before_transfer; -- If all is well, commit the transaction COMMIT;

In this example, if something goes wrong during the second operation (like adding money to account B), the system can roll back to the state right before the transfer operation.

Transaction Error Handling

Transactions should be used with proper error handling to ensure that they maintain the integrity of the database. For example:

START TRANSACTION; -- Perform multiple operations UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A'; UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B'; -- If an error occurs during any operation, rollback IF (SELECT balance FROM accounts WHERE account_id = 'A') < 0 THEN ROLLBACK; ELSE COMMIT; END IF;

This ensures that if any operation fails (e.g., insufficient funds), all changes made within the transaction will be rolled back.

Conclusion

  • Transactions in MySQL are a powerful way to ensure that a set of SQL queries are executed together as a single unit, maintaining the ACID properties.
  • You can start, commit, and rollback transactions using START TRANSACTION, COMMIT, and ROLLBACK.
  • Isolation levels control how transactions interact with each other, and MySQL offers four levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable).
  • Savepoints allow partial rollback within a transaction, offering greater flexibility.

By using transactions appropriately, you can ensure data consistency, integrity, and robustness in your MySQL databases.

Soeng Souy

Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

CAN FEEDBACK
close