Database acid, isolation level, and MVCC

First you need to define the concept of a transaction: a set of atomic SQL queries that, if the database engine succeeds in applying all the statements of the group query to the database, executes the set of statements, otherwise all statements are not executed.

The transaction has an acid four feature, namely:

atomicity : A transaction is an inseparable minimal unit of work whose operations are either all successful or all fail;

consistency : The database is always transitioning from one consistent state to another. The so-called consistency state, that is, all the integrity constraints of the database (especially the user-defined constraints) are adhered to, in the case of bank transfer, "the transfer operation will inevitably result in one account reduction amount, the other account increases the amount, and the total amount of the two accounts is the same" is an integrity constraint.

Persistence: Once a transaction commits, its modifications are persisted to the database

Isolation : Isolation is used to define the degree of isolation between transactions and there are four isolation levels.

First you need to explain a few conceptual definitions related to isolation:

(1) Dirty read: Refers to the transaction read dirty data, the so-called dirty data, refers to the incorrect data, such as a transaction during the execution of a record modified, and then rollback, if other transactions read the record's intermediate modified value, then dirty read.

(2) Non-repeatable read: During the execution of a transaction, the same existing record is read multiple times, and the values of each read are different. Read Commit isolation level there is a non-repeatable read problem, transaction 1, 2 concurrent execution, transaction 2 first read record 1, then transaction 1 modify record 1 and commit, transaction 2 continues to read the record 1, the transaction 22 reads the value is different.

(3) Phantom reading: Phantom reading refers to the number of records that may be read differently when a batch of records is read with a certain condition. The difference between a phantom read and a dirty read or non-repeatable read is that dirty reads and non-repeatable reads are not required for a certain existing record (reading to dirty data or multiple read values), while Phantom reads multiple times using the same condition to query a batch of records, and the number of records read several times is different, that is, dirty reads, Non-repeatable reads are caused by multiple transactions executing the update in parallel, while Phantom reads are caused by multiple transactions executing inserts in parallel (the problem that causes the concurrency delete to appear to be the same ...). ）。

The four isolation levels are:

(1) Read uncommited: Reads uncommitted, meaning that there are multiple concurrent transactions, any one transaction can read the modifications that have not yet been committed by the other transaction:

There are dirty reads, non-repeatable reads, and the possibility of phantom reading.

(2) Read commited: Read committed, meaning for multiple concurrent transactions, any one transaction can only read the changes that have been committed by other transactions:

Resolve dirty reads, there are non-repeatable read, the possibility of phantom reading.

(3) REPEATABLE READ: Repeatable read, meaning that when multiple transactions are executed concurrently, any one transaction reads the existing record repeatedly, and each read value is the same

Resolve dirty read, non-repeatable read, there is the possibility of phantom reading.

(4) Serializable: serialization, meaning that all transactions are executed serially, so there is no case of transaction concurrency execution.

Resolves dirty reads, non-repeatable reads, and Phantom reads.

Multi-version concurrency control MVCC

Of the four isolation levels mentioned above, read UNCOMMITTED isolation is the worst, and performance is not much improved with respect to read-committed, and the serialization isolation is best, but performance is poor and almost impossible to use. The default isolation level for a generic database is either read-committed or repeatable (for example, MySQL's InnoDB engine) or read-committed (for example, Oracle).

If you use row-level read locks, write locks to implement read-committed or repeatable reads, the following steps should be taken:

1, transaction 1 will modify row 1, the row 1 plus write lock, start the transaction;

2, Transaction 2 is a pure read operation, you need to read Line 1, attempting to add a read lock on row 1, because transaction 1 has been written lock, so transaction 2 waits until transaction 1 completes.

3. If transaction 2 starts first, transaction 1 also needs to wait until transaction 2 is complete and the read lock is released before it can begin execution.

Even if a write operation on a row blocks all read operations on that row, a read operation on a row blocks all writes to that row, and when the system has read and write concurrency, it is limited by the lock and will result in poor performance, regardless of how high the system IO capability is.

MVCC is used to solve this problem to improve the performance of the system, MVCC does not have a unified standard, the implementation of each database is implemented in different ways to implement the Mvcc,innodb is as follows:

Preparatory work:

(1) Add row and delete flag two fields to each row record;

(2) Maintain a global system version number, one transaction at a time (note that select is also a transaction, read transaction), add the system version number to 1 and act as the version number of the transaction

The row flag for the inserted record is set to this transaction version number, and the delete flag is empty;

Delete Record deletion flag set to this transaction version number;

Modified processing: The original record deleted version number is modified to this transaction version number; Insert a new record containing the original record data and this modification, the row record flag is set to this transaction version number, the deletion flag is empty;

Process of reading:

Only record lines that meet the following conditions are read:

(1) The row flag is less than or equal to this transaction version number (equal to the guaranteed ability to read the increase in commits within this transaction);

(2) The deletion flag is empty or greater than this transaction version number (not including equals to ensure that no records are read to the transaction deleted);

Equivalent to the point at which the read transaction begins, a snapshot of the system is established, and all the data read by the transaction is read from the snapshot, thus satisfying the repeatable read condition and solving the problem of phantom reading, and will not read to produce "the same query condition, The first read in a transaction is greater than the number of records read the second time "(caused by concurrent deletion)

From the above, the use of MVCC, most of the reading is no longer required to read the lock, so read no longer block write, write no longer block read. Read operations are only subject to system IO capability.

Database acid, isolation level, and MVCC