MySQL provides two transactional storage engines: InnoDB and NDB Cluster. There are also third-party storage engines that support transactions

1. Database transactions

A database transaction is a set of data operations within which all operations either succeed or fail.

For example, in the transfer process, zhang SAN transfers 2000 to Li Si. In the first step, 2000 is deducted from the bill account, and in the second step, 2000 is added to Li Si’s account. These two steps can be regarded as one transaction. If both steps succeed, the transfer succeeds. If either step fails, the transfer operation is withdrawn

Transactions have Atomicity, Consistency, Isolation and Durability (ACID for short).

The main thing here is isolation

2. Possible problems of transaction concurrency

Why should there be an isolation level, mainly to prevent a series of problems in transaction concurrency

Suppose our table structure and data are as follows:

create table movie (
   id int unsigned not null auto_increment primary key,
   name varchar(30) not null comment 'Movie Title',
   price int not null comment 'tickets'
)ENGINE=InnoDB comment='movie';

INSERT INTO movie (id, name, price) VALUES (1.Don 'agent 3'.70);
INSERT INTO movie (id, name, price) VALUES (2.'Hello, Li Huanying'.60);
Copy the code

2.1 dirty read

Dirty read refers to reading data that has not been committed by another transaction. Uncommitted data in a transaction may be temporary data or may need to be rolled back and may not be stored in the database itself, which is called dirty reads.

Before the business started, the price of Tang Tan 3 was 70 yuan.

Transaction 1 starts the transaction first, and then transaction 2 starts the transaction. Because transaction 2 processes the data faster, the price is changed to 55 yuan first. At this time, the transaction has not committed or rolled back, so transaction 1 needs to read this data

2.2 Unrepeatable read

Not repeatable read means within the same transaction, different time to read the same data may not be the same, may be affected by other transactions, such as in the read data and second data for the first time, among other issues also to modify the data, lead to the same transaction on the same data inconsistent reads. This usually happens with data UPDATE operations.

2.3 phantom read

Phantom reading is when a transaction queries the same range twice before and after, and the latter query sees rows not seen by the previous query.

3. Transaction isolation level

The SQL standard defines four isolation levels, all of which are supported by MySQL. The four isolation levels are:

1. READ UNCOMMITTED
2. READ COMMITTED
3. REPEATABLE READ
4. SERIALIZABLE

Each isolation level addresses the corresponding problem

You can see from this table that the higher the isolation level, the more problems are solved, and the higher the level, the greater the impact on concurrency performance

READ UNCOMMITTED, nothing is resolved, and I’m not talking about that isolation level here, okay

3.1 How does READ COMMITTED work

Read commit resolves the dirty read problem

The first 6 steps are the same as those in the isolation level of the commit statement: However, since the data has been found to have been modified in transaction 2, the query can not be performed until the completion of transaction 2 (commit or rollback). Therefore, the steps 4 and 5 of transaction 1 can not be performed until the completion of transaction 6 in transaction 2

3.2 REPEATABLE READ

Repeatable reads solve the problem of dirty and unrepeatable reads

Update (id = 1); update (id = 1);

A: Because InnoDB has MVCC (multi-version concurrency control), you can use snapshot reads without blocking.

3.3 SERIALIZABLE

This pattern is relatively simple, it requires serialized transaction execution, the transaction can only be executed one after another, not concurrently, to solve the dirty read, unrepeatable read and phantom read, of course, the worst performance.

4. Multi-version Concurrency Control (MVCC)

Most of MySQL’s transactional storage engines are not simple row-level locks. They generally implement multiple versions of concurrency control MVCC at the same time for promotion and release purposes.

4.1 the InnoDB MVCC

InnoDB’s MVCC is implemented by storing two hidden columns at the end of each row. These two columns hold the creation time of the row and the expiration (or deletion) time of the row, but not the actual time, but the system version number. Each time a transaction is started, the system version number increases automatically. The version number of the transaction start time is used as the version number of the current transaction, which is compared with the version number of each row of records queried.

The specific operation of REPEATABLE READ isolation level is as follows:

• SELECT

  InnoDB checks each row based on two criteria:

  A. InnoDB only queries rows whose version is earlier than the current transaction version (i.e., rows whose system version number is less than or equal to the transaction’s system version number). This ensures that the transaction reads rows that either existed before the transaction started or were inserted or modified by the transaction itself. B. The deleted version of the row is either undefined or greater than the current transaction version number. This ensures that rows read by the transaction are not deleted before the transaction begins.

  Only records that meet the preceding two conditions can be returned as the query result

• INSERT

  InnoDB stores the current system version number as the row version number for each newly inserted row

• DELETE

  InnoDB stores the current system version number as a row deletion identifier for each row that is deleted

• UPDATE InnoDB Inserts a new row, saves the current system version number as the row version number, and saves the current system version number to the original row as the row delete mark

Two additional system version numbers are kept, enabling most read operations to be unlocked. This design makes reading data simple, performs well, and guarantees that only rows that meet the criteria will be read

5. Transaction issues under hybrid storage engines

MySQL does not manage transactions at the service layer and transactions are implemented by the underlying storage engine, so it is not reliable to process the same transaction in different storage engines. For example: Table_A uses innoDB engine, table_B uses MyISAM engine (does not support transactions) suppose the following operations are in a transaction:

insert table_a ...    # 1
insert table_b ...    # 2
update table_a ...    # 3
Copy the code

If an exception occurs during execution #3, the transaction will be rolled back because Table_B does not support transactions. This will cause table_B’s changes to be unable to be rolled back, which will violate the consistency and atomicity of the transaction

6. View and set the isolation level

6.1 Viewing the Isolation Level

View the current isolation level

One - way
show variables like '%tx_isolation%';

Two - way
select @@tx_isolation;
Copy the code

View the isolation level of the session

SELECT @@session.tx_isolation;
Copy the code

View global isolation levels

SELECT @@global.tx_isolation;
Copy the code

In MySQL 8.0.3, tx_ISOLATION was replaced with transaction_isolation, and tx_isolation was replaced with transaction_isolation

6.2 Changing the Isolation Level

MySQL provides the SET TRANSACTION statement, which can change the TRANSACTION isolation level for a single session or globally. The syntax is as follows:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

The SESSION and GLOBAL keywords are used to specify the scope of the modified transaction isolation level:

• SESSION: indicates that the modified transaction isolation level will be applied to all transactions in the current SESSION (current CMD window);
• GLOBAL: indicates that the modified transaction isolation level will be applied to all transactions in all sessions (globally) and existing sessions will not be affected.
• If SESSION and GLOBAL are omitted, the modified transaction isolation level will apply to the next transaction within the current SESSION that has not yet started.

Any user can change the transaction isolation level of the session, but only users with SUPER permission can change the global transaction isolation level.

6.3 Changing the isolation level of the current connection for JDBC

SetTransactionIsolation () is provided in Connection to change the isolation level of the current connection as follows:

connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
Copy the code