[MySQL] ACID

1. Business

It is a sequence of actions that are either executed or not executed. It is an inseparable unit of work.

2. ACID

Transactions have four characteristics: atomicity, consistency, isolation, persistence.

Atomic Nature

A transaction is an indivisible unit of work in which operations are either occurring or not occurring.

Database management system By default, a SQL is a separate transaction, and the transaction is automatically committed. Only the display uses start transaction to open a transaction in order to place a block of code in a transaction, guaranteeing that the atomicity of the transaction is the basic responsibility of the data block, so many databases use the logging mechanism. With a pre-written transaction log, the data is written on the transaction log before it is submitted to the actual data page.

Consistency

The integrity constraints of the database are not compromised before the transaction starts and after the transaction, meaning that the database transaction does not break the integrity of the relational data and the consistency of the business logic. That is, the data operations in the database to achieve conservation.

Workaround: 1. Database mechanism level: Before and after a transaction executes, the data conforms to the set constraints (foreign KEY constraints, UNIQUE constraints, check constraints, and so on) and trigger settings. 2. Business level: Guaranteed by the developer, or through the database level to achieve

Isolation of

When multiple transactions are concurrently accessed, a transaction should not affect the performance of other transactions when it is isolated between transactions. In a concurrency environment, when different transactions operate with the same data at the same time, each transaction has its own full data space, and the modifications made by the concurrent transaction must be isolated from the modifications made by any other concurrent transactions, while the data is in the same state as the other transaction when it is updated. Either the state after which another transaction modifies it, and the transaction does not view the data in the middle state.

WORKAROUND: The database is locked and blocked to ensure different levels of isolation between transactions. In general, complete isolation is unrealistic. Only one transaction is executed at the same time. Severely affect performance.

Interference between concurrent events:

1. Interactions between transactions: Dirty reads, non-repeatable reads, Phantom reads, missing updates

2. Understand the isolation level in the database: READ UNCOMMITTED, Read committed (default), repeatable read, serializable. In the concurrent system of multi-user multi-process multithreading, the data consistency and integrality are ensured. High isolation levels mean more locks, which affect performance.

The effect of isolation transactions is achieved through locks, blocking the effects above. Different isolation levels are implemented by blocking different locks.

Durability

means that after the transaction is complete, the changes made to the database by the transaction are persisted in the database and are not rolled back. Once a transaction is committed, it is permanently present in the database.

WORKAROUND: Use Write-ahead transaction log to ensure durability. That is, changes to the database in a transaction are written to the transaction log before being written to the database. The transaction log is the LSN of the sequential automatic arranging, when the database crashes or the server loses power, restarting the database first checks the log sequence number, persisting portions of the database that were not made to the database to ensure durability.

Summarize

The Acid property of the transaction is implemented by the relational database system, and the database management system uses the log to guarantee the atomicity, consistency and persistence of the transaction. 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 realizes the isolation of the transaction through the locking mechanism, and when multiple transactions update the data at the same time, only the transaction that holds the lock is allowed to update the data, and the other transaction must wait until the previous transaction releases the lock, and the other transaction has the opportunity to update the data.

[MySQL] ACID