MySQL transaction and storage engine comparison

InnoDB supports transactions, while MyISAM does not support transactions.

Definition of a transaction:

When multiple users access the same data, one user may change the data in the process of other users to initiate a change request, in order to ensure that the data updates from one consistent state to another consistent state, it is necessary to introduce the concept of transactions.

MySQL offers a variety of engines to support InnoDB and BDB. InnoDB Storage engine transactions are primarily implemented through the undo log and the redo log, and the MyISAM and memory engines do not support transactions. The differences and features of the three MySQL engines are given separately:

MyISAM Storage Engine: because the engine does not support transactions and does not support foreign keys, access is fast. Therefore, there is no requirement for transactional integrity and access-based applications are suitable for use with this storage engine.

InnoDB Storage Engine:

Because the storage engine has an advantage over transactions, which supports transactional installations with commit, rollback, and crash resiliency, it consumes more disk space than the MyISAM storage engine. As a result, frequent updates and deletions are required, while the integrity of the transaction requires a high level of concurrency control, which is suitable for use in this storage engine.

Memory Storage Engine:

The storage engine uses memory to store data, so the data access of the storage engine is very fast, but there is no security guarantee (Redis is also a memory storage engine). If the data involved in the application is small and requires quick access, it is appropriate to use the storage engine.

This article mainly introduces MySQL's transaction from the following four aspects:

1. Transaction overview
2. Transaction control Statements
3. Transaction ISOLATION LEVEL
4. InnoDB lock mechanism

Transaction Overview:

The transaction has acid four properties to ensure that the database is updated from one consistent state to another consistent state:

Atomicity (atomicity): All operations in a transaction are treated as an atomic unit, that is, data modifications to the firm can be either fully committed or completely rolled back.

Consistency (consistency): When a transaction is complete, all data must be changed from one consistency state to another, and all changes must be applied to the transaction's modifications to ensure the integrity of the data.

Isolation (insolation): The modification of an action statement in one thing must be isolated from the modifications made by other things. The state in which the data is located when the transaction is viewed, either in the state before the modification of another concurrent transaction, or after being modified by another concurrent transaction, that is, the current transaction does not view the data being modified by another concurrent transaction. This feature is mainly implemented by the lock mechanism.

Persistence (Durability): After a transaction is complete, the effect of the changes on the data is permanent, and the data can be recovered even if the system restarts or the system fails.

Redo logs and Undo logs:

Redo log:

Transaction execution requires the execution of the transaction log to be written to the log file, the corresponding file is the redo log. When each SQL statement makes a database update operation, the redo log is first written to the log buffer. When the client executes a commit, the contents of the log buffer are flushed to disk, the log buffer is refreshed, or the time interval can be controlled by the parameter innodb_flush_log_at_trx_commit.

The redo log corresponds to the Ib_logfilen (ib_logfile0,ib_logfile1) file on the disk, which defaults to 5MB and is recommended to be set to 512MB to accommodate larger transactions. Records in the Redo log are re-executed when the MySQL crash is resumed.

Undo log:

In contrast to the redo log, the undo log is primarily used for data rollback when a transaction exception occurs, in which the contents of the database before the transaction are copied to the undo buffer, and the content is flushed to disk at the appropriate time.

Unlike the redo log, there is no separate undo log file on the disk, and all of the undo log files are stored in the. IDB data file for the table space, and the undo log becomes the rollback segment.

MySQL Transaction control statement:

STARTTRANSACTION | BEGIN [ Work] //Open a transaction COMMIT [ Work] [and [NO]CHAIN][[NO]RELEASE]//Submit a transactionROLLBACK [ Work] [and [NO]CHAIN][[NO]RELEASE]//Rollback of a transactionSETAutocommit={0 | 1}//Auto COMMIT transaction on or off

To view the isolation level of a transaction:

 like ' tx_isolation ';

Example: A commit does not insert a table

The table will not change after rolling back

SET autocommit = 1;
When Autocommit is opened, we rollback and will automatically submit it.

MySQL Transaction ISOLATION LEVEL

The SQL standard defines four isolation levels, indicating which changes are visible to other transactions, and what changes other data is not visible. Lower levels of isolation can support higher concurrent processing while consuming less system resources.

