How to ensure transaction characteristics of MySQL InnoDB?

Preface

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

Let me first state the conclusion:

• Redo log is used to ensure the persistence of transactions
• Undo log rollback log ensures the atomicity of transactions
• Undo log + redo log ensures transaction consistency
• Locks (shared, exclusive) are used to ensure transaction isolation

redo log

The redo log redo log is divided into two parts: one is the redo log buffer in the memory, which is easy to lose; the other is the redo log file, which is persistent. InnoDB achieves persistence through the Force Log at Commit mechanism. When committing, all transaction logs must first be written to the redo log file for persistence, and the transaction is considered complete only after the commit operation is completed.

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

• The master thread flushes the redo log buffer to the redo log file every second;
• When each transaction is committed
• When the remaining space in 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 needs to call an fsync (flush) operation each time the log buffer is written to the redo log file. But this is not absolute. Users can control the strategy of flushing redo logs to disk by modifying the innodb_flush_log_at_trx_commoit parameter, which can be used as an optimization point when committing a large number of transactions.

• The default value of 1 indicates that an fsync operation must be called when a transaction is committed.
• 0 means that when a transaction is committed, the redo log cache is not written to the redo log file immediately, but fsync operations are performed at intervals of the Master Thread.
• 2 means that the redo log is written to the redo log file when the transaction is committed, but it is only written to the file system cache and no fsync operation is performed.
  The efficiency of fsync depends on the performance of the disk, so the performance of the disk determines the performance of transaction submission, that is, the performance of the database. So if someone asks you how to optimize the MySQL database, don't forget about the hardware. Ask them to improve the hard disk configuration and switch to SSD solid-state drives. Redo logs are stored in 512-byte blocks, which are called redo log blocks. They are the same size as disk sectors, which means that the writing of redo logs can guarantee atomicity and does not require doublewrite technology. It has the following three features:
• Redo logs are generated at the InnoDB layer
• The redo log is a physical format log that records the changes to each page.
• Redo logs are continuously written during transactions, and are written sequentially.
  

Rollback log undo log

In order to ensure the atomicity of transactions, before operating any data, first back up the data to a place (the place where the data backup is stored is called Undo Log), and then modify the data. If an error occurs or the user executes a ROLLBACK statement, the system can use the backup in the Undo Log to restore the data to the state before the transaction started.

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 that logically cancels all modifications to the database. When a transaction is rolled back, it is actually doing the reverse of what it did previously. For each INSERT, the InnoDB storage engine performs a DELETE; for each UPDATE, the InnoDB storage engine performs a reverse UPDATE.

The undo log cannot be deleted immediately after a transaction is committed, because there may be other transactions that need to use the undo log to obtain the previous version of the row record. When a story task is submitted, the undo log is put into a linked list. Whether the undo log can be deleted depends on the following two situations:

• Insert undo log: The record of insert operation is visible only to the transaction itself and not to other transactions (this is a requirement of transaction isolation). Therefore, the undo log can be deleted directly after the transaction is committed. No purge operation is required.
• update undo log: records the undo logs generated by delete and update operations. The undo log may need to provide an MVCC mechanism, so it cannot be deleted when the transaction is committed. When submitting, it is put into the undo log linked list, waiting for the purge thread to perform the final deletion.

Lock

The implementation principle of transaction isolation is lock, so isolation can also be called concurrency control, lock, etc. Transaction isolation requires that the objects of each read-write transaction be separated from the operation objects of other transactions. Furthermore, for example, when operating the LRU list in the buffer pool, deleting, adding, and moving elements in the LRU list, locks must be involved to ensure consistency.

Types of locks

InnoDB has two main types of locks: row-level locks and intention locks

Row-level locks:

• A shared lock (read lock S) allows a transaction to read a row of data. A transaction can only read a row of records after it obtains a shared S lock on the row, and prevents other transactions from adding X locks to it. The purpose of shared locks is to improve read-read concurrency.
• An exclusive lock (write lock X) allows a transaction to delete or update a row of data. A transaction can modify or delete a row only after it obtains an exclusive X lock on the row. The purpose of exclusive lock is to ensure data consistency.

Among row-level locks, except for S and S, all others are incompatible.

Intention lock:

• Intention shared lock (read lock IS): If a transaction wants to obtain a shared lock for several rows of data in a table, the transaction must first obtain the IS lock of the table before adding a shared lock to a data row.
• Intention exclusive lock (write lock IX). If a transaction wants to obtain exclusive locks for several rows of data in a table, the transaction must first obtain the IX lock of the table before adding an exclusive lock to a data row.

Explain intention lock

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 main purpose of intention locks is to express that a transaction is locking or will lock a row of data. For example, if transaction A wants to apply an X lock to a row of record r, InnoDB will first apply for an IX lock on the table, and then lock the X lock on record r. Before transaction A is completed, transaction B wants to perform a full table operation. At this time, IX at the table level directly tells transaction B to wait without having to determine whether each row in the table has a lock. The value of intention exclusive locks lies in saving InnoDB's lock positioning and processing performance. Also note that intention locks will not block except for full table scans.

Locking Algorithm

InnoDB has three row lock algorithms:

• Record Lock: lock on a single row record
• Gap Lock: Gap lock, locks a range, not the record itself
• Next-Key Lock: Combines Gap Lock and Record Lock to lock a range and the record itself. The main problem to be solved is phantom read under REPEATABLE READ isolation level. You can refer to the article to learn about transaction isolation levels.

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, thereby causing phantom reads.

Note that if a unique index is used, Next-Key Lock will be downgraded to Record Lock, which means that only the index itself is locked, not the range. That is to say, the prerequisite for Next-Key Lock is that the transaction isolation level is RR and the queried index is a non-unique index or a primary key index.

Let's explain this in detail using an example.

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

At this time, the SQL statement uses a non-unique index, so Next-Key Locking is used, and there are two indexes, which need to be locked separately.

For the clustered index, it only adds Record Lock to the index with id equal to 5. For the auxiliary index, Next-Key Lock is added to lock the range (1,3). It is particularly important to note that the InnoDB storage engine also adds a Gap Lock to the next key value of the auxiliary index, that is, a lock in the range (3.6).

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

select * from T where id = 5 lock in share MODE -- cannot be executed, because transaction A has added an X lock to the value of id = 5, and the execution will be blocked. INSERT INTO T SELECT 4,2 -- cannot be executed, the value of the auxiliary index is 2, which is in the range of (1,3), and the execution is blocked. INSERT INTO T SELECT 6,5 -- cannot be executed, the gap lock will lock the range of (3,6), and the execution is blocked.

Now imagine that transaction A locks the record with f_id = 5. Normally, there will be a gap lock that locks (5, 6). If there is no gap lock on (5, 6), the user can insert a record with index f_id 5. Then, transaction A will return a different record when querying again, which will lead to phantom reads.

Similarly, if our transaction A executes select * from T where f_id = 10 FOR UPDATE , no data can be found in the table. However, based on Next-Key Lock, (8, +∞) will be locked. We cannot successfully insert INSERT INTO T SELECT 6,11 , which fundamentally solves the phantom read problem.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.