Shulou(Shulou.com)05/31 Report--

This article focuses on "transactions in MySQL, 4 major features, what is the isolation level", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "MySQL transactions, four major features, what is the isolation level" bar!

The environment version used for operation and testing in this article is 5.7.21.

Mysql > select version (); +-+ | version () | +-+ | 5.7.21 | +-+ 1 row in set (0.00 sec)

Remember: in our common MySQL storage engine, only InnoDB supports transactions. So the following operations are also done under InnoDB.

one。 What is a transaction?

Transaction is a logical operation abstracted from reality, which is either executed or not executed, and there can be no partial execution.

A more classic case is bank transfer: a transfer of 100 yuan from Xiao A to Xiao B.

Normal situation: Xiao A's account is deducted by 100 yuan, Xiao B's account is increased by 100 yuan.

Abnormal situation: the account of Xiao An is deducted by 100 yuan, and the amount of Xiao B account remains the same.

Under abnormal circumstances, there is a problem with the banking system after the deduction of 100 yuan from the small An account, and the increase of 100 yuan from the small B account has not been carried out. In other words, the amounts on both sides do not match, Xiao An is not willing, Xiao B is not willing, and the bank is not willing either. The emergence of affairs is to avoid the occurrence of abnormal situations and make everyone satisfied.

two。 Four characteristics of transactions (ACID)

1. Atomicity (Atomicity)

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

two。 Consistency (Consistency)

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

3. Isolation (Isolation)

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

4. Persistence (Durability)

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

three。 Explicit transaction, implicit transaction

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

The key commands for control are as follows

Set autocommit=0;-turn off autocommit transaction (explicit) set autocommit=1;-enable autocommit transaction (implicit)-manually control transaction rollback; when autocommit=0-rollback transaction commit;-commit transaction-when autocommit=1 automatically commit transaction, but you can control manual commit start transaction;-open transaction (or open transaction with begin) commit;-commit transaction rollback -- rollback transaction SAVEPOINT SavePoint name;-- SavePoint (equivalent to archiving, so you don't have to roll back all operations) rollback to SavePoint;-- rollback to a SavePoint (you don't test this later, just know that you have this operation)

Build a table first, 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)

1. Implicit transaction

Take a look at the current autocommit status. The default is on status mysql > show variables like 'autocommit'. +-+-+ | Variable_name | Value | +-+-+ | autocommit | ON | +-+-+ 1 row in set (0.01sec)-insert a piece of data mysql > insert into ajisun values; Query OK, 1 row affected (0.00 sec) mysql > rollback -- it is not effective to execute rollback, or you can query the inserted data (there is no need for us to control commit manually) mysql > select * from ajisun;+-+-+ | id | name | +-+-+ | 1 | Aji | +-+-+ 1 row in set (0.00 sec)

two。 Explicit transaction mode 1

Explicit transactions are controlled by us to open, commit, rollback, and so on.

-- enable explicit transaction-rollback mysql > set autocommit=0;Query OK, 0 rows affected (0.00 sec) mysql > select * from ajisun;+-+-+ | id | name | +-+-+ | 1 | Aggie | +-+-+ 1 row in set (0.00 sec) mysql > insert into ajisun values (2Jing 'Mr. Ji') Query OK, 1 row affected (0.00 sec)-after insertion, you can see 2 pieces of data mysql > select * from ajisun +-+-+ | id | name | +-+-+ | 1 | Aggie | 2 | Mr. Ji | +-+-+ 2 rows in set (0.00 sec)-- after rollback, the data inserted above has no mysql > rollback;Query OK, 0 rows affected (0.00 sec) mysql > select * from ajisun +-+-+ | id | name | +-+-+ | 1 | Aji | +-+-+ 1 row in set (0.00 sec)-- insert a piece of data mysql > insert into ajisun values (2); Query OK, 1 row affected (0.01 sec)-submit mysql > commit Query OK, 0 rows affected (0.00 sec)-rollback mysql > rollback;Query OK, 0 rows affected (0.00 sec)-commit commit first, if the rollback data still exists, it means that the commit takes effect, the transaction has been committed, and the rollback will not take effect. Mysql > select * from ajisun;+-+-+ | id | name | +-+-+ | 1 | Aggie | | 2 | ajisun | +-+-+ 2 rows in set (0.00 sec)

3. Explicit transaction mode 2

Use start transaction

First change to the default transaction set autocommit=1

-- Open transaction 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)-- commit transaction mysql > commit;Query OK, 0 rows affected (0.01 sec) mysql > select * from ajisun +-+-+ | id | name | +-+-+ | 2 | ajisun | +-+-+ 1 row in set (0.00 sec)-- Open transaction mysql > start transaction;Query OK, 0 rows affected (0.00 sec) mysql > delete from ajisun where id = 2bot query OK, 1 row affected (0.01 sec)-- rollback transaction mysql > rollback Query OK, 0 rows affected (0.01 sec)-deletion operation failed mysql > select * from ajisun;+-+-+ | id | name | +-+-+ | 2 | ajisun | +-+-+ 1 row in set (0.00 sec)

four。 Problems in concurrent transactions

There will be no problem if there is only one transaction on the table at the same time, but this is impossible. In reality, it is used as much as possible, and multiple transactions operate at the same time. Multiple transactions will bring a lot of problems, such as dirty reading, dirty writing, unrepeatable reading, phantom reading.