To view the isolation level of a transaction:

'tx_isolation'; 

#未提交读, the following is the setting of the isolation level statement, changing the default isolation level and the need to log on againSETGLOBALTRANSACTION Isolation  Level READ Uncommitted; #提交读SETGLOBALTRANSACTION Isolation  Level READ COMMITTED; #可重复读SETGLOBALTRANSACTION Isolation  Level  Repeatable READ; #可串行化SETGLOBALTRANSACTION Isolation  Level SERIALIZABLE;

read-uncommitted (Read UNCOMMITTED content):

At this isolation level, all things can see the execution results of other uncommitted transactions. This isolation level is rarely used because its performance does not have to be much higher than other levels. Reading uncommitted data is called dirty reading.

Two things before the update, the data is consistent, but the left side of the transaction has not committed the changes, the right side of the transaction also read its change data, which is known as dirty read.

read_commited (read submissions):

This is the default isolation level for most databases, but not the default isolation level for MySQL. It satisfies the simple definition of isolation: Any changes made by a transaction from the beginning to the commit are not visible, and the transaction function sees the changes made by the committed firm.

This isolation level can cause non-repeatable read issues because other instances of the same transaction may have new data submissions causing data changes during the processing of the instance, so the same query may return different results at different times.

The following example: A transaction is not committed, but it sees different data. The data that you see two times in the same transaction is inconsistent.

Repeatable-read (can be reread):

This is the default isolation level for MySQL.

This isolation level guarantees that multiple instances of the same transaction will see the same rows of data while concurrently reading the data, theoretically causing another problem: Phantom reading.

Phantom reads: For example, transaction a modifies data in a table to involve all rows of data. At this point, transaction B inserts a row of data into the table. The problem with this is that there are data rows in the table that have not been modified by transaction A.

The InnoDB and Falcon storage engines address this issue through the MVCC (multi-versioning) mechanism.

The MVCC mechanism of the INNODB storage Engine: InnoDB is implemented by adding two implied values to each data row. These two implied values record the time the row was created and the expiration time. Each row stores the system version number at the time of the event, and each time a new thing starts, the version number is automatically added 1, and each object saves the version number at the beginning, and each query queries the result based on the version of the thing.

Serializable (Serializable):

This is the highest level of isolation, by forcing things to sort, making it impossible to conflict with each other, and thus solving the illusion of reading. In short, a shared lock implementation is added to each read data row. At this level, it can cause a lot of timeouts and lock competition, which is generally not recommended.

This isolation level is implemented with different lock types.

Implicit commit of a transaction: for example, ALTER TABLE causes an implicit transaction commit, although we do not commit the transaction, but we see changes in our data in other transactions.

Locking mechanism of InnoDB:

Type of Lock:

Shared locks:

A shared lock, also known as an S-lock, is an abbreviation for share, where the lock granularity of a shared lock is a row or tuple (multiple rows). After a transaction acquires a shared lock, it can perform a read operation on the data of the locked range.

Exclusive Lock:

The code of the lock is x, is the abbreviation of the exclusive, the lock granularity of the lock is also a row or tuple, a transaction acquires an exclusive lock, you can write to the data in the locked range.

Intent Lock:

Intent lock is a table lock, the granularity of the lock is the whole table, divided into the intent of shared lock (IS) and the intention of exclusive lock (IX) two categories. An intent lock indicates that a transaction intentionally shares a lock on the data or locks it. "Intentional" means that the transaction wants to perform the operation, but it is not actually executed.

Locks and locks are either compatible or mutually exclusive:

Size of Lock:

The size of the lock is divided into row and table locks. The smaller the granularity of the lock, the more system resources it consumes, but the concurrency is better.

The cost of table locks is minimal, while the allowable concurrency is also the smallest locking mechanism. MyISAM uses this locking mechanism.

Row locks Support maximum concurrency, the InnoDB storage engine uses this locking mechanism, and if concurrent reads and writes are supported, it is recommended to use the INNODB lock mechanism.

The SELECT statement adds a shared lock, and if the data you are looking for has been added to the lock, the shared lock waits for the end of the period, and if the wait time is too long, it shows the lock timeout that needs to wait.

Insert, update, and delete are added to the lock.

MySQL transaction and storage engine comparison