Various locks and transaction principles in InnoDB

InnoDB data page structure

image

The file header occupies 38 bytes, the data page header occupies 56 bytes, the largest record and the smallest record occupies 26 bytes, the size of the data row area is not fixed, and the size of the free area is also not fixed. , The size of the data page directory is also not fixed, and the end of the file occupies 8 bytes.

Locks in the InnoDB storage engine

Shared lock (S Lock), allows transactions to read a row of data.

Exclusive lock (X Lock), allows transactions to delete or update a row of data.

Intentional shared lock (IS Lock), the transaction wants to obtain a shared lock on certain rows in a table

Intent exclusive lock (IX Lock), the transaction wants to obtain an exclusive lock on certain rows in a table

Consistent non-locking read

Consistent nonlocking read refers to that the InnoDB storage engine reads data in the database at the current execution time through multiversioning. If the read row is performing a DELETE or UPDATE operation, then the read operation will not wait for the release of the row lock. Conversely, the InnoDB storage engine will read a snapshot data of the row. The realization is done through the undo segment.

Consistency lock read

The MySQL default transaction isolation level is REPEATABLE READ, and the SELECT operation of the InnoDB storage engine uses consistent non-locking read.

However, in some cases, users need to explicitly lock the database read operation to ensure the consistency of the data logic. The InnoDB storage engine supports two consistent locking read operations for SELECT statements:

SELECT...FOR UPDATE

SELECT…LOCK IN SHARE MODE

SELECT...FOR UPDATE adds an X lock (exclusive lock) to the rows that are read, and other transactions cannot add any locks to the locked rows. SELECT...LOCK IN SHARE MODE adds an S lock (shared lock) to the read row record. Other transactions can add an S lock to the locked row, but if an X lock is added, it will be blocked.

Lock problem

Dirty read

Dirty data refers to the modification of the row record in the buffer pool by the transaction, and it has not yet been committed (commit). At this time, the data read by other transactions is dirty read. Read the uncommitted data

Non-repeatable

Non-repeatable read refers to reading the same data set multiple times in a transaction. Before the end of this transaction, another transaction also accessed the same data set and performed some DML operations. Therefore, between the two read data in the first transaction, due to the modification of the second transaction, the data read twice in the first transaction may be different. In this way, the data read twice in a transaction is different. This situation is called non-repeatable read. Read the submitted data

Phantom reading

Phantom read refers to the inconsistency of the number of records in a transaction based on the same condition multiple times.

Realization of the transaction

The transactions in the InnoDB storage engine fully comply with the characteristics of ACID.

ACID is an abbreviation of the following 4 words:

Atomicity

Consistency

Isolation

Durability

Persistence

The redo log is used to achieve the durability of the transaction, that is, the D in the transaction ACID. It consists of two parts: one is the redo log buffer in memory, which is volatile; the other is the redo log file, which is persistent.

InnoDB is a transaction storage engine. It uses the Force Log at Commit mechanism to achieve transaction persistence, that is, when the transaction is committed (COMMIT), all logs of the transaction must be written to the redo log file for persistence. The COMMIT operation is completed.

Atomicity

The undo log rollback log is used to achieve the atomicity of the transaction

When modifying the database, the InnoDB storage engine will not only generate redo, but also a certain amount of undo. In this way, if the transaction or statement executed by the user fails for some reason, or the user requests a rollback with a ROLLBACK statement, the undo information can be used to roll back the data to the way it was before the modification.

Isolation

The four isolation levels defined by the SQL standard are:

READ UNCOMMITTED (read uncommitted)

READ COMMITTED (read has been submitted)

REPEATABLE READ (repeatable read) ------ MySQL default isolation level

SERIALIZABLE (Serialization)

The isolation of concurrent execution of transactions under the default isolation level (RR) is achieved by the classic MVCC multi-version concurrency control mechanism, which solves the problems of dirty writing, dirty reading, non-repeatable reading, and phantom reading.

undo log version chain

The undo log is divided into:

insert undo log

update undo log

Insert undo log refers to the undo log generated in the insert operation. Because the record of the insert operation is only visible to the transaction itself and not to other transactions (this is a requirement of transaction isolation), the undo log can be deleted directly after the transaction is committed. No purge operation is required.

The update undo log records the undo log generated for delete and update operations. The undo log may need to provide an MVCC mechanism, so it cannot be deleted when the transaction is committed. Put it into the undo log linked list when submitting, and wait for the purge thread to perform the final deletion.

Each piece of data in the update undo log actually has two hidden fields, one is DATA_TRX_ID and the other is DATA_ROLL_PTR. This DATA_TRX_ID is the transaction id of the last update of this data, and DATA_ROLL_PTR points to the undo log generated before you update this transaction.

image

ReadView mechanism

ReadView is a collection of all current transactions when the transaction is opened.

m_ids------This means which transactions are executed in MySQL and have not yet been committed at this time

min_trx_id------ is the smallest value in m_ids

max_trx_id------This means that the next transaction id to be generated by mysql is the maximum transaction id

creator_trx_id------ is the id of your transaction

Detailed

Perform two transactions concurrently

Transaction A (trx_id=45) Transaction B (trx_id=59)

A ReadView is generated at this time

In the scenario of transaction A

If the trx_id attribute value of the accessed version is less than the min_trx_id value in ReadView, it indicates that the transaction that generated this version has been committed before the current transaction generates ReadView, so this version can be accessed by the current transaction.

image

If the trx_id attribute value of the accessed version is the same as the creator_trx_id value in ReadView, it means that the current transaction is accessing its own modified record, so this version can be accessed by the current transaction.

image

If the trx_id attribute value of the accessed version is between the min_trx_id and max_trx_id of ReadView, then you need to determine whether the trx_id attribute value is in the m_ids list. If it is, it means that the transaction that generated this version when the ReadView was created is still active. Cannot be accessed; if not, it means that the transaction that generated the version when the ReadView was created has been committed and the version can be accessed.

image

If the trx_id attribute value of the accessed version is greater than or equal to the max_trx_id value in ReadView, it indicates that the transaction that generated this version is started after the current transaction generates ReadView, so this version cannot be accessed by the current transaction.

image

When MySQL implements the MVCC mechanism, it is based on the undo log multi-version chain + ReadView mechanism. The default RR isolation level is implemented based on this mechanism. Relying on this mechanism to achieve the RR level, in addition to avoiding dirty writing, Dirty reading and non-repeatable reading can also avoid the problem of phantom reading.

consistency

The purpose of atomicity, durability, and isolation is also to ensure data consistency!