An explanation of transaction isolation and other concurrent transactions in MySQL database

In MySQL, the transaction isolation level defines the situation in which a transaction and other concurrent transactions can see each other’s changed data. The SQL standard specifies four isolation levels, as follows:

• Serialization (SERIALIZABLE): Transactions are executed serially, that is, each transaction must wait for the end of the previous transaction before starting execution. This ensures that transactions do not interfere with each other, but also results in reduced concurrency performance.
• REPEATABLE READ: Within the same transaction, when the same data is read multiple times, the results will be consistent, regardless of whether other transactions have modified the data. However, after the transaction is committed, other transactions can change the data. At this time, the transaction sees the data before modification.
• READ COMMITTED: Only modifications made by other transactions that have been submitted can be seen. In other words, when a transaction starts, only the changes made by all transactions that have been committed before the start can be seen. Modifications made within this transaction are not visible until other transactions are committed.
• Uncommitted read (READ UNCOMMITTED): Can see uncommitted modifications made by other transactions. This means that this transaction may have read dirty data, that is, data that has not yet been committed.

Dirty read: A dirty read occurs when a transaction reads the uncommitted modifications of another transaction;

Nonrepeatable read: The same query is performed multiple times in the same transaction. Due to modifications or deletions made by other commit transactions, A different result set is returned each time, and non-repeatable reading occurs at this time;

Phanton read: The same query is performed multiple times in the same transaction. There are no insert operations performed by other submit transactions. Each time Different result sets are returned each time, and a phantom read occurs.

Four isolation levels

Isolation level	Dirty read	Non-repeatable read	Phantom read	Locked read
Read uncommitted Read uncommitted	√	√	√	No lock
Read committed	×	√	√	No locking
Repeatable read Repeatable read	×	×	×	Locked
Serializable	×	×	×	Lock

#four types of isolation
-- Read uncommitted read uncommitted dirty read non-repeatable read phantom read locked read
-- Read committed Read committed Non-repeatable read Phantom read No locking
-- Repeatable read Repeatable read without locking
-- Serializable can be serialized and locked

-- View the current session isolation level
SELECT @@tx_isolation
-- View the current isolation level of the system
SELECT @@global.tx_isolation
--Set the current session isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Set the current isolation level of the system
-- set session transaction isolation level [isolation level name]

Specific example demonstration:

-- Demonstrate MySQL transaction isolation level
-- 1. Open two consoles
-- 2. View the current isolation level of MySQL
SELECT @@tx_isolation;

# mysql> SELECT @@tx_isolation;
-- + ------------------ +
-- | @@tx_isolation |
-- + ------------------ +
-- | REPEATABLE-READ |
-- + ------------------ +

-- 3. Set the isolation level of one of the consoles to Read uncommitted

Note: Start the transaction start tracaction for both tables at the same time;

If console B reads the data when console A is not submitted when A is not submitted, it proves dirty reading;

If after A submits, B console reads, and A console modifies the deleted data, proving that the read is non-repeatable;

If after A submits, console B reads and the data inserted by console A proves phantom reading;

ps: Under normal circumstances, what the B console should read is the data when the B console is turned on at that time, which is not the data updated in real time!

1. Prove that Read uncommitted reads uncommitted dirty reads, non-repeatable reads, and phantom reads

This is another console, you can also check the data synchronously

When control A is not submitted, and after it is submitted, console B can query relevant data changes, which proves the above;

2. Prove Read committed, non-repeatable read, phantom read

Change the B console to read committed;

When console A inserts data again without submitting it, console B cannot find the data; there is no dirty read;

When the B console finds the data after submission, it is non-repeatable reading and phantom reading;

3. Prove that Repeatable read does not exist, dirty read, non-repeatable read, phantom read

Initial state:

Now to change the data:

When console A is not submitted, console B cannot detect data changes.

After console A submits, console B still cannot detect data changes, indicating that dirty reads, non-repeatability, and phantom reads do not exist;

4. Prove that Serializable does not exist, dirty reads, non-repeatable reads, phantom reads, and is locked

Open two transactions at the same time and set the B console to serializable;

Perform data operations, input data, and change data on console A. When querying on console B, the cursor keeps flashing. The lock is reflected here, because transaction A is occupying the table, and console B cannot view it and perform related operations.

After waiting for a while in the B console, a relevant statement appeared, meaning that the lock wait timed out and an attempt was made to restart the transaction.

In console A, after the transaction is submitted, console B can perform related operations on the table.

It can also be proved here that Serializable does not have dirty reads, non-repeatability, phantom reads, and is locked.

Summary:

1. Under normal circumstances, when a transaction is opened, the two transactions cannot access each other. Because the transaction has not yet been submitted at this time, the operator may modify the data. If transaction B accesses transaction A that is being modified and reads temporary data, then Dirty reading.
2. When transactions A and B are started at the same time, after transaction A modifies, deletes and commits, transaction B still cannot access the operations performed by transaction A on the table. Because, under normal circumstances, A and B transactions are independent of each other and should not be affected by the other party. At this time, it is non-repeatability.
3. When transactions A and B are started at the same time, after transaction A commits the insert operation, transaction B still cannot access the operations performed by transaction A on the table. Because, under normal circumstances, A and B transactions are independent of each other and should not be affected by the other party. At this time, it is phantom reading.

The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. Java skill treeUsing JDBC to operate the databaseDatabase operation 139333 people are learning the system