MySQL series of 24 isolation levels and lock

Transaction

1. Definition: All operations must be completed successfully, otherwise all changes made in each operation will be revoked.

2, the acid of the transaction

Atomicity atomicity Consistency Consistency isolation isolation persistence durability

2-1. Atomicity: A transaction is a logical working unit of a database, and each operation contained in a transaction is either done or not done

2-2, consistency: The result of transaction execution must be the database from one consistent state to another consistency state. Therefore, when the database contains only the results of a successful transaction submission,

Indicates that the database is in a consistent state.

2-3, Isolation: The execution of a transaction cannot be disturbed by other transactions.

2-4, Persistence: That is, permanent, once a transaction is committed, it will change the data in the database should be a permanent line.

3. MySQL's four-middle isolation level

3-1. READ UNCOMMITTED (reads uncommitted)

All transactions can see the execution results of other uncommitted transactions. Also known as dirty reading, seldom applied to actual

3-2. Read Committed (reads the submission content)

A transaction can only see changes that have been committed by the firm.

3-3, REPEATABLE read (REPEATABLE Read)

MySQL default transaction isolation level, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data. However, this can lead to phantom reading.

Phantom reading refers to when a user reads a range of data rows, and another transaction inserts a new row within that range. When the user reads the data row of the range again, a new phantom row appears

3-4, serializable (serializable)

A shared lock is added to each read row of data. This level can cause a large number of timeouts and lock competitions.

4, the principle of transaction isolation

1, one block or two segment lock

In a large number of concurrent accesses, in order to prevent deadlocks, the general application is recommended to use a blocking method, that is, at the beginning of the method, you know what data will be used in advance,

Then all locks, after the method is run, is unlocked at all. However, this approach does not apply to the database because the database does not know what data will be used.

The database is a two-stage lock protocol, which divides the transaction into two stages, and locks the stage and unlocks the phase.

Lock phase: Before reading any data to request and obtain S lock (shared lock, other transactions can continue to add a shared lock, but not the lock), in the Write

Before the operation, you need to apply for an X lock (exclusive lock, other transactions can no longer acquire any locks). If the lock is unsuccessful, the transaction goes into a wait state until locking succeeds to continue execution.

Unlocking phase: When a transaction releases a block, the transaction enters the unlock phase, and the lock operation cannot be resolved until the unlock operation is performed at that stage.

The difference between non-repeatable reads and Phantom reads:

Non-repeatable reading focuses on update and delete, while Phantom reads the insert

5, MVCC in the MySQL innodb realization

In InnoDB, two additional hidden values are added after each row of data to implement MVCC, which is a record of when this row of data is created and another record of the row

When the data expires (or is deleted) in the actual operation, the storage is not the time, but the version number of the transaction, each opening a new transaction, the version number of the transaction will be

Increasing. Under the REPEATABLE READ Transaction ISOLATION level:

Select, read create version number is not greater than current transaction version, delete version number is empty or larger than current transaction version number

When insert, keeps the current transaction version number as the created version number of the row

Delete, keep the current version number of the deleted version number of the line

UPDATE, insert a new record, save the current transaction version number for the line created by the version number, while maintaining the current transaction version number to the original deleted row

MySQL series of 24 isolation levels and lock