Database Transaction attributes

1. Database Transaction attributes-ACID (the first letter of four English words): 1) Atomicity (Atomicity) the so-called Atomicity is to take a group of operations as an operational unit, it is an atomic operation, that is, either to execute all or not to execute all. 2) Consistency refers to the Consistency of a transaction before it is executed.

1. Database Transaction attributes-ACID (the first letter of four English words): 1) Atomicity (Atomicity) the so-called Atomicity is to take a group of operations as an operational unit, it is an atomic operation, that is, either to execute all or not to execute all. 2) Consistency refers to the Consistency of a transaction before it is executed.

1. Database Transaction Attribute-ACID (the first letter of four English words ):

1) Atomicity)

The so-called atomicity means to take a group of operations as an operation unit. It is an atomic operation, that is, either all or all.

2) Consistency)

Transaction consistency means that the database must be consistent before and after a transaction is executed. If the transaction is successfully completed, all changes in the system will be correctly applied 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 the original state.

3) Isolation)

Isolation refers to the isolation of concurrent transactions. That is to say, the operations inside a transaction and the data being operated must be blocked and not seen by other transactions that attempt to modify the data.

4) Durability)

Durability means that once a transaction is committed, its changes to data in the database are permanent, and other operations and database faults should not have any impact on it. That is, once a transaction is committed, the DBMS (Database Management System) ensures that its changes to the data in the Database should be permanent, and the persistence is guaranteed through Database backup and recovery.

2. In relational databases, transaction isolation is divided into four isolation levels. before interpreting these four levels, we will introduce several concepts about Data Reading.

1) Dirty Reads: Dirty reading refers to reading Dirty Data, which refers to uncommitted Data. That is to say, a transaction is modifying a record. before the transaction is completed and committed, the data is in the TBD state (may be committed or rolled back, the second transaction reads the uncommitted data and performs further processing accordingly, resulting in uncommitted data dependency. This phenomenon is called dirty reading.

2) Non-Repeatable Reads: A transaction Reads the same record successively, but the data read twice is different. That is to say, this transaction is modified by another transaction between two reads.

3) Phantom Reads: A transaction re-Reads the previously retrieved data based on the same query conditions, but finds that other transactions Insert new data that meets the query conditions, this phenomenon is called phantom read.

3. Comparison of four transaction isolation levels:

1) Uncommitted Read (Read Uncommitted): The SELECT statement is executed in non-locked mode, so dirty data may be Read, with the lowest isolation level.

2) Read Committed: only Committed data can be Read. That is, dirty reads are solved, but repeated reads are not allowed.

3) Repeated Read: All queries in the same transaction are consistent at the start time of the transaction, and the default InnoDB level. In the SQL standard, this isolation level eliminates non-repeated reads, but there are still Phantom reads.

4) Serializable: Fully Serializable read. All SELECT statements are implicitly converted to SELECT... lock in share mode, that is, reading and using table-level shared locks will block reading and writing. The highest isolation level.

Isolation level comparison table:

4. transaction isolation level settings:

1) Service startup options--transaction-isolationOr set in the configuration file:

[mysqld]

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED

| REPEATABLE-READ | SERIALIZABLE}

2) settings after the service is started:

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL

{Read uncommitted | READCOMMITTED | repeatable read | SERIALIZABLE}