MySQL lock, transactional, multi-version concurrency control basics for high-performance MySQL reading notes

1.2 Concurrency control
1.2.1 Read/write lock
When dealing with concurrent reads or writes, the problem is solved by implementing a locking system consisting of two types of locks. These two types of locks are often referred to as shared locks and exclusive locks (exclusive lock), which are also called read locks and write locks (write lock).
Read locks are shared, or are not blocked from each other. Multiple clients can read the same resource at the same time without interfering with each other. Write locks are exclusive, meaning that a write lock blocks other write and read locks.
1.2.2 Lock particle size
To increase the concurrency of shared resources, try to lock only part of the data that needs to be modified, not all data.
Table lock

Table locks are the most basic lock policy in MySQL, and are the least expensive policies. Write operations (INSERT, delete, UPDATE, etc.) require a write lock first, which blocks all read and write operations to the table by other users. Read locks are not blocked from each other. A write lock has a higher priority than a read lock, so a write lock request may be inserted in front of the read lock queue (the write lock can be inserted in front of the lock queue, whereas a read lock cannot be inserted in front of the write lock).
Statements such as ALTER table use table locks, and the lock mechanism of the storage engine is ignored.
Row-level lock (Row lock)
Row-level locks can support concurrent processing to the maximum, but at the same time they come with the largest lock overhead. The storage engine, such as MySQL's InnoDB and xtradb, implements row-level locks. Row-level locks are implemented only at the storage engine level, and the server layer does not understand the lock implementation in the storage engine at all.
1.3 Business
A transaction is a set of atomic SQL queries that either execute successfully or all fail.
The acid concept of a transaction:
Atomicity (atomicity): A transaction must be considered an indivisible minimum unit of work.
Consistency (consistency):
Isolation (Isolation): In general, changes made by one firm are not visible to other transactions until they are finally committed (also depending on the isolation level (isolation levels)).
Persistence (Durability): Once a transaction commits, its modifications are persisted to the database.
1.3.1 Isolation Level
There are four isolation levels defined in SQL:
READ UNCOMMITTED: Changes in the transaction, even if not committed, are visible to other transactions, the transaction can read uncommitted data, also known as dirty Read (Dirty read), is deprecated.
Read COMMITTED: At the beginning of a transaction, you can see only the changes that have been made to the transactions that have been committed, that is, any changes made to the transaction from the beginning until the commit are not visible to other transactions.
REPEATABLE READ (repeatable read): Resolves the problem of dirty reads, ensuring that the same transaction, the results of multiple reads of the same record are consistent. Repeatable reads are the default transaction isolation level for MySQL.
SERIALIZABLE (SERIALIZABLE): Serializable is the highest isolation level, which is enforced by forcing transaction serial execution, which avoids phantom reads, but causes a large number of timeouts and lock contention issues.
Phantom Read (Phantom Read): When a transaction reads a range of records, another transaction inserts a new record in that range, and when the previous transaction reads the records in that range again, a magic line (Phantom row) is generated.
1.3.2 Deadlock
A deadlock is a vicious cycle in which two or more transactions occupy each other on the same resource and request a lock on the resources occupied by the other. There are two reasons for deadlocks: Some are real data collisions, and some are caused by the way the storage engine is implemented.
The InnoDB storage engine detects the cyclic dependency of a deadlock and returns an error immediately. InnoDB currently handles deadlocks by rolling back transactions that hold the fewest row-level exclusive locks. When a deadlock occurs, only partially or completely rolls back one of the transactions to break the deadlock. In most cases, you simply need to re-execute the transaction that was rolled back because of a deadlock.
1.4 Multi-version concurrency control (MVCC)
It can be considered that MVCC is a variant of the row-level lock, which implements non-blocking reads, and the write operation locks only the necessary rows.
The implementation of MVCC is achieved by saving the snapshot of the data at a certain point in time. For multiple transactions, depending on the start time of the transaction, the data that is seen at the same time for each transaction on the same table may be different.
InnoDB's MVCC is achieved by saving two hidden columns after each row of records. These two columns, a Save row creation time, a save row expiration time (or delete time). Of course, it is not the actual time value that is stored, but the system version number. The system version number is automatically incremented for each transaction that starts. The system version number at the start of a transaction is used as the version number of the transaction to compare to the version number of each row of records queried.
Repeatable read isolation level, MVCC specific operation mode:
Select:innodb will check each line of records based on two criteria:
A. InnoDB only the data rows that have a version number earlier than the current transaction version (the system version number of the row is less than or equal to the system version number of the transaction), which guarantees that the transaction reads the rows that existed before the transaction started, either by the transaction itself or by a modified one.
B. The deleted version of the row is either undefined or larger than the version number of the current transaction. This ensures that the transaction is read to a row that has not been deleted before the transaction begins.
INSERT:
InnoDB saves the current system version number as the row version number for each newly inserted row.
DELETE:
InnoDB saves the current system version number for each row deleted as a row delete identity.
UPDATE:
InnoDB is the new record inserted, save the current system version number as the line version number, and save the current system version number to the original line as the row to delete the identity.

MVCC only works under the two isolation levels of repeatable read and Read Committed.

MySQL lock, transactional, multi-version concurrency control basics for high-performance MySQL reading notes