Acid characteristics and isolation levels for database transactions

1. Four features of a transaction

A database transaction (Transaction) is a series of operations performed as a single logical unit of work, either completely or completely without execution. On the one hand, when multiple applications access the database concurrently, transactions can provide an isolation method between applications to prevent interference with each other. Transactions, on the other hand, provide a way for a database operation sequence to recover from failure.

Transactions have four properties: atomicity (atomicity), consistency (consistency), isolated (isolation), persistence (durability), or acid.

1.1 atomicity (Atomicity)

The atomicity of a transaction is that the operations in the transaction are not split and are only allowed to execute all or not.

1.2 Consistency (consistency)

Transactional consistency means that the execution of a transaction does not compromise the consistency of the database, and consistency is also known as completeness. After a transaction executes, the database must transition from one consistent state to another.

1.3 Isolation Type (isolation)

The isolation of transactions means that concurrent transactions are isolated from each other and cannot interfere with each other.

1.4persistence (Durability)

the persistence of a transaction means that once a transaction is committed, the state change to the data should be persisted.

four isolation levels for 2 transactions

The actual work of the transaction is almost concurrent, completely do not interfere with each other can seriously sacrifice performance, in order to balance the isolation and performance, the SQL92 specification defines four transaction isolation levels: READ UNCOMMITTED, Read committed (reads Committed), REPEATABLE READ (Repeatable Read), serialization (Serializable). four levels are gradually enhanced, and each level resolves a problem at the previous level.

2.1 Read not submitted (READ UNCOMMITTED)

Another transaction modifies the data but has not yet committed, and select in this transaction reads the uncommitted data ( dirty read ).

Dirty reads refer to another transaction that modifies the data but has not yet committed, and select in this transaction reads the uncommitted data.

2.2 Read submitted (read Committed)

This transaction reads the most up-to-date data (after other transactions have been committed). The problem is that in the same transaction, two times the same select will read different results ( non-repeatable read ).

Non-repeatable reading refers to the fact that another transaction commits new data during the execution of the same transaction, so that the data results read two times by this transaction are inconsistent.

2.3 Repeatable READ (REPEATABLE Read)

in the same transaction, the result of select is the state of the start point of the transaction, and the same select operation reads the same result. However, there is a phantom reading phenomenon.

non-repeatable reads guarantee the same transaction, and the result of the query is the state (consistency) at the beginning of the transaction. However, if another transaction commits new data at the same time, when the transaction is updated, the new data is discovered, and it seems that the previously read data is illusory, which is the Phantom reading .

2.4 Serialization (Serializable)

All transactions can be executed one by one serially, not concurrently.

3 Selection of Isolation levels

The higher the transaction isolation level, the greater the consistency of the data, but the greater the impact on concurrency performance, the tradeoff or compromise between consistency and performance.

In general, most applications can choose to set the isolation level of the database to read committed, which avoids dirty reads and can also get good concurrency performance. Although this isolation level results in non-repeatable, phantom-readable, this individual application can be actively locked for concurrency control.

Acid characteristics and isolation levels for database transactions