MySQL's business

Database Transactions (Database Transaction) refers to a series of operations performed as a single logical unit of work, either completely or completely out of execution.

Open transaction: Start transaction;
ROLLBACK TRANSACTION (if the transaction cannot be rolled back after commit): rollback;
Commit TRANSACTION: Commit;

4 major features of the transaction:
1. atomicity (automic): Multiple sets of operations in the same transaction cannot be split, must be a whole

2. Consistency (consistent): Consistent with the total amount of transaction operations before transaction operations

3. Isolation (isolation): non-interference between multiple transactions

There are 4 isolation levels of transactions in MySQL: READ UNCOMMITTED (Reading uncommitted transactions), Read committed (Read Committed transactions), REPEATABLE read (Repeatable read), serializable (serializable), (The level increases in order, and the higher the level, the greater the performance impact on the database)

View the transaction isolation level in the MySQL software: select @ @tx_isolation;

Modify the default isolation level for MySQL software: Set global transaction ISOLATION level isolation levels

SET GLOBAL TRANSACTION Isolation level READ COMMITTED;

　

　Different isolation levels can cause different problems:

When MySQL's transaction isolation level is read UNCOMMITTED, it causes dirty reads : One transaction can read uncommitted data from another transaction.

WORKAROUND: Change the transaction isolation level to read Committed,set GLOBAL TRANSACTION isolation Levels Read Committed;

When the transaction isolation level of the MySQL software is read committed, non- repeatable reads are thrown: the results of multiple reads in the same thing are inconsistent.

WORKAROUND: Change the transaction isolation level to repeatable read,SET GLOBAL TRANSACTION isolation levels repeatable read;

Instead of repeatable read, if two transactions are performed concurrently, one transaction changes the data, and the data result of the other transaction query does not affect, does not change, and remains the data before the change.

When the transaction isolation level of the MySQL software is repeatable read, it causes a virtual read (Phantom Read):

4. Persistence (Durable): Once the data is entered into the library, the table is permanently present

MySQL transaction