ACID and isolation level and dirty read unrepeatable read unreal

ACID

What is ACID?

  • A Atomicity
  • C Consistency
  • I Isolation
  • D. Durability

Atomicity:

The atomicity of a transaction is that a transaction either commits successfully or rolls back on failure.

Consistency:

The execution of a transaction cannot destroy the integrity and consistency of database data. The database must be in a consistent state before and after a transaction is executed. If the database system fails during operation and some things 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, that is, an inconsistent state.

Isolation:

The isolation of transactions means that in a concurrent environment, concurrent transactions are isolated from each other, and the execution of one transaction cannot be interfered by other transactions. When different transactions concurrently operate the same data, each transaction has its own complete data space, that is, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrently executed transactions cannot interfere with each other.

“Durability” :

Once a transaction commits, its changes to the state of the corresponding data in the database are permanently stored in the database. Even in the event of a system crash or machine outage, if the database can be restarted, it can be restored to the state where the transaction successfully ended.

Dirty read cannot repeat magic read

Dirty read

Transaction changes uncommitted, and transaction 2 reads the changes made by transaction 1.

Unrepeatable read

Within a transaction scope, multiple queries return different contents (because the data has been changed by another transaction), dirty read is uncommitted data, and unrepeatable read is committed data by another transaction

Phantom read

A phantom is a condition that occurs when a transaction is not executing independently. While transaction 1 is batch modifying a batch of data to 1->2, another transaction inserts data 1 into the table. If transaction 1 looks at the modified data, it will find a 1. Unreal and unrepeatable reads both read another committed transaction. The difference is that non-repeatable read queries are for the same data, while unreal read queries are for a batch of data.

The solution to non-repeatable reads is to lock rows. The solution to phantom reading is to lock the table.

Isolation level

What is the isolation level?

In the standard SQL specification, there are four transaction isolation levels defined, which handle transactions differently:

  • Read uncommitted
  • Read committed (Read Commited)
  • Repeatable Read
  • Serializable

MySQL default isolation level is Repeatable Read

Read uncommitted

This isolation level allows dirty reads and has the lowest isolation level. For example, if transaction A and transaction B are running at the same time and transaction A’s change data is not committed, transaction B can read the change content of transaction A’s change is not committed, that is, it is read uncommitted.

Read committed (Read Commited)

Only committed data can be read. For example, transaction A and transaction B work together. Transaction A’s change data is not committed, and transaction B cannot read the change content of transaction A. When transaction A commits, transaction B can read the changes made by transaction A.

Repeatable Read

Ensure that when the same data is read multiple times during a transaction, its value is the same as when the transaction started. Avoid dirty and unrepeatable reading

Serializable

The most stringent isolation several times requires that all transactions be executed serially. Can avoid dirty read, non – repeat read, unreal read