How MySQL InnoDB transactional characteristics guarantee

If someone asks you, “What are characteristics of the database transaction”? You may soon answer the atomicity, consistency, isolation, durability namely ACID properties. Then you know how InnoDB transactional features to ensure that these do? If you know, then you can skip this article does not Kanla (# ^. ^ #)

Let me talk about the conclusion:

    redo log redo logs to ensure durability of transactions

    undo log rolling log to ensure transaction atomicity

    Consistency undo log + redo log to ensure that matters

    Lock (shared, exclusive) to ensure transaction isolation

Redo log redo log

Redo log redo log is divided into two parts: one is the memory of the redo log buffer (redo log buffer), is easily lost; the second part is the redo log files (redo log file), it is persistent. InnoDB is achieved by Force Log at Commit persistence mechanism, when the commit, you must first log all transactions are written to redo log files persist until the commit operation is completed is completed.
    InnoDB under the following circumstances will the contents of the redo log buffer is written redo log file:

    master thread per second refresh redo log buffer to redo log files;

    Each transaction commit time

    When the redo log buffer pool space is less than 1/2 of the remaining

In order to ensure that each redo log files are written to the log after each log buffer is written redo log file, InnoDB storage engine needs to be called once fsync (brush plate) operation. But this is not absolute. Users can be controlled by modifying the parameters innodb_flush_log_at_trx_commoit redo log flushed to disk strategy that can be used as the optimization point when a large number of the transaction commits.

    1 parameter default values, expressed the need to call fsync operation once the transaction commits.

    0 indicates that the transaction commits, the redo log buffer is not written to redo log files immediately, but with interval Master Thread performed fsync operation.

    2 represents the transaction redo log write redo log file when submitted, but only written to the cache file system, without fsync operation.
            fsync efficiency depends on the performance of the disk, so disk performance determines the performance of the transaction is committed, that is, the performance of the database. So if someone asks you how to optimize the Mysql database, do not forget that there is a hardware, allowing them to upgrade the hard drive configuration, for SSD solid state drive
            Redo logs are stored in 512 bytes, called redo log block, consistent with the disk sector size, this means that the redo log may be written to guarantee atomicity, no doublewrite technology. It has the following three characteristics:

    Layer is in the redo log generated InnoDB

    Redo log is a physical format of the log record is modified for each page

    Redo logs continue to be written in a transaction in progress, but sequential write

Rollback log undo log

In order to satisfy the atomic transaction, before any data operation, data is first backed up to a place (where the backup data is stored is referred to Undo Log), and then modify the data. If an error or a user performs a ROLLBACK statement, the system can use the backup Undo Log in to restore the data to its state before the transaction began.
    undo log multi-version concurrency control (MVCC) to assist ensure transaction isolation.

Rollback log is different from the redo logs, it is logical log, cancel the modifications are logical database. When the transaction is rolled back, it is actually doing is the opposite of the previous work. For each INSERT, InnoDB the DELETE a storage engine will be completed; for each UPDATE, InnoDB storage engine will perform a reverse UPDATE.

After the transaction is committed and can not be deleted immediately undo log, because there may be other matters need to get previous versions of rows by undo log. The story will undo log into the service to submit a linked list, if you can delete undo log based on different operating points of the following two cases:

    Insert undo log: record insert operation, visible only to their own affairs, not visible to other transactions (as required by isolation of affairs), so the undo log can be deleted directly after the transaction commits. No need for purge operations.

    update undo log: record delete and update operations is generated undo log. The undo log may need to provide MVCC mechanism, and therefore it can not be removed when the transaction commits. When submitting the list into the undo log, waiting for final purge thread deleted.


The principle is the isolation of the transaction lock, thus isolation can also be called concurrency control, locking and so on. Transaction isolation requirements for each read and write transactions to the operation target object other matters can be separated from each other. Furthermore, such operations LRU list in the buffer pool, delete, add, move elements LRU list, in order to ensure consistency then it would lock intervention.

The type of lock

InnoDB There are two kinds of locks: row-level locking, intent lock

Row-level locking:

    Shared lock (read lock S), to allow the transaction to read a row of data. Affairs to get a row of record shared lock S, can read this line, and to prevent its affairs other add X lock. The purpose is to improve the shared lock read concurrency.

    Exclusive lock (write lock X), allowing the transaction to update or delete a row data row of data. Transaction records to get a line of exclusive X lock, can modify or delete this line. Exclusive lock aim is to ensure data consistency.

Row-level locking, in addition to S and S compatible, others are not compatible.

Intent locks:

    Intent shared lock (read lock IS), transaction wants to acquire a shared lock a few rows of a table, before a transaction to rows of data shared locks must obtain the IS lock on the table.

    Intent exclusive lock (write lock IX), transaction wants to acquire an exclusive lock on a table a few rows of data, transactions, he must obtain before the lock IX lock on the table to add a row of data rows.
            Explain intent locks

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 is to express intent locks are locked in a transaction line or row of data you want to lock. e.g: A transaction to be carried out on the X lock on a row r, then InnoDB will first apply IX lock on a table, then lock records r X-lock. A completed before the transaction, the transaction B wants to operate a full table, this time directly at the table level of transaction B IX told to wait on the table without the need to determine whether each line has a lock. Value intent exclusive lock exists that InnoDB savings and handling properties for positioning a lock. Also pay attention, in addition to a full table scan intent lock will not be blocked.

Lock algorithm

There are three InnoDB row lock algorithm:

    Record Lock: lock on a single row record

    Gap Lock: gap locks, lock a range, rather than the records themselves

    Next-Key Lock: binding and Gap Lock Record Lock, a lock range, and the lock record itself. The main problem is the phantom read at REPEATABLE READ isolation level. You can refer to the article for the knowledge transaction isolation level.

Here mainly to talk about Next-Key Lock, use Next-key Lock Lock is not a single value but a range, his purpose is to prevent multiple transactions to insert records into the same range leading to phantom reads.

Note, if a unique index to go, then the Next-Key Lock will be downgraded to a Record Lock, that is, only lock the index itself, rather than a range. That non-unique index Next-Key Lock pre-condition for the transaction isolation level for the index RR and inquiries go, the primary key index.

Let’s use an example detail about.
    First, establish 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;

A transaction execute the following statement:


Then take the non-unique index SQL statement, so use Next-Key Locking locked, and there are two indexes, it needs to be locked separately.
    For clustered indexes, which only index id equal to 5 plus the Record Lock. For secondary indexes, together with its Next-Key Lock, locked the range (1,3), in particular, to note that, InnoDB storage engine will be a key index of the aid plus Gap Lock, namely the range of (3.6) locks.
    So if you execute the following statement on a new session will be in error [Err] 1205 – Lock wait timeout exceeded; try restarting transaction:

select * from T where id = 5 lock in share MODE -- 不能执行,因为事务A已经给id=5的值加上了X锁,执行会被阻塞
INSERT INTO T SELECT 4,2  -- 不能执行,辅助索引的值为2,在(1,3)的范围内,执行阻塞
INSERT INTO T SELECT 6,5  -- 不能执行,gap锁会锁住(3,6)的范围,执行阻塞

Imagine this time, transaction A locks record f_id = 5, normally there will be a gap lock, lock (5,6), then if there is no (5,6) of the lock gap, then the user can insert index f_id 5 records, so that the transaction a query will return again to a different record, it led to the creation of phantom reads.

Similarly, if we perform a transaction A select * from T where f_id = 10 FOR UPDATE, finding the data in the table, but based on the Next-Key Lock will lock (8, + ∞), we performed INSERT INTO T SELECT 6,11 is not successful insertion, which solves the phantom read problem fundamentally.

Leave a Reply