Properties-acid and isolation levels for database transactions

1, database Transaction Properties-acid (the first letter of the four English words):

1) atomicity (atomicity)

The so-called atomicity is the operation of a set of operations as an operating unit, atomic operations, that is, either all execute, or all do not execute.

2) Consistency (consistency)

Transactional consistency means that the database must be in a consistent state before and after a transaction is executed. If the transaction completes successfully, all changes in the system are applied correctly and the system is in a valid state. If an error occurs in the transaction, all changes in the system are automatically rolled back and the system returns to its original state.

3) Isolation (isolation)

Isolation refers to the concurrency of transactions that are isolated from each other. That is, the operations within a transaction and the data being manipulated must be blocked from being seen by other transactions attempting to modify it.

4) Durability (durability)

Persistence refers to the fact that once a transaction is committed, its changes to the data in the database are permanent, and the subsequent operations and database failures should not have any effect on it. That is, once a transaction commits, the DBMS (database Management System) guarantees that its changes to the data in the database should be permanent and that persistence is guaranteed through database backup and recovery.

2, in the relational database, the isolation of the transaction is divided into four isolation levels, in the interpretation of these four levels before the introduction of several concepts about reading data.

1) Dirty Read (Dirty Reads): The so-called dirty reading is the Dirty data (drity) read, and dirty data refers to uncommitted data. That is, a transaction is modifying a record, and before the transaction is completed and committed, the data is in a pending state (which may or may not be rolled back), when the second transaction reads the uncommitted data and further processes it, resulting in uncommitted data dependencies. This phenomenon is called dirty reading.

2) Non-repeatable read (non-repeatable Reads): One transaction reads the same record sequentially, but the data read two times is different, we call it non-repeatable read. That is, the transaction is changed between two reads and the data is modified by other firms.

3) Phantom Read (Phantom Reads): A transaction re-reads the previously retrieved data in the same query condition, but finds that other transactions have inserted new data that satisfies its query criteria, a phenomenon called Phantom reading.

3. Comparison of four isolation levels for transactions:

1) uncommitted read (READ UNCOMMITTED): The SELECT statement is executed in a non-locking manner, so it is possible to read dirty data with the lowest isolation level.

2) Read Committed: Read only the data that has been submitted. That is, the dirty reads are resolved, but non-repeatable reads are not resolved.

3) Repeatable read (repeated read): Queries within the same transaction are the same as the start of the transaction, InnoDB the default level. In the SQL standard, this isolation level eliminates non-repeatable reads, but there are also phantom reads.

4) serial Read (Serializable): Fully serialized read, all SELECT statements are implicitly converted to select ... Lock in SHARE MODE, that is, reads using table-level shared locks, both read and write blocking each other. The isolation level is highest.

Isolation Level Comparison table:

4. Settings for the transaction isolation level:

1) Service startup options--transaction-isolation或在配置文件中设置：

[Mysqld]

transaction-isolation = {read-uncommitted | read-committed

| Repeatable-read | SERIALIZABLE}

2) Settings after service startup:

SET [GLOBAL | SESSION] TRANSACTION Isolation Level

{READ Uncommitted | READ COMMITTED | Repeatable READ | SERIALIZABLE}

Properties-acid and isolation levels for database transactions