Four properties of transactions (ACID)

atomic

All operations in a transaction, either complete or not complete, do not end at some intermediate stage. If a transaction fails during execution, it will be rolled back to the state before the transaction began, as if the transaction had never been executed. That is, transactions are indivisible and irreducible.

Isolation,

The ability of a database to allow multiple concurrent transactions to read, write, and modify its data at the same time. Isolation prevents data inconsistencies due to cross-execution when multiple transactions are executed concurrently. Transaction isolation can be divided into different levels, including Read uncommitted, Read Committed, Repeatable Read, and Serializable.

consistency

The integrity of the database is not compromised before and after a transaction. This means that the data written must fully comply with all preset constraints, triggers, cascading rollback, and so on.

persistence

After a transaction, changes to the data are permanent and will not be lost even if the system fails.

The isolation level of the transaction

Read uncommitted: Read uncommited

  • Problems: dirty read, phantom read, unrepeatable read
  • Problem solved: NULL

Read committed: read commited

  • Problems: illusory reading, unrepeatable reading
  • Problem solved: Dirty reading

Repeatable read: repeatable read

  • Problems: Illusory reading
  • Problem solved: dirty read and unrepeatable read

Serialization (serialization) : serializable

  • Problem: NULL
  • Problem solved: All
  • Highest isolation level, lowest efficiency

Dirty read, phantom read, unrepeatable read

1. Dirty reads

A transaction reads uncommitted data from another transaction (rollback)

2. Phantom reads

Is a special case of unrepeatable reads: when transaction 1 executes SELECT twice… In the middle of a WHERE operation to retrieve a range of data, transaction 2 creates (for example, INSERT) a new row of data in the table that satisfies transaction 1’s “WHERE” clause.

It can feel like an illusion, when data that didn’t exist before suddenly appears, and is called “phantom reading.”

3. Do not repeat the read

When the first transaction “reads” twice, another transaction updates the same data, and the results of the first two reads are inconsistent, so it is called “non-repeatable read”.

Default database isolation level

  1. Repeatable read mysql: repeatable read
  2. Oracle: Read commited

The propagation level of a transaction

  • MANDATORY: MANDATORY) The current transaction can be used, and if there is no transaction, an exception is thrown
  • REQUERS_NEW :(REQUERS_NEW) creates a new transaction and suspends the previous transaction if one exists
  • If there is no transaction, create a new transaction. If there is a transaction, join it
  • There are transactions, NESTED executions, and no transactions, executed required
  • SUPPORTS (SUPPORTS) If there is no transaction, non-transaction execution, if there is a transaction, join the transaction
  • NOT_SUPPORTED :(NOT_SUPPORTED) it must be non-transactional, and if there is a transaction, the transaction is suspended
  • NEVER :(NEVER) non-transaction execution, if there is a transaction, an exception is thrown

PS:

  1. Article from a variety of resources collation (part of the original), if there is infringement, please inform to delete.
  2. Please indicate the source for reprinting this article