Shulou(Shulou.com)06/01 Report--

Preface

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

Let's start with the conclusion:

Redo log redo logs are used to ensure the persistence of transactions undo log rollback logs ensure the atomicity of transactions undo log+redo log guarantee transaction consistency locks (sharing, exclusiveness) are used to ensure transaction isolation

Redo log redo log

The redo log redo log is divided into two parts: one is the in-memory redo log buffer (redo log buffer), which is easy to lose, and the other is the redo log file (redo log file), which is persistent. InnoDB achieves persistence through the Force Log at Commit mechanism. When commit, all logs of the transaction must be written to the redo log file for persistence until the 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 second; when each transaction commits, 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 invoke a fsync 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 a large number of transactions are committed.

The default value of the 1 parameter means that the fsync operation must be called once when the transaction is committed. 0 means that when a transaction commits, the redo log cache does not immediately write to the redo log file, but instead performs fsync operations at intervals of Master Thread. 2 means that when a transaction commits, the redo log is written to the redo log file, 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 transaction commit, that is, the performance of the database. So if someone asks you how to optimize your Mysql database, don't forget to have hardware, ask them to upgrade their hard drive configuration and switch to SSD solid state drives.

Redo logs are stored in 512 bytes, called redo log blocks, which is the same size as the disk sector, which means that focusing on log writing can ensure atomicity and does not require doublewrite technology. It has the following three features: the redo log generated at the InnoDB layer is a physical format log, which records that the modification redo log for each page is constantly written during the transaction and is written sequentially.

Roll back log undo log

In order to satisfy the atomicity of the transaction, before manipulating any data, first back up the data to a place (the place where the data backup is called Undo Log) and then modify the data. If an error occurs or the user executes the ROLLBACK statement, the system can use the backup in Undo Log to restore the data to the state it was 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 all changes to the database are logically cancelled. When a transaction rolls back, it actually does the opposite of what it did before. A DELETE; is completed for each INSERT,InnoDB storage engine and an opposite UPDATE is performed for each UPDATE,InnoDB storage engine.

The undo log cannot be deleted immediately after the transaction is committed, because there may be other transactions that need to go through undo log to get the previous version of the row record. Therefore, if the undo log is put into a linked list when the transaction is submitted, whether the undo log can be deleted can be divided into the following two situations according to the operation:

Insert undo log: the record of the insert operation is visible only to the transaction itself, but not to other transactions (this is a requirement of transaction isolation), so the undo log can be deleted directly after the transaction commits. No purge operation is required. Update undo log: records the undo log generated for delete and update operations. The undo log may need to provide a MVCC mechanism, so it cannot be deleted when the transaction commits. Put into the undo log linked list when submitting and wait for the purge thread to make the final deletion.

Lock

The principle of transaction isolation is lock, so isolation can also be called concurrency control, lock and so on. The isolation of transactions requires that the objects of each read-write transaction can be separated from the operands of other transactions. In addition, such as manipulating the LRU list in the buffer pool, deleting, adding, and moving elements in the LRU list, lock intervention is required to ensure consistency.

Type of lock

There are mainly two kinds of locks in InnoDB: row level lock and intention lock.

Row-level locks:

The shared lock (read lock S) allows the transaction to read a row of data. A transaction gets a shared S lock for a row of records before it can read that row and prevent other transactions from adding X locks to it. The purpose of shared lock is to improve read concurrency. An exclusive lock (write lock X) that allows a transaction to delete or update a row of data. A transaction can modify or delete a row only if it gets the exclusive X lock of a row. The purpose of exclusive lock is to ensure the consistency of data.

Row-level locks are incompatible except for S and S compatibility.

Intention lock:

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

Explain the 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 an intention lock is to express that a transaction is locking a row or is about to lock a row of data. E.g: if transaction A puts an X lock on one row of record r, InnoDB will first apply for the IX lock of the table, and then lock the X lock of record r. Before transaction A completes, transaction B wants a full table operation, so the IX at the table level tells transaction B that it needs to wait without determining whether each row has a lock on the table. The value of the existence of intentional exclusive locks is to save the positioning and processing performance of InnoDB locks. Also note that there is no blocking except for full table scanning with externally oriented locks.

Algorithm of lock

InnoDB has three algorithms for row locking:

Record Lock: lock on a single row record Gap Lock: gap lock, lock a range, not the record itself Next-Key Lock: combine Gap Lock and Record Lock, lock a range, and lock the record itself. The main problem solved is the phantom reading under the REPEATABLE READ isolation level. You can refer to the article to learn about the level of transaction isolation.

Here we mainly talk about Next-Key Lock, which uses Next-key Lock to lock not a single value but a range, and its purpose is to prevent multiple transactions from inserting records into the same range, resulting in phantom reading.

Note that if you take a unique index, Next-Key Lock is downgraded to Record Lock, that is, only the index itself is locked, not the scope. In other words, the Next-Key Lock precondition is a non-unique index or primary key index with a transaction isolation level of RR and the index of the query.

Let's elaborate on it with an example.

First, create a table:

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

Transaction An executes the following statement:

SELECT * FROM T WHERE f_id = 3 FOR UPDATE

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

For clustered indexes, it only adds Record Lock to indexes with id equal to 5. As for the secondary index, it adds Next-Key Lock to lock the range (1J3), and it is important to note that the InnoDB storage engine also adds a Gap Lock to the next key value of the secondary index, that is, the lock of the range.

So if you execute the following statement in the new session, you will get 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 transaction A has already added an X lock to the value of id=5, execution will be blocked INSERT INTO T SELECT 4 INSERT INTO T SELECT 2-cannot be executed, the value of the secondary index is 2, within the range of (1 dint 3), execution blocking INSERT INTO T SELECT 6 5-cannot be executed, the gap lock will lock the range (3pm 6) and perform blocking

At this point, imagine that transaction A locks the record with f_id = 5, and there will normally be a gap lock locked (5Magne6), then if there is no gap lock (5Power6), then the user can insert the record with an index f_id of 5, so that transaction A will return a different record when queried again, which leads to phantom reading.

By the same token, if our transaction An executes select * from T where f_id = 10 FOR UPDATE and no data is found in the table, but based on the fact that Next-Key Lock will be locked (8meme + ∞), we will not be able to insert INSERT INTO T SELECT 6 and 11 successfully, which fundamentally solves the problem of phantom reading.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.