This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

The environment version used for this article’s operations and tests is 5.7.21

mysql> select version(); + -- -- -- -- -- -- -- -- -- -- -- + | version () | + -- -- -- -- -- -- -- -- -- -- -- + | 5.7.21 | + -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

Remember: InnoDB is the only one of our common MySQL storage engines that supports transactions. So the following operations are also done under InnoDB.

I. What is a transaction

A transaction is a logical operation abstracted from reality. It can either be executed or not executed. There can be no partial execution.

The classic case is bank transfer: Little A transfers 100 yuan to little B

Normal situation: the account of little A deducted 100 yuan, the account of little B increased 100 yuan.

Abnormal situation: 100 yuan will be deducted from small A’s account, while the amount of small B’s account remains unchanged.

Under normal circumstances, there is A problem in the banking system after the deduction of 100 yuan from small A account, and the operation of adding 100 yuan to small B account is not executed. That is, the two sides of the amount is not on, small A is not willing, small B is not willing, the bank is not willing to ah. Transactions exist to avoid abnormal situations and keep everyone happy.

Four properties of Transactions (ACID)

1. Atomicity

The operations of a transaction are indivisible, either all or none, and just like a transfer, there is no intermediate state. And this atomicity does not mean that there is only one action, there may be many operations, but the result is indivisible, that is to say, the atomicity is a result state.

2. Consistency

The data is consistent before and after the transaction, just like in a bank account system the total number of accounts should be the same whether the transaction is successful or not.

3. Isolation

When multiple transactions operate on data at the same time, the transactions are directly isolated from each other and do not affect each other.

4. Durability

The impact of a transaction on data after it commits is permanent and will not be lost when written to disk.

Explicit transaction, implicit transaction

Mysql transactions are divided into explicit transactions and implicit transactions. The default transaction is implicit, and the variable autocommit is automatically enabled, committed, and rolled back during the operation.

The key commands for control are as follows

set autocommit=0; Set autocommit=1; -- Enable automatic commit transactions (implicit) -- Manually rollback transactions when autoCOMMIT =0; -- Rollback transaction commit; -- Commit transaction -- Commit transaction automatically when autoCOMMIT =1, but can control manual commit start transaction; -- Start a transaction (or start a transaction with begin) commit; -- Commit transaction ROLLBACK; -- Rollback transaction SAVEPOINT name; Rollback to rollback to rollback -- Rollback to a savepointCopy the code

Let’s create a table ajisun

mysql> create table ajisun(id int(5), name varchar(20) character set utf8  COLLATE utf8_bin ) engine=innodb character set= utf8mb4 COLLATE = utf8mb4_bin;
Query OK, 0 rows affected (0.03 sec)
Copy the code

1. Implicit transactions

Mysql > show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 row in data set (0.01 SEC) - insert a mysql > insert into ajisun values (1, 'o ji'); Query OK, 1 row affected (0.00 SEC) mysql> rollback; Mysql > select * from ajisun; mysql> select * from ajisun; + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | | id name | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | 1 | o ji | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

2. Explicit transaction mode 1

Explicit transactions allow us to start, commit, roll back, and so on

Mysql > set autocommit=0; Query OK, 0 rows affected (0.00 SEC) mysql> select * from ajisun; + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | | id name | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | 1 | o ji | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > insert Into ajisun values(2,' 03 '); Query OK, 1 row affected (0.00 SEC) mysql> select * from ajisun; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id name | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | 1 | o ji | | 2 | | + -- -- -- -- -- - Mr Ji + -- -- -- -- -- -- -- -- -- -- - + 2 rows in the set (0.00) Mysql > rollback; Query OK, 0 rows affected (0.00 SEC) mysql> select * from ajisun; + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | | id name | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | 1 | o ji | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code
Mysql > insert into ajisun values(2,'ajisun'); Query OK, 1 row affected (0.01sec) -- mysql> commit; Query OK, 0 rows affected (0.00 SEC) -- rollback; Query OK, 0 rows affected (0.00 SEC) -- Commit commit; rollback data still exists; mysql> select * from ajisun; + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | | id name | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | 1 | o ji | | 2 | ajisun | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

3. Explicit transaction mode 2

Use start transaction

Set autocommit=1;

Mysql > start transaction; Query OK, 0 rows affected (0.00 SEC) mysql> delete from ajisun where id=1; Query OK, 1 row affected (0.00 SEC) -- mysql> commit; Query OK, 0 rows affected (0.01sec) mysql> select * from ajisun; + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | | id name | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | 2 | ajisun | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code
Mysql > start transaction; Query OK, 0 rows affected (0.00 SEC) mysql> delete from ajisun where id =2; Query OK, 1 row affected (0.01sec) -- rollback mysql> rollback; Query OK, 0 rows affected (0.01sec) mysql> select * from ajisun; + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | | id name | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | 2 | ajisun | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Problems in concurrent transactions

This would not be a problem if the operation on the table had only one transaction at a time, but this is not possible. In reality, it is possible to utilize multiple transactions simultaneously. Multiple transactions can cause a lot of problems, such as dirty reads, dirty writes, ‘unrepeatable reads, phantom reads

1. Dirty reads

A dirty read is when a transaction reads data modified by another uncommitted transaction.

For example, two transactions A, B: operate on a record at the same time

After a transaction changes the record, it is not formally committed to the database. Then B will read the record and use the read data for subsequent operations.

