preface

We introduced the database in the previous chapter and introduced you to the use of THE ACID feature for transactions in a database. This section focuses on the isolation level of a transaction, a very important knowledge in the database. If there are mistakes, please point out in time

Question:

  • What are the isolation levels for transactions?
  • What happens if concurrent transactions are not isolated?

The following are all mysql databases

Problems can occur if there is no effective avoidance mechanism when multiple transactions are concurrently performing database operations. In general, there are the following problems:

First, the problems caused

The following problems occur when concurrent transactions are not isolated.

Problem one: dirty reading

Dirty reads are when a transaction reads uncommitted data from another transaction.

See the following case introduction

Problem two: not repeatable

Non-repeatable reads refer to reading a row of data in a table within a transaction, with different results. The difference between an unrepeatable read and a dirty read is that a dirty read reads the dirty data that has not been committed in a previous transaction. An unrepeatable read reads the data that has been committed in a previous transaction.

See the following case introduction

Problem three: Phantom reading (virtual reading)

Phantom read (virtual read) refers to a transaction that reads data inserted by another transaction, resulting in inconsistent reads.

See the following case introduction

Second, the concept of

2.1 Transaction isolation levels are as follows:

  • Read uncommitted
  • Read Committed
  • Repeatable Reads
  • Serializable

Read uncommitted

Read uncommitted: A transaction level with the lowest isolation level. At this isolation level, dirty reads, unrepeatable reads, and phantom reads are triggered.

Read Committed

Read the submitted value and read the submitted value. This isolation level causes unrepeatable and phantom reads, but avoids dirty reads.

Repeatable Reads

Repeatable read At this isolation level, phantom reads are generated, but dirty and unrepeatable reads are avoided.

Serializable

Serialization is the strictest isolation level. At the Serializable isolation level, all transactions are executed in sequence. Dirty reads, unrepeatable reads, and phantom reads will not appear.

Three, operation

3.1 Viewing the Transaction Isolation Level

SHOW VARIABLES LIKE 'tx_isolation';

View the transaction isolation level globally

SHOW GLOBAL VARIABLES LIKE 'tx_isolation';

Query using system variables

SELECT @@global.tx_isolation; SELECT @@session.tx_isolation; SELECT @@tx_isolation;

3.2 Setting the transaction Isolation level for MysQL

grammar

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
  {
       REPEATABLE READ
     | READ COMMITTED
     | READ UNCOMMITTED
     | SERIALIZABLE
   }
Copy the code

SESSION: Sets the transaction isolation level of the current SESSION. If GLOBAL or SESSION is not specified in the statement, the default value is SESSION

Set the transaction isolation level using system variables

SET GLOBAL tx_isolation='REPEATABLE-READ'; SET SESSION tx_isolation='SERIALIZABLE';

Iv. Case analysis

For the concurrency control statements used in the following operations, see the operation description above

As a demonstration: the product table

productId productName productPrice productCount
1 xiaomi 1999 100

With that in mind, what problems can transactions cause without isolation?

Open two Windows at the same time to simulate two users accessing the database concurrently

4.1 Set transaction isolation level to Read Uncommitted

Query the transaction isolation level

SELECT @@tx_isolation;
Copy the code

Set the isolation level to uncommitted read:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Copy the code

Note: You need to change the transaction isolation level for both Windows simultaneously

Here we take two users grab Millet mobile phone as an example

The timeline A transaction Transaction B
T1 Start the transaction;
T2 select p.productName,p.productCount from product p where p.productId=1; (productCount =100)
T3 Start the transaction;
T4 select p.productName,p.productCount from product p where p.productId=1; (productCount =100)
T5 update product set productCount = 99 where productId = 1;
T6 select p.productName,p.productCount from product p where p.productId=1; (productCount =99)
T7 ROLLBACK;
T8 select p.productName,p.productCount from product p where p.productId=1; (productCount =100)

T1 — A User starts A transaction and starts A transaction. T2 – A user queries the current remaining quantity of millet mobile phone, the select p.p roductName, p.p roductCount from product p where p.p roductId = 1; The quantity is displayed as 100. T3 — B Start transaction; T4 – B user queries the current remaining quantity of millet mobile phone, the select p.p roductName, p.p roductCount from product p where p.p roductId = 1; The quantity is displayed as 100. Update product set productCount = 99 where productId = 1 update product set productCount = 99 where productId = 1 At this point, only the data is modified and the transaction is not committed. T6 – A user refresh the page, select p.p roductName, p.p roductCount from product p where p.p roductId = 1; The quantity is 99. T7 — B User purchase failed, rollback transaction. T8 – A user queries the current remaining quantity of millet mobile phone, the select p.p roductName, p.p roductCount from product p where p.p roductId = 1; The quantity is displayed as 100.

