Solve the problem of MySql8.0 checking transaction isolation level error

MySql8.0 View transaction isolation level error

question

Database view transaction isolation level

select @@global.tx_isolation,@@tx_isolation;

Error:

mysql> select @@global.tx_isolation,@@tx_isolation;
ERROR 1193 (HY000): Unknown system variable 'tx_isolation'
mysql> select @@global.tx_isolation,@@tx_isolation;
ERROR 1193 (HY000): Unknown system variable 'tx_isolation'

I use mysql version 8.0. tx_isolation changed to global.transaction_isolation in version 8.0

The command changes to

select @@global.transaction_isolation,@@transaction_isolation;

MySQL transaction isolation level (including mysql8.0+ settings and viewing)

Four transaction isolation levels of MySQL

1. Basic Elements of Transactions (ACID)

1. Atomicity : After a transaction starts, all operations must be completed or not done at all. It is impossible for the transaction to be stuck in the middle. If an error occurs during the execution of a transaction, it will be rolled back to the state before the transaction started, and all operations will be as if nothing had happened. In other words, affairs are an indivisible whole, just like the atoms we learned in chemistry, which are the basic units of matter.

2. Consistency : The integrity constraints of the database are not violated before and after the transaction starts and ends. For example, if A transfers money to B, it is impossible for A to deduct the money but B does not receive it.

3. Isolation : Only one transaction is allowed to request the same data at the same time, and there is no interference between different transactions. For example, if A is withdrawing money from a bank card, B cannot transfer money to this card before A completes the withdrawal process.

4. Durability : After the transaction is completed, all updates to the database by the transaction will be saved to the database and cannot be rolled back.

2. Concurrency issues of transactions

1. Dirty read : Transaction A reads the data updated by transaction B, and then B rolls back the operation, so the data read by A is dirty data

2. Non-repeatable read : Transaction A reads the same data multiple times. During the process of transaction A reading multiple times, transaction B updates and commits the data, resulting in inconsistent results when transaction A reads the same data multiple times.

3. Phantom read : System administrator A changes the grades of all students in the database from specific scores to ABCDE grades, but system administrator B inserts a record with a specific score at this time. When system administrator A finishes the change, he finds that there is still a record that has not been changed, as if an illusion has occurred. This is called phantom read.

Summary: Non-repeatable reads and phantom reads are easily confused. Non-repeatable reads focus on modifications, while phantom reads focus on additions or deletions. To solve the problem of non-repeatable reads, you only need to lock the rows that meet the conditions. To solve the problem of phantom reads, you need to lock the table.

3. MySQL transaction isolation level The transaction isolation level is divided into

Read uncommitted, read committed, repeatable read, serializable .

The four levels from low to high are Read uncommitted, Read committed, Repeatable read, and Serializable. These four levels can solve the problems of dirty read, non-repeatable read, and phantom read one by one.

Yes : May occur No : Will not occur

Read Uncommitted

Transaction A has been executed but not committed; Transaction B queries the updated data of Transaction A; Transaction A is rolled back; - Dirty data appears

Read Committed

Transaction A performs an update; Transaction B queries; Transaction A performs an update again; When Transaction B queries again, the data is inconsistent between the two times; - Non-repeatable read

Repeatable Read

No matter how many times transaction A is executed, as long as it is not committed, the query value of transaction B will not change; transaction B only queries the data snapshot at the moment when transaction B starts; - phantom read

Serialization

Concurrent read and write operations are not allowed. When writing is executed, reading must wait.

4. Database Settings

// View the current transaction level (not applicable to MySQL versions above 8.0, see below):
SELECT @@tx_isolation;

//Set the isolation level of mysql:
set session transaction isolation level; Set transaction isolation level // Set read uncommitted level:
set session transaction isolation level read uncommitted;

//Set the read committed level:
set session transaction isolation level read committed;

//Set the repeatable read level:
set session transaction isolation level repeatable read;

//Set the serializable level:
set session transaction isolation level serializable;

MySQL 8.0+ Query database transaction isolation level

According to the method in the MOOC course, query the transaction isolation level using:

select @@tx_isolation;

I'm in sequal pro, but it prompts an error: Unknown system variable 'tx_isolation'

After checking the official documentation, I found that this query method has been abandoned in 8.0+.

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

In MySQL 8.0.3, this variable has been replaced by transaction_isolation.

There are many new query methods, and two are provided below:

1.select @@transaction_isolation;

2. show variables like 'transaction_isolation';

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.