MySQL InnoDB How to Guarantee Transaction Features Example Explanation

Preface

If someone asks you, "What are the characteristics of database transactions?" You may quickly answer that atomicity, uniformity, isolation and persistence are ACID characteristics. So do you know how InnoDB guarantees these transaction characteristics? If you know, you can skip this article directly (#. #)

Let's start with the conclusion:

redo log redo logs are used to ensure transaction persistence undo log Rollback Log Guarantees the Atomicity of Transactions undo log + redo log Guarantee the Uniformity of Transactions Locks (shared, exclusive) are used to ensure transaction isolation

Redo Log redo log

The redo log redo log is divided into two parts: one part is the redo log buffer in memory (redo log buffer), which is easy to be lost; Part 2 is the redo log file (redo log file), which is persistent. InnoDB realizes persistence through Force Log at Commit mechanism. When commit, all logs of transactions must be written to redo log file for persistence, and commit operation is completed.

InnoDB writes the contents of the redo log buffer to the redo log file in the following cases:

master thread flushes the redo log buffer to the redo log file every 1 second; When each transaction commits, When the remaining space of the redo log buffer pool is less than 1/2,

To ensure that each log is written to the redo log file, the InnoDB storage engine invokes the fsync (brush disk) operation once each time the log buffer is written to the redo log file. But this is not absolute. The user can control the policy of flushing the redo log to disk by modifying the innodb_flush_log_at_trx_commoit parameter, which can be used as an optimization point when a large number of transactions are committed.

The default value of the 1 parameter, which means that the fsync operation must be called once when the transaction is committed. 0 indicates that when a transaction commits, the redo log cache does not immediately write to the redo log file, but performs fsync operations at Master Thread intervals. 2 indicates that the redo log is written to the redo log file when the transaction commits, but only to the cache of the file system, without fsync operation.
The efficiency of fsync depends on the performance of the disk, so the performance of the disk determines the performance of the transaction commit, that is, the performance of the database. So if someone asks you how to optimize the Mysql database, don't forget to have hardware, let them upgrade the hard disk configuration and replace it with SSD solid-state disk
Redo logs are stored in 512 bytes, which is called redo log block, which is corresponding to the disk sector size of 1, which means that the writing of redo logs can guarantee atomicity and does not need doublewrite technology. It has the following three features: The redo log is generated at the InnoDB layer The redo log is a physical format log that records the changes made to each page The redo log is written continuously and sequentially during the transaction

Rollback Log undo log

In order to satisfy the atomicity of transactions, before any data is operated, the data is backed up to one place (this place where the data backup is stored is called Undo Log), and then the data is modified. If an error occurs or the user executes an ROLLBACK statement, the system can use the backup in Undo Log to restore the data to the state before the transaction began.

undo log implements multi-version concurrency control (MVCC) to help ensure transaction isolation.

The rollback log is different from the redo log, it is a logical log, and the changes to the database are logically canceled. When a transaction rolls back, it actually does the opposite of what it did before. For each INSERT, the InnoDB storage engine will complete one DELETE;; For each UPDATE, the InnoDB storage engine executes an opposite UPDATE.

undo log cannot be deleted immediately after a transaction commits, because there may be other transactions that need undo log to get the previous version of the row record. When the story service is submitted, undo log is put into a linked list. Whether undo log can be deleted can be divided into the following two situations according to different operations:

Insert undo log: A record of an insert operation, visible only to the transaction itself and not to other transactions (this is a requirement for transaction isolation), so the undo log can be deleted directly after the transaction commits. No purge operation is required. update undo log: undo log generated by delete and update operations are recorded. The undo log may need to provide the MVCC mechanism, so it cannot be deleted at transaction commit. Put the undo log linked list on submission and wait for the purge thread to make the final deletion.

Lock

The realization principle of transaction isolation is lock, so isolation can also be called concurrency control, lock and so on. Transaction isolation requires that the object of each read-write transaction can be separated from the operation object of other transactions. Furthermore, such as operating the LRU list in the buffer pool, deleting, adding and moving the elements in the LRU list, the lock is required to intervene in order to ensure the uniformity.

Type of lock

There are two main types of locks in InnoDB: row-level locks and intent locks

Row level lock:

Shared lock (read lock S), which allows transactions to read 1 row of data. A transaction acquires the shared S lock of a 1-row record before it can read the 1-row record and prevent other transactions from adding X locks to it. The purpose of shared locks is to improve read-read concurrency. Exclusive lock (write lock X), which allows transactions to delete or update 1 row of data. A transaction can modify or delete a 1-row record only if it acquires an exclusive X lock on the 1-row record. The purpose of exclusive lock is to ensure the uniformity of data.

Row-level locks are incompatible except S and S.

Intention lock:

Intentional shared lock (read lock IS), transaction wants to acquire a shared lock of several rows of data in a table, and transaction must acquire IS lock of the table before adding a shared lock to a data row. Intentional exclusive lock (write lock IX), a transaction wants to acquire exclusive locks on several rows of data in a table, and the transaction must acquire IX locks on a table before adding exclusive locks to a data row.

Explain the intention lock under 1

The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table.

The primary purpose of intent locking is to indicate that a transaction is locking 1 row or is about to lock 1 row of data. e. g: If the transaction A is to lock X on a row of record r, InnoDB will lock IX on the application form first, and then lock X on record r. Before transaction A completes, transaction B wants a full table operation, at which point IX, directly at the table level, tells transaction B that it needs to wait without having to determine whether there is a lock on every 1 row on the table. The value of intentional exclusive lock lies in saving the positioning and processing performance of InnoDB for locks. Also note that intent locks will not block except for full table scanning.

Algorithm of lock

InnoDB has three row locking algorithms:

Record Lock: Lock on a single row record Gap Lock: Gap lock, locking 1 range, not the record itself Next-Key Lock: Combine Gap Lock and Record Lock to lock a range and lock the record itself. The main problem to be solved is phantom reading at REPEATABLE READ isolation level. You can refer to the article for knowledge points about transaction isolation level.

Here, we mainly talk about Next-Key Lock. Next-key Lock locks not a single value but a range. Its purpose is to prevent multiple transactions from inserting records into the same range, which leads to phantom reading.

Note that Next-Key Lock will be downgraded to Record Lock if the index is only 1, that is, only the index itself, not the range, is locked. That is to say, the precondition of Next-Key Lock is that the transaction isolation level is RR and the query index goes to the non-only 1 index and the primary key index.

Let's use an example to talk about it in detail.

First, create a table:

CREATE TABLE T (id int ,f_id int,PRIMARY KEY (id), KEY(f_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into T SELECT 1,1;
insert into T SELECT 3,1;
insert into T SELECT 5,3;
insert into T SELECT 7,6;
insert into T SELECT 10,8;

Transaction A executes the following statement:

SELECT * FROM T WHERE f_id = 3 FOR UPDATE

In this case, the SQL statement takes a non-only index, so it is locked with Next-Key Locking, and there are two indexes, which need to be locked separately.

For clustered indexes, it only adds Record Lock to indexes where id equals 5. For the secondary index, Next-Key Lock is added to lock the range (1, 3). It is particularly important to note that the InnoDB storage engine will also lock the key value under the secondary index with Gap Lock, that is, the range (3.6).

Therefore, if you execute the following statement in the new session, you will report an error [Err] 1205 - Lock wait timeout exceeded; try restarting transaction :

select * from T where id = 5 lock in share MODE --  Cannot be executed because the transaction A Has been given id=5 Added to the value of X Lock, execution will be blocked 
INSERT INTO T SELECT 4,2 --  Cannot be executed, the value of the secondary index is 2 In ( 1,3 ), blocking is performed within the scope of 
INSERT INTO T SELECT 6,5 --  Can't be executed, gap The lock will lock ( 3,6 ) to perform blocking 

At this time, imagine 1, the transaction A locks the record of f_id = 5, and normally there will be an gap lock that locks (5, 6). If there is no gap lock (5, 6), the user can insert the record with index f_id as 5, so that the transaction A queries again and returns a different record, which leads to the generation of magic reading.

Similarly, if our transaction A executes select * from T where f_id = 10 FOR UPDATE No data is found in the table, but based on Next-Key Lock will lock (8, + ∞), we execute INSERT INTO T SELECT 6,11 It can't be inserted successfully, which fundamentally solves the illusion reading problem.

Summarize