- A transaction ensures that a group of database operations are all successful or all fail. In MySQL, transaction support is implemented at the engine layer.
- MySQL is a multi-engine system, but not all engines support transactions. For example, MySQL's native MyISAM engine does not support transactions, which is one of the main reasons why MyISAM is being replaced by InnoDB.
- ACID (Atomicity, Consistency, Isolation, Durability)
Isolation and Isolation Levels
When there are multiple transactions executing on the database at the same time, problems such as dirty reads, non-repeatable reads, and phantom reads may occur. To solve these problems, the concept of "isolation level" is introduced. The more strict the isolation, the lower the efficiency.
The standard SQL transaction isolation levels include: read uncommitted, read committed, repeatable read, and serializable.
- Read uncommitted means that changes made by a transaction can be seen by other transactions before it is committed.
- Read committed means that changes made by a transaction can be seen by other transactions only after it is committed.
- Repeatable read means that the data seen by a transaction is always consistent with the data it saw when it started. Of course, in the repeatable read isolation level, uncommitted changes are also invisible to other transactions.
- Serialization, as the name implies, for the same row record, "write" will add "write lock", "read" will add "read lock". When a read-write lock conflict occurs, the later accessed transaction must wait for the previous transaction to complete before it can proceed.
The behavior of the database is different under different isolation levels. For example, Oracle's default isolation level is actually "read committed". Therefore, for some applications migrating from Oracle to MySQL, in order to ensure the consistency of the database isolation level, the MySQL isolation level is set to "read committed".
The way to configure this is to set the value of the startup parameter transaction-isolation to READ-COMMITTED.
Repeatable Read
In MySQL, every record update will also record a rollback operation at the same time. The latest value of the record, through the rollback operation, can get the value of the previous state.
If a value is changed from 1 to 2, 3, 4 in sequence, the rollback log will have records like the following.
When there is no read-view earlier than this rollback log in the system, the rollback log will be deleted.
Long transactions mean that there are very old transaction views in the system. In MySQL 5.5 and earlier versions, the rollback log is placed in the ibdata file along with the data dictionary. Even if the long transaction eventually commits and the rollback segment is cleared, the file will not shrink. In addition to the impact on the rollback segment, long transactions also occupy lock resources and may drag down the entire library.
Transaction Startup Method
There are several ways to start a transaction in MySQL:
- Explicitly start a transaction statement, begin or start transaction. The matching commit statement is commit, and the rollback statement is rollback.
- set autocommit=0, this command will turn off this thread's automatic commit. This means that if you only execute a select statement, this transaction will start and will not automatically commit. This transaction lasts until you execute commit or rollback statement actively, or disconnect.
In the case where autocommit is 1, the transaction explicitly started with begin, if execute commit then it will commit the transaction. If execute commit work and chain, it will commit the transaction and automatically start the next transaction, which also saves the overhead of executing the begin statement again. The benefit it brings is to clearly know whether each statement is in a transaction from the perspective of program development.
