A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back.
Using mysql transactions
MySQL supports several storage engines. The InnoDB is fully ACID compliant. The ACID stands for Atomicity, Consistency, Isolation and Durability. Reliable transactions must support all these four properties.
The ACID Properties
The MySQL transaction architecture fully satisfies the ACID tests for transaction safety via its InnoDB storage engine. Older table types, such as the MyISAM type, do not support transactions. Transactions in such systems, therefore, can only be implemented through the use of explicit table locks (although this may not be ACID-compliant).
The term “ACID” is an acronym, stating four properties that every transactional RDBMS must comply with. To qualify for ACID compliance, an RDBMS must exhibit the following characteristics, as described in the following sections.
- Atomicity means that every transaction must be treated as an indivisible unit. Given a transaction consisting of two or more tasks, all the statements within it must be successful for the transaction to be considered successful. In the event of a transaction failure, the system should be returned to its pre-transaction state.
- Consistency means that every transaction must ensure that the database is in a consistent state once it completes executing. Or, to put it another way, consistency means that the database must never reflect a partially completed transaction at any time.
- Isolation means that every transaction must occur in its own separate and independent “transaction space,” and its impact on the database only becomes visible once the transaction has completed executing (regardless of whether the transaction was successful or not). This is particularly important in multiuser, multitransaction systems, because it implies that the effects of a particular transaction are not “felt” until the transaction is complete. In the absence of the isolation property, two conflicting transactions might quickly produce data corruption, because each transaction would violate the other’s integrity.
- Durability means that changes made by a successful transaction will not be lost, even if the system crashes. Most RDBMS products ensure data durability by keeping a log of all activity that alters data in the database in any way. This database log keeps track of any and all updates made to tables, queries, reports, and so on. In the event of a system crash or a corruption of the data storage media, the system is able to recover to the last successful update on restart and reflect the changes carried out by transactions that were still in progress when it went down through the use of its logs.
Caution The ALTER TABLE command works by backing up the data in the table, erasing it, re-creating it with the specified modifications, and then reinserting the backed-up records. A failure in any of these steps could result in the loss or corruption of your data. Therefore, a good idea is always to create a table backup prior to using the ALTER TABLE command.
Operations within a transaction must be atomic. This means, that either all operations succeed or fail. This is all or nothing rule. The consistency property ensures that the database is in a consistent state after the transaction is finished. The data is valid and there are no half-finished records. For example there are no customers left with no payment records or there are no payment records without customers. Isolation is the requirement that other operations cannot access data that has been modified during a transaction that has not yet completed. The question of isolation occurs in case of concurrent transactions. Without isolation, the data may end up in inconsistent state. Durability is the ability of the database system to recover the committed transaction updates against any kind of system failure.
In a highly concurrent environment, highly isolated transactions may lead to deadlocks. A deadlock is a situation, where transactions compete over resources and effectively prevent each other from accessing the resource. Furthermore there is a tradeoff between isolation level and performance of the database. Therefore, database systems offer several levels of isolation for transactions.
MySQL offers four levels of transaction isolation:
- Repeatable read
- Read committed
- Read uncommitted
In the serializable isolation level all transactions occur in a completely isolated fashion. All transactions are executed one after the other.
In a repeatable read isolation level statements cannot read data that has been modified but not yet committed by other transactions. No other transactions can modify data that has been read by the current transaction until the current transaction completes. It is the default isolation level for InnoDB.
In a read committed isolation level statements cannot read data that has been modified but not committed by other transactions. Statements wait until rows of data that are write-locked by other transactions are unlocked before they acquire their own locks. This prevents them from reading dirty data.
In a read uncommitted isolation level, statements can read rows that have been modified by other transactions but not yet committed.
Phantom reads, non-repeatable reads and dirty reads are issues, which can be encountered, when the transactions are not completely separated. A phantom read occurs when a transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction. A non-repeatable read occurs when a transaction re-reads data it has previously read and finds that data has been modified by another transaction. That committed since the initial read. A dirty read occurs when a transaction reads data from a row that has been modified by another transaction, but not yet committed.
The default transaction isolation level for MySQL is repeatable read.
SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | SERIALIZABLE | +----------------+
We can change the isolation level with the SET TRANSACTION ISOLATION LEVEL statement.
MySQL also automatically commits statements that are not part of a transaction. The results of any UPDATE or INSERT statement not preceded with a START will immediately be visible to all connections.
SELECT @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+
The autocommit variable is set by default.
SET autocommit=0; SELECT @@autocommit; +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+
The autocommit can be turned off.
Now we are going to demonstrate the autocommint variable.
SELECT @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ CREATE TABLE Test(Num INTEGER NOT NULL) engine=InnoDB;
With autocommit enabled, each single SQL statement is wrapped automatically in its own transaction. To start our own transaction, we issue the START TRANSACTION statement. The transaction is later finished with the COMMIT or ROLLBACK statements. Multiple statements may be issued in the body of the transaction. All are either committed or rolled back as one unit.
The InnoDB storage engine supports an additional useful feature: the ability to roll back a transaction partially instead of completely. This is accomplished through the use of savepoints—user-defined points that can be used to mark substages within a transaction. In the event of a transaction failure, these savepoints make it possible to roll back only specific parts of a transaction rather than the entire transaction. Savepoints within a transaction are set with the SAVEPOINT command, which accepts a user-defined identifier. The ROLLBACK TO SAVEPOINT command can then be used to roll an in-progress transaction back to the named savepoint, reversing all changes made after the savepoint.
START TRANSACTION; INSERT INTO flight (FlightID, RouteID, AircraftID) VALUES (834, 1061, 3469); SAVEPOINT flight1; INSERT INTO flightdep (FlightID, DepDay, DepTime) VALUES (834, 4, '16:00'); SAVEPOINT flight2; INSERT INTO flightclass (FlightID, ClassID, MaxSeats, BasePrice) VALUES (834, 'A', 20, 200); SAVEPOINT flight3;
ROLLBACK TO SAVEPOINT flight2;
Issuing a ROLLBACK TO SAVEPOINT command doesn’t commit or roll back the transaction. Conclude the transaction by rolling back all the remaining changes.
There are some important things to learn about savepoints:
- Multiple savepoints can be set per transaction, so long as they each have a unique identifier. Repeating an identifier overwrites previously set savepoints with the same identifier.
- Rolling back to a savepoint does not end the transaction. To end the transaction, use the COMMIT or ROLLBACK commands. However, rolling back to a specified savepoint deletes all savepoints set after that point. If the savepoint specified in the ROLLBACK TO SAVEPOINT command does not exist, MySQL will generate an error.
- A savepoint can be removed using the RELEASE SAVEPOINT command, which accepts a savepoint identifier and removes that savepoint from the stack. Note that this command does not perform an implicit COMMIT or ROLLBACK, so the transaction remains in progress until an explicit COMMIT or ROLLBACK is issued.