This is the 7th day of my participation in Gwen Challenge

The transaction

A common explanation for a transaction is that a transaction is a set of logical operations that either all or none of them execute.

Classic example: Transfer money.

Four Properties of Transactions (ACID)

  • atomic
  • Consistency: Data is consistent before and after a transaction is executed. Multiple transactions read the same data with the same result.
  • Isolation,
  • persistence

The isolation level of the transaction

In order to achieve the four features of transactions, the database defines four different transaction isolation levels, from lowest to highest

Read UNcommitted, Read committed, Repeatable Read, Serializable,

The four levels solve the problems of dirty reads, unrepeatable reads, and phantom reads one by one.

Read-uncommitted: The lowest isolation level that allows UNCOMMITTED data changes to be READ, potentially resulting in dirty, illusory, or unrepeatable reads.

Read-committed: Allows concurrent transactions to READ data that has been COMMITTED, preventing dirty reads, but magic or unrepeatable reads can still occur.

REPEATABLE-READ: Multiple reads of the same field are consistent, unless the data is modified by the transaction itself. This can prevent dirty reads and unrepeatable reads, but phantom reads are still possible.

SERIALIZABLE: Highest isolation level, fully subject to ACID isolation level. All transactions are executed one by one so that interference between transactions is completely impossible. That is, this level prevents dirty reads, unrepeatable reads, and phantom reads.

Setting the Isolation Level

To set innoDB’s transaction level, run the following command: SET scoped Transaction Isolation Level Transaction isolation level

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

mysql> set global transaction isolation level read committed; / / global

mysql> set session transaction isolation level read committed; // Current session (a CMD window)

Dirty read, unrepeatable read, phantom read

  • Drity Read: a transaction has updated a copy of data, and another transaction has Read the same copy of data. For some reason, the first transaction has rolled back, and the data Read by the second transaction is incorrect.
  • Non-repeatable read: Data inconsistency between two queries of a transaction. This may be because the original data updated by a transaction was inserted between the two queries.
  • Phantom Read: a transaction where the number of pens is inconsistent between two queries. For example, if one transaction queries for rows and another inserts new columns, the previous transaction will find columns that it did not have before on subsequent queries.

Pay attention to

REPEATABLE_READ Isolation level Used by Mysql Default READ_COMMITTED isolation level used by Oracle default

The implementation of transaction isolation mechanism is based on locking mechanism and concurrent scheduling. Among them, concurrent scheduling uses MVVC (Multi-version Concurrency Control), which supports concurrent consistent read and rollback by saving modified old version information.

Most database systems have read-committed isolation because the lower the isolation level, the less locks there are on transaction requests. But remember that InnoDB storage engine uses REPEATABLE READ by default without any performance penalty.

The InnoDB storage engine typically uses the SERIALIZABLE isolation level for distributed transactions.