Four isolation levels and four characteristics of transactions for a database

Four characteristics of database transactions:

A transaction (Transaction) is the basic unit of concurrency control. The so-called transaction, which is a sequence of operations that are either executed or not executed, is an inseparable unit of work. For example, bank transfers work: Debit from one account and add another account, both of which are either executed or not executed.

1, the database transaction must have acid characteristics, acid is atomic (atomicity), consistency (consistency), isolation (isolation) and durability (persistence) abbreviations.

1) atomicity (atomicity)

All operations in a transaction (transaction) are either completed or not completed and will not end up in the middle of a link. When an error occurs during execution, the transaction is rolled back (Rollback) to the state before the transaction begins, as if the transaction had never been executed.

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)

In a concurrency environment, when different transactions manipulate the same data at the same time, each transaction has its own full data space. Modifications made by a concurrent transaction must be isolated from modifications made by any other concurrent transaction. When a transaction views the data update, the data is in the state it was in before the other transaction modifies it, or the state after the other transaction modifies it, and the transaction does not view the data in the middle State .

4) Durability (durability)

This means that as long as the transaction completes successfully, the updates it makes to the database must be persisted. Even if a system crash occurs, the database can be restored to the state at the end of the transaction when the database system is restarted.

2. The (ACID) characteristic of the transaction is implemented by the relational database management system (RDBMS, database System). The database management system uses logs to ensure the atomicity, consistency and persistence of transactions . The log records the updates that the transaction makes to the database, and if a transaction has an error during execution, it can undo the updates that the transaction has made to the database based on the log, returning the database to the initial state before the transaction was executed.

The database management system adopts the lock mechanism to realize the isolation of the transaction . When multiple transactions update the same data in the database at the same time, only transactions that hold the lock are allowed to update the data, and other transactions must wait until the previous transaction releases the lock, and other transactions have the opportunity to update the data.

3, 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.

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:

5. 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}

Add: The database system must maintain the following characteristics of the transaction (for short, acid):

Atomicity (atomicity)

Consistency (consistency)

Isolation (Isolation)

Persistence (Durability)

⑴ atomicity (atomicity)

Atomicity means that all operations contained by a transaction are either all successful or fail back, so the operation of a transaction must be fully applied to the database if it succeeds, and cannot have any effect on the database if the operation fails.

⑵ Consistency (consistency)

Consistency means that a transaction must transform a database from one consistent state to another, meaning that a transaction must be in a consistent state before and after execution.

Take the transfer, assume that user A and User B both the sum of money together is 5000, then no matter how a and B transfer, transfer several times, after the end of the transaction two users of the sum should be 5000, this is the consistency of the transaction.

⑶ Isolation (Isolation)

Isolation is when multiple users concurrently access the database, such as when the same table operation, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions to be isolated from each other.

To achieve this effect: for any two concurrent transactions T1 and T2, in the case of transaction T1, the T2 either ends before the T1 starts, or starts after the T1 ends, so that every transaction does not feel that another transaction is executing concurrently.

The isolated database on transactions provides a variety of isolation levels.

⑷ Persistence (Durability)

Persistence refers to the fact that once a transaction is committed, changes to the data in the database are permanent, even if the database system encounters a failure, and the commit transaction is not lost.

Four isolation levels and four characteristics of transactions for a database