MySQL's business

When multiple users access the same data, one user may change the data while another user initiates a change request, introducing a transaction to ensure that the data changes from one consistency state to another.

MySQL provides a variety of storage engine support transactions, and the storage engine that supports transactions has InnoDB and BDB. InnoDB storage engine transactions are implemented primarily through the undo log and the redo log. The MyISAM and memory storage engines do not support transactions.

1. Transaction overview Four features of a transaction:

A. Atomicity (atomicity): All operations in a transaction are treated as an atomic unit, that is, the manipulation of data modified by the firm can only be fully committed or completely rolled back.

B. 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 modification of the transaction to ensure the integrity of the data.

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

D. Persistence (durability): After the transaction is complete, the effect of the modification on the data is permanent, even if the system restarts, or the system failure data can be recovered.

MySQL provides a variety of transactional storage engine support, such as InnoDB and BDB, while MyISAM does not support transactions, InnoDB supports acid transactions, row-level locks, and high concurrency. To support transactions, the InnoDB storage engine introduces transaction-related undo and redo logs, while transactions rely on the lock mechanism provided by MySQL.

1.1 Redo Log

Transaction execution requires the execution of the transaction log to be written to the log file, the corresponding log file is the redo log. When each SQL 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 innidb_flush_log_at_trx_commit.

The redo log corresponds to the Ib_logfilen file on 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.

1.2 Undo Log

The undo log, in contrast to the redo log, is used primarily 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 logs are stored in the. IBD data file for the table space, even if the MySQL service has a standalone table space enabled. The Undo log is also known as a rollback segment.

2. MySQL Transaction control statements

The transaction begin transaction is used in MySQL, the transaction ommit ends the transaction, and the transaction can be rolled back using rollback. MySQL supports local transactions through statements such as set Autocommit,start transaction,commit and rollback. The syntax is as follows:

STARTTRANSACTION | BEGIN [ Work]COMMIT [ Work] [and [NO]CHAIN][[NO]RELEASE]ROLLBACK [ Work] [and [NO]CHAIN][[NO]RELEASE]SETAutocommit={0 | 1}

In the default settings, transactions in MySQL are committed by default. It is convenient to start a transaction using start transaction or BEGIN IF transaction control is required for some statements. This will automatically return to auto-commit after the transaction has ended.

The following example shows a simple transaction that updates a record in a table to ensure that the data is updated from one consistency state to another, so the transaction completes the update process and can be used for rollback if the update fails or for other reasons. When this instance executes, the corresponding MySQL default isolation level is Repeatable-read.

#查看MySQL隔离级别mysql>Show variables like 'tx_isolation';+---------------+-----------------+|Variable_name|Value|+---------------+-----------------+|Tx_isolation| Repeatable-READ |+---------------+-----------------+1Rowinch Set(0.33sec) #创建测试需要的表, the primary storage engine is Innobmysql>  UseCompany ;DatabaseChangedmysql> Create TableTest_1 (IDint, usernamevarchar( -)) engine=InnoDB; Query OK,0Rows Affected (0.67sec) MySQL> Insert  intoTest_1Values(1,'Petter'),(2,'Bob'),(3,'Allen'),(4,'Aron'); Query OK,4Rows Affected (0.17sec) Records:4Duplicates:0Warnings:0MySQL> Select *  fromtest_1;+------+----------+|Id|Username|+------+----------+|    1 |Petter||    2 |Bob||    3 |Allen||    4 |Aron|+------+----------+4Rowsinch Set(0.00sec) #开启一个事务mysql> begin; Query OK,0Rows Affected (0.02sec) #更新一条记录mysql> UpdateTest_1SetUsername='Test' whereId= 1; Query OK,1Row affected (0.06sec) Rows matched:1Changed:1Warnings:0#提交事务mysql> Commit; Query OK,0Rows Affected (0.06sec) #发现记录已经更改生效mysql> Select *  fromtest_1;+------+----------+|Id|Username|+------+----------+|    1 |Test||    2 |Bob||    3 |Allen||    4 |Aron|+------+----------+4Rowsinch Set(0.00sec) #开启另外一个事务mysql> begin; Query OK,0Rows Affected (0.00sec) MySQL> UpdateTest_1SetUsername='Petter' whereId= 1; Query OK,1Row affected (0.00sec) Rows matched:1Changed:1Warnings:0MySQL> Select *  fromtest_1;+------+----------+|Id|Username|+------+----------+|    1 |Petter||    2 |Bob||    3 |Allen||    4 |Aron|+------+----------+4Rowsinch Set(0.00sec) #回滚事务mysql> rollback; Query OK,0Rows Affected (0.13sec) #此时发现数据已经回滚mysql> Select *  fromtest_1;+------+----------+|Id|Username|+------+----------+|    1 |Test||    2 |Bob||    3 |Allen||    4 |Aron|+------+----------+4Rowsinch Set(0.00Sec

View Code

3. mysql Transaction ISOLATION LEVEL

The SQL standard defines four isolation levels, specifying which data in a transaction changes the visibility of other transactions, what changes other transactions are not visible, and low-level isolation levels that can support higher concurrent processing while consuming less system resources.

To view the MySQL isolation level:

 like ' tx_isolation ';

The transaction isolation level can be set using the following statement:

#未提交读SETGLOBALTRANSACTION Isolation  Level READ Uncommitted; #提交读SETGLOBALTRANSACTION Isolation  Level READ COMMITTED; #可重复读SETGLOBALTRANSACTION Isolation  Level  Repeatable READ; #可串行化SETGLOBALTRANSACTION Isolation  Level SERIALIZABLE;

3.1 read-uncommitted (Read UNCOMMITTED content)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. Because the performance is not much higher than the other levels, this isolation level is rarely used in practical applications, and reading uncommitted data is called dirty Read (Dirty read).

The dirty read example is as follows:

3.2 read-committed (read submissions)

This is the default isolation level for most database systems, but not the default isolation level for MySQL. It satisfies the simple definition of isolation, and any changes made by a transaction from the beginning to the commit are not visible, and the transaction can only see the changes that have been committed by the firm. This isolation level also supports so-called non-repeatable reads (nonrepeatable read), because other instances of the same transaction may have new data submissions causing data changes during the instance processing, so the same query may return different results. The non-repeatable reads caused by this level are as follows:

3.3 repeatable -read (can be reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data. This can theoretically lead to phantom reads (Phantom read). For example, the first transaction modifies the data in a table, which involves all rows of data in the table. The second transaction also modifies the data in the table by inserting a new row of data into the table. Then, the user who operates the first transaction in the future will discover that there are no modified rows of data in the table. The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (multi_version Concurrency CONTROL,MVCC) mechanism.

InnoDB Storage engine MVCC mechanism:

3.3 SERIALIZABLE (serializable)

MySQL's business