Introduction of the transaction

I believe that the friends who have used MySQL all know the transaction, we also often through this example to explain the role of transaction: A transfer to B, here can be divided into two steps to operate the database, A account balance decrease, B account balance increase. However, if A database outage occurs suddenly when the account balance of A decreases, will the balance of A decrease but the balance of B not increase? The answer is no. The reason is that databases support transactions (commonly used relational data, such as MySQL, Oracle, etc.).

A transaction is a series of rigorous operations in an application, all of which must complete successfully or all changes made in each operation will be undone. That is, transactions are atomic, and a series of operations in a transaction will either all succeed or none will be done.

There are two ways to end a transaction: when all steps in the transaction have been successfully executed, the transaction commits. If one of the steps fails, a rollback occurs to undo all previous actions up to the beginning of the transaction.

ACID properties of transactions

This is a common question and one often asked in interviews: What are the ACID properties of transactions? ACID here stands for four words: Atomicity, Consistency, Isolation and persistence.

1. Atomicity. A transaction is the logical unit of work of a database in which all or none of the operations involved are performed

2. Consistency. The result of transaction execution must be to change the database from one consistent state to another. Therefore, a database is said to be in a consistent state when it contains only the results of successful transaction commits. If a failure occurs during the operation of the database system and some transactions are interrupted before completion, some of the changes made to the database by these unfinished transactions have been written to the physical database, then the database is in an incorrect state, or inconsistent state.

3. Isolation. The execution of one transaction cannot be interfered with by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions. Concurrent transactions cannot interfere with each other.

4. Persistence. Also known as permanence, meaning that once a transaction is committed, its changes to the data in the database should be permanent. Subsequent operations or failures should not have any impact on the results of its execution.

The isolation level of the transaction

Before talking about transaction isolation levels, consider the following questions:

1. Dirty Read

A dirty read is a transaction that accesses uncommitted data from another transaction. If transaction A modifies A piece of data, but the transaction is not committed, then transaction B reads the data that has been modified but not committed by transaction A, this is A dirty read.

2. Non-repeatable read

Read the same data multiple times within a transaction. The same data is accessed by another transaction while the transaction is still active. Therefore, between the two reads in the first transaction, the data read by the first transaction may be different due to the modification of the second transaction. This happens because the data read twice in a transaction is not the same and is therefore called unrepeatable.

3. Phantom Read

A phantom read is when multiple queries within the same transaction return different result sets (for example, row records are added or subtracted). For example, in the same transaction A, there are N records in the first query, but n+1 records in the second query under the same conditions, it seems to have an illusion, why the two results are different. Like unrepeatable reads, phantom reads occur when another transaction adds, deletes, or modifies data in the first transaction result set. The difference is that non-repeatable reads are when the data contents of the same record are modified, while phantom reads are when the data row records are changed more or less

With these issues in mind, let’s look at the four transaction isolation levels.

1, Read Uncommitted

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in real-world applications because its performance is not much better than other levels. Reading uncommitted data is also known as dirty reads.

2, Read Committed

This is the default isolation level for most database systems (but not for MySQL). It satisfies a simple definition of isolation: a transaction can only see the changes made by committed transactions. This is known as non-repeatable read.

Repeatable Read (Repeatable Read)

This is MySQL’s default transaction isolation level and ensures that multiple instances of the same transaction will see the same rows when they concurrently read data. In theory, though, this leads to another thorny problem: Phantom Read. Simply put, phantom reading refers to when a user reads a row in a certain range, another transaction inserts a new row in that range, and when the user reads a row in that range, a new phantom row is found. InnoDB and Falcon storage engines address this problem through the Multiversion Concurrency Control (MVCC) mechanism.

4. Serializable

This is the highest isolation level, and it solves the phantom problem by forcing transactions to be ordered so that they cannot conflict with each other. In short, it places a shared lock on each read row. At this level, a lot of timeouts and lock contention can result.

A summary of possible problems with the four isolation levels

With the exception of the worst-performing Serializable isolation level, which does not have dirty reads, unrepeatable reads, or phantom reads, all isolation levels have one or more problems. However, MySQL InnoDB engine solved the phantom reading problem of Repeatable Read by MVCC.

Viewing the Isolation Level

SELECT @@session.tx_ISOLATION; SELECT @@global.tx_isolation;Copy the code

Sets the transaction isolation level

1. Global modification

Modify the mysql configuration file, such as mysql.ini for Windows and my.cnf for Linux.

READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
[mysqld]
transaction-isolation = REPEATABLE-READ
Copy the code

2. Run commands to set the current connection

# Optional parameters are: read uncommitted, Read committed, REPEATable read, serialIZABLE
set session transaction isolation level xxx
Copy the code