Summary:

Transaction A reads uncommitted data, and transaction B rolls back, causing transaction A’s data to be inconsistent, resulting in transaction A’s dirty read!

4.2 Set transaction isolation level to Read Committed

Query the transaction isolation level

SELECT @@tx_isolation;
Copy the code

Change the database isolation level to Commit read:

SET SESSION  TRANSACTION ISOLATION LEVEL READ COMMITTED;
Copy the code

Note: You need to change the transaction isolation level for both Windows simultaneously

The timeline A transaction Transaction B
T1 Start the transaction;
T2 select p.productName,p.productCount from product p where p.productId=1; (productCount =100)
T3 Start the transaction;
T4 select p.productName,p.productCount from product p where p.productId=1; (productCount =100)
T5 update product set productCount = 99 where productId = 1;
T7 select p.productName,p.productCount from product p where p.productId=1; (productCount =100)
T6 commit;
T8 select p.productName,p.productCount from product p where p.productId=1; (productCount =99)

I won’t say too much about the process here.

Summary:

You can see that dirty reads are avoided, but instead, A non-repeatable read problem occurs before A transaction is completed, i.e. productCount goes from 100->100->99 for transaction A. However, the transaction is not committed during this process.

4.3 Transaction isolation level set to Repeatable Read (mysql default level)

Query the transaction isolation level

SELECT @@tx_isolation;
Copy the code

Change the database isolation level to repeatable reads:

SET SESSION  TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Copy the code

Note: You need to change the transaction isolation level for both Windows simultaneously

The timeline A transaction Transaction B
T1 Start the transaction;
T2 select p.productName,p.productCount from product p where p.productId=1; (productCount =100)
T3 Start the transaction;
T4 select p.productName,p.productCount from product p where p.productId=1; (productCount =100)
T5 update product set productCount = 99 where productId = 1;
T7 select p.productName,p.productCount from product p where p.productId=1; (productCount =100)
T6 commit;
T8 select p.productName,p.productCount from product p where p.productId=1; (productCount =100)

I won’t say too much about the process here.

Summary:

As you can see, the repeatable read isolation level avoids the problem of dirty, unrepeatable reads, but phantom reads occur. The number of millet queried by transaction A is equal to 100, but transaction B modifies the number to 99, but transaction A reads the same value of 100. When transaction A is subtracted by 1 to 99, it is not correct to say that 99-1=98. Next we raise the transaction isolation level by one more.

4.4 Set transaction isolation level to Serializable

Query the transaction isolation level

SELECT @@tx_isolation;
Copy the code

Change the database isolation level to serialization:

SET SESSION  TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Copy the code
The timeline A transaction Transaction B
— — —
T1 Start the transaction;
T2 Start the transaction;
T2 select p.productName,p.productCount from product p where p.productId=1; (productCount =100);
T4 update product set productCount = 99 where productId = 1; (Waiting..)

I won’t say too much about the process here.

Summary:

In our Serializable isolation level, we can see that transaction B is stuck when it goes to modify and cannot execute down. This is because transaction A’s select operation is locked, so transaction B will be blocked if it changes the value. The operation of transaction B is performed only when the operation of transaction A completes. This avoids all three problems.

The problem itself

  • To get back to the point, we don’t need to raise the transaction this high.

  • The problem is that when we finish reading, we have to put a lock on it. We don’t want people to be able to read it. Because somebody reads count, they change the value of count and write it in. So we add for update to the select operation. This will lock out the line operation. If the same operation is performed by another person, it means that the select count needs to be updated and locked.

select p.productName,p.productCount from product p where p.productId=1 for update;
Copy the code

PS: In the actual development process, such locking behavior, is very consumption of system performance. Pessimistic and optimistic locks will be covered in the next section

At the end of the article

This chapter mainly introduces the isolation of database transactions in the ADID feature, in the absence of isolation in the case of what happens, I believe that you through this chapter, the isolation of database transactions have a certain understanding, the next article we will introduce the pessimistic and optimistic locks in the database. Welcome to pay attention to the public number: Coder programming to obtain the latest original technical articles and related free learning materials, anytime, anywhere to learn technical knowledge!

Recommended reading

Learn how to use a JOIN in a database

Learn about group by in a database

Introduces you to the ACID properties of transactions in a database

Github home page directory

Gitee personal home page directory

Welcome to follow and Star~