1. Dirty reading

One transaction reads the modified data of another uncommitted transaction. This is dirty reading.

For example, two transactions: a record is operated at the same time.

A transaction after modifying the record has not been formally committed to the database, then b transaction to read, and then use the read data for subsequent operations.

If a transaction rolls back, the modified data no longer exists, then b transaction is using a non-existent data. This is dirty data.

two。 Dirty writing (data loss)

One transaction modifies the modified data of another uncommitted transaction

For example, two transactions: a record is operated at the same time.

A transaction does not commit after modification, then b transaction modifies the same data, and then b transaction commits the data.

If the a transaction rolls back its changes and also rolls back the changes of the b transaction, the problem is that the b transaction has been modified and committed, but the database has not changed, which is dirty writing.

3. Non-repeatable

One transaction can only read the data modified by another committed transaction, and each time the data is modified and committed by other transactions, the transaction can query for the latest value.

That is, when the same record is read multiple times in the same transaction, the content is different (other transactions are modified and committed before each read), which is non-repeatable.

4. Illusory reading

Query data under the same conditions in a transaction, and the number of records queried one after another 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 that condition again, the records inserted by another transaction can also be read out, which means that a false reading has occurred.

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

five。 Isolation level of the transaction

The concurrency problems of transactions mentioned above are different in different scenarios, and the acceptable problems are also different. The order of seriousness between them is as follows:

Dirty writing > dirty reading > unrepeatable reading > phantom reading

Four isolation levels are available in MySQL to deal with these issues, as follows

Isolation level dirty read cannot repeat phantom read READ- UNCOMMITTED √√√ READ-COMMITTED × √√ REPEATABLE-READ × × √ SERIALIZABLE × ×

The SQL standard defines four isolation levels:

READ-UNCOMMITTED (read uncommitted): the lowest isolation level that allows reading of data changes that have not yet been committed and may result in dirty, unrepeatable, and phantom reads. But the degree of concurrency is the highest.

READ-COMMITTED (read committed): allows you to read data that has been committed by concurrent transactions, which prevents dirty reads, but phantom and unrepeatable reads can still occur.

REPEATABLE-READ (repeatable readable): multiple reads to the same field are consistent, unless the data is modified by its own transaction, which can prevent dirty and unrepeatable reads, but phantom readings can still occur.

SERIALIZABLE (serializable): the highest isolation level, fully compliant with the isolation level of ACID. All transactions are executed one by one, so that interference between transactions is completely impossible, and this level prevents dirty reading, unrepeatable reading, and phantom reading. The degree of concurrency is also the lowest.

MySQL default REPEATABLE_READ isolation level Oracle default READ_COMMITTED isolation level

1. How to set the isolation level

You can view the isolation level through the variable parameter transaction_isolation

Mysql > SELECT @ @ transaction_isolation;+-+ | @ @ transaction_isolation | +-+ | REPEATABLE-READ | +-+ 1 row in set (0.00 sec) mysql > show variables like'% transaction_isolation%' +-+-+ | Variable_name | Value | +-+-+ | transaction_isolation | REPEATABLE-READ | +- -+-+ 1 row in set (0.02 sec)

Modified command: SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL $[level]

The value of level is the isolation level READ-UNCOMMITTED READ-COMMITTED REPEATABLE-READ SERIALIZABLE in 4.

Set the global isolation level

Works only for sessions that occur after the statement is executed.

The session that already exists 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'

For example:

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 set (0.00 sec)-current session (meeting that already exists before setting Level is default) mysql > select @ @ transaction_isolation +-+ | @ @ transaction_isolation | +-+ | REPEATABLE-READ | +-+ 1 row in set (0.00 sec)

Session B (session created after set)

Mysql > select @ @ global.transaction_isolation +-- + | @ @ global.transaction_isolation | +-- + | SERIALIZABLE | +-- + 1 row In set (0.00 sec) mysql > select @ @ transaction_isolation +-+ | @ @ transaction_isolation | +-+ | SERIALIZABLE | +-+ 1 row in set (0.00 sec)

Set the isolation level for the session

Valid for all subsequent transactions of the current session

This statement can be executed in the middle of a transaction that is already open, 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'

For example:

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 set (0.00 sec)

New session B (still the default level: repeatable)

Mysql > select @ @ transaction_isolation;+-+ | @ @ transaction_isolation | +-+ | REPEATABLE-READ | +-+ 1 row in set (0.00 sec)

two。 How to choose the isolation level

In general, it is fine to be readable by default, and this is rarely changed, unless the business scenario is special.

Remember: the higher the isolation level, the fewer concurrency problems, but the lower concurrency, so it depends on the business choice.

six。 Summary

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

Common commands for transactions:

Set autocommit=0/1;-disable / enable autocommit transaction start transaction;-enable transaction (or begin) rollback;-roll back transaction commit;-commit transaction

Problems of concurrent transactions: dirty writing > dirty reading > non-repeatable reading > phantom reading

You need to be familiar with the four isolation levels of the transaction and the MySQL default level

How to set the isolation level (global,session)

At this point, I believe you have a deeper understanding of "transactions in MySQL, four major features, and what is the isolation level". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.