The word transactional comes from a translation of the English word Transactional, which means more often to transactional. Transactional is often called a transaction in a database or software system.

This article is shared from huawei cloud community “[Database transactions and locking mechanism] – transaction isolation level”, original author: technology torch bearer.

The word transactional comes from a translation of the English word Transactional, which means more like “transactional.” Transactional is often called a transaction in a database or software system.

Four properties of database transactions: ACID. They are atomicity, consistency, isolation and persistence. These four characteristics of database transactions are derived from the ISO standard ISO/IEC 10026-1:1992/COR 1:1996, which defines the four characteristics required for transactions. How do you implement these features in InnoDB? Next we discuss how MySQL’s support for transactions is implemented.

MySQL supports MVCC before we discuss the implementation of MySQL transaction processing features, we need to understand MySQL support for MVCC, wikipedia explains MVCC.

The Multiversionconcurrency control (MCC or MVCC) is a concurrency control commonly used in database management systems. It is also used to implement transactional memory in programming languages. MVCC is intended to solve the problem of multiple, long-running read operations starving write operations caused by read and write locks. The data item read by each transaction is a snapshot and depends on the isolation level of the implementation. Instead of overwriting an existing item, a write operation creates a new version that is not visible until the action is committed. Snapshot isolation allows things to see the data state as it was started.

Isolation level of database transactions

In order to achieve transaction isolation, ISO standards organization has four definitions of the isolation levels that transaction locks need to achieve. Let’s briefly describe the four isolation levels.

READ UNCOMMITTED The READ is not committed

RU(READ UNCOMMITTED) is called READ UNCOMMITTED, some data is called Browse access but the correct translation should be UNCOMMITTED. RU is the lowest standard of isolation, uncommitted read means that in the case of concurrent transactions, one transaction can be allowed to read the change without committing the change by another transaction, which will result in dirty read condition. The table below shows the suppression of dirty, phantom, and repeatable reads for each transaction isolation level. In fact RU produces not only dirty reads but also both of the other reads.

First of all, it is necessary to clarify the concept of the above three kinds of data read, we simply know for database transaction is a set of database operations in a transaction, the transaction either succeed or fail, but you have to know the database in the process of actual use is not serial, it is a concurrent, In the serial scenario, our prior transaction is very simple, just one operation at a time, we queue up to execute. However, in the scenario of concurrent transactions, there will be the problem of competing for the same data. The simple understanding is that you also need to read and write this data, and I also need to read and write this data. So how can we ensure the consistency and integrity of the data when multiple transactions operate on the same data? At this time, there will be data dirty read, phantom read, repeat read problems.

Dirty read

Dirty reads can occur when one transaction allows reading of data that has been modified but not committed by another transaction.

Dirty read refers to multiple transactions reading and writing data simultaneously. When data A is read and modified in transaction 1, data A is modified in transaction 2, and this modification is reflected in transaction A.

Let’s imagine A scenario where two transactions are operating on the same record in the table. Transaction A needs to get the current value and then increments it by 3 (to buy cucumbers). The original value is 5, but transaction B changes the value to 8. This modification is then picked up by transaction A and added by 3 to the 8 for 11. However, I never expected that after transaction A completed this operation, transaction B was rolled back (regrettably, the banana money was not given). At this time, the amount in the account after the transaction of A is inexplicably changed to 11, but in fact it should be 8. This is also the case with dirty reading.

Unrepeatable read

Non-repeatable reads occur when a row of data is retrieved twice with different results in a transaction.

Understand repeatable reads before you understand unrepeatable reads. Repeatable reads mean that multiple reads of the same data in a transaction should have the same result (without changing its value in the transaction). The reverse means that multiple reads of the same data in a transaction have different values. When can unrepeatable reads occur?

Or the above example, if A transaction is doing A plus 3 operation before you read the original value is 5, and then continue to other operations, this time affairs for the record to add 3 B and then submit the operation, when A transaction when A read again this value found that current value into eight, the value of the two completely different before and after this time, This is also called unrepeatable reading.

Non-repeatable reads are specific to a single transaction, that is, whether a single data can be read repeatedly in a transaction. If not, it means that the requirement of repeatable reads is not met.

A non-repeatable read is similar to a dirty read, but there is a difference. A dirty read means that transaction 2 gets the value without committing the change, while a non-repeatable read means that an inconsistency occurs after committing the change.

Phantom read

During transaction execution, when two identical query statements are executed to produce different result sets. This phenomenon is called phantom read.

Phantom reading is actually an unrepeatable scenario, such as in transaction 1, three data are read for the first time according to a condition, but then transaction 2 adds one data that meets this condition to the table. The second read of transaction 1 shows that there is one more piece of data (or the other way around, one less piece of data), which is a bit confusing to transaction 1.

So all three of these read problems are possible for the uncommitted read isolation level, so it is the lowest level of transaction isolation.

READ COMMITTED READ commit

RC(READ COMMITTED) is when you can READ after committing. Some sources are called COMMITTED. The main scenario for committing a read is the UPDATE statement, which means that an UPDATE can only be read after it has been committed. Imagine if transaction 2 had committed the value instead of rolling back the value for a long time after changing the amount.

This is why committed reads only solve the problem of dirty reads and not the other two types of reads. Because it is clear that even if transaction 2 commits this change, the two reads before and after transaction 1 will be inconsistent (unrepeatable), and of course there will be a magic read scenario, because magic books are inherently unrepeatable special scenarios.

REPEATABLE READS can be read repeatedly

REPEATABLE READS (RR) is a transaction isolation level next to SERIALIZABLE. Usually, REPEATABLE READS are implemented by locking, which can not avoid phantom READS. InnoDB uses the transaction isolation level of RR by default, but different from other databases, InnoDB uses THE NEXT-key Lock algorithm of NKL in the transaction isolation level of RR to avoid phantom read. This is different from other databases, so in InnoDB RR’s transaction isolation level meets the serialized transaction isolation standard.

NKL refers to locking a range and the data itself, not just the data itself, in order to avoid phantom reading, official documents

SERIALIZABLE SERIALIZABLE

Is the highest level of transaction isolation, which by definition means that all transactions are executed serialized, that is, no concurrent transactions occur, thus avoiding all read problems, but this is not possible for a database, because no database can tolerate this situation. So most people think that using this transaction isolation level will have a very significant impact on performance, but some papers experimentally show that serialization does not have a significant impact on performance.

Whether serialization has an impact on performance depends on how the database implements this transaction isolation level, but I don’t know if serialization is slow

MySQL database transaction isolation level

Query the transaction isolation level

In MySQL we can query the transaction isolation level used by the database in the following way

show variables like '%tx_isolation%'; SELECT @@session.tx_isolation; SELECT @@global.tx_isolation;Copy the code

Modify the transaction isolation level

MySQL provides the SET TRANSACTION statement, which can change the TRANSACTION isolation level for a single session or globally. The syntax is as follows:

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

The SESSION and GLOBAL keywords are used to specify the scope of the modified transaction isolation level

  • SESSION: indicates that the modified transaction isolation level will be applied to all transactions in the current SESSION (current CMD window);
  • GLOBAL: indicates that the modified transaction isolation level will be applied to all transactions in all sessions (globally) and existing sessions will not be affected.

If SESSION and GLOBAL are omitted, the modified transaction isolation level will apply to the next transaction within the current SESSION that has not yet started.

Any user can change the transaction isolation level of the session, but only users with SUPER permission can change the global transaction isolation level

JDBC modifies the isolation level of the current connection

connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
Copy the code

​

Click to follow, the first time to learn about Huawei cloud fresh technology ~