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
- 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.
- Consistency: A transaction ensures that the database transitions from one valid state to another, maintaining database integrity.
- 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.
- 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
orBEGIN
: 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
If something goes wrong during the transaction and you need to undo 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.
If something goes wrong (e.g., insufficient funds), you can undo the transaction:
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.
This command sets the isolation level for the next transaction. To view the current isolation level:
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:
Example with Savepoints:
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:
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
, andROLLBACK
. - 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.