If transaction A rolls back and the modified data does not exist, then transaction B is using data that does not exist. This is dirty data.

2. Dirty write (data loss)

One transaction modifies data modified by another uncommitted transaction

For example, two transactions A, B: operate on a record at the same time

Transaction A modifies and does not commit, then transaction B modifies the same data, and then transaction B commits.

If a transaction rolls back its changes and b’s changes are also rolled back, the problem is that b’s changes are committed but the database does not change, which is a dirty write.

3. Do not repeat the read

A transaction can only read data that has been modified by another committed transaction, and the transaction can query for the latest value each time the data has been modified and committed by another transaction.

A non-repeatable read is one that reads the same record multiple times in the same transaction and gets different results (other transactions complete and commit changes before each read).

4. The magic to read

In a transaction, the number of records queried under the same conditions is different

That is, one transaction first queries some records according to certain conditions, and then another transaction inserts records that meet these conditions into the table. When the original transaction queries according to these conditions, the records inserted by the other transaction can also be read out, which means that a phantom read has occurred

The difference between unrepeatable read and illusory read: The difference between unrepeatable read and illusory read is that the data value is different before and after the same record (content change), while the difference between illusory read is that the number of records obtained before and after the same query condition is different (number change).

V. Isolation level of transactions

The concurrency of transactions mentioned above varies in different scenarios, and the acceptable problems vary. The order of severity is as follows:

Dirty write > Dirty read > Unrepeatable read > Phantom read

There are four isolation levels provided in MySQL to handle these issues, as follows

Isolation level Dirty read Unrepeatable read The phantom read
READ- UNCOMMITTED Square root Square root Square root
READ-COMMITTED x Square root Square root
REPEATABLE-READ x x Square root
SERIALIZABLE x x x

The SQL standard defines four isolation levels:

  • Read-uncommitted: The lowest isolation level that allows UNCOMMITTED data changes to be READ, which can resultDirty read,Unrepeatable readandPhantom read. But the concurrency is the highest
  • Read-committed: Allows concurrent transactions to READ COMMITTED data, preventing dirty reads, butPhantom readandUnrepeatable readIt could still happen.
  • REPEATABLE-READ: Multiple reads of the same field are consistent, unless the data is modified by the transaction itself, which can be preventedDirty readandUnrepeatable read, butPhantom readIt could still happen.
  • SERIALIZABLE: Highest isolation level, fully subject to ACID isolation level. All transactions are executed one by one so that interference between transactions is completely impossible, and this level prevents itDirty read,Unrepeatable readAs well asPhantom read. It also has the lowest concurrency
REPEATABLE_READ Isolation level Used by MySQL Default READ_COMMITTED isolation level used by Oracle defaultCopy the code

1. How do I set the isolation level

You can view the isolation level using the variable parameter transaction_isolation

mysql> SELECT @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > show variables like '% transaction_isolation %'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | Transaction_isolation | the REPEATABLE - READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.02 SEC)Copy the code

Modify command: SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL $[LEVEL];

The value of level is READ-UNCOMMITTED read-committed REPEATABLE-READ SERIALIZABLE

Sets the global isolation level

This applies only to sessions that occur after the statement is executed.

The existing session is invalid.

set global transaction_isolation='read-uncommitted';
set global transaction_isolation='read-committed';
set global transaction_isolation='repeatable-read';
set global transaction_isolation='serializable';
Copy the code

Such as:

The session A

mysql> set global transaction_isolation='serializable'; Query OK, 0 rows affected (0.01sec) mysql> select @@global.transaction_isolation; +--------------------------------+ | @@global.transaction_isolation | +--------------------------------+ | SERIALIZABLE | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC), the current session (set existed before, Mysql > select @@transaction_ISOLATION; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Session B(new session after set)

mysql> select @@global.transaction_isolation; +--------------------------------+ | @@global.transaction_isolation | +--------------------------------+ | SERIALIZABLE | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > select @ @ transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | SERIALIZABLE | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Sets the isolation level of the session

Valid for all subsequent transactions of the current session

This statement can be executed in the middle of an already opened transaction, but does not affect the currently executing transaction.

If executed between transactions, it is valid for subsequent transactions.

set session transaction_isolation='read-uncommitted';
set session transaction_isolation='read-committed';
set session transaction_isolation='repeatable-read';
set session transaction_isolation='serializable';
Copy the code

Such as:

The session A

mysql> set session transaction_isolation='read-uncommitted'; Query OK, 0 rows affected (0.00 SEC) mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | READ-UNCOMMITTED | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Create a new session B(still the default level: repeatable)

mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

2. How do I choose the isolation level

In general, the default repeatable read is fine, and this is rarely changed unless the business scenario is specific

Keep in mind that the higher the isolation level, the fewer concurrency problems, but also the lower the concurrency, so it’s still a matter of business choice.

6. Summary

  1. Four characteristics of transactions: atomicity, consistency, isolation, and persistence

  2. Common commands for transactions:

    set autocommit=0/1; -- Turn off/on automatic transaction start transaction; -- Start transactions (or begin) rollback; -- Rollback transaction commit; Commit transactionCopy the code

  3. Problems with concurrent transactions: dirty writes > dirty reads > Unrepeatable reads > phantom reads

  4. You need to be familiar with the four isolation levels for transactions as well as the MySQL default level

  5. How to set isolation level (global, session)

If you have any questions, please communicate and learn from each other.