Mysql – Can select read index page while modify this page in innodb

innodbMySQL

When mysql innodb insert or update secondary index page , will at the same time this index page can searched by select ?

Best Answer

Yes, that's possible, that's what the change buffer is for. It caches changes to secondary indexes. When data is read via an index and (parts of) the index is in the change buffer, it's read from there. The changes are written to disk periodically.

The change buffer is a special data structure that caches changes to secondary index pages when affected pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.
...
Periodically, the purge operation that runs when the system is mostly idle, or during a slow shutdown, writes the updated index pages to disk.

Read about it in more detail here.

Also creating/dropping/changing an index is possible while selects/insert/updates/deletes can be performed at the same time with online DDL (at least with MySQL >= 5.6).

Related Solutions

Mysql – How long can Deadlock info hold in innodb status page

The first thing you must look for is the setting for innodb_lock_wait_timeout.

Suppose you run this SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; and get

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql>

At the very least, you should rightly expect a newly detected deadlock on a busy DB server every 50 seconds.

InnoDB cannot detect deadlock under two conditions:

LOCK TABLES is explicit executed
A lock set by a storage engine other than InnoDB is involved

These two conditions are usually self-inflicted and relatively easy to find. Setting the innodb_lock_wait_timeout would be the usual workaround.

However, there is a one boobytrap you need to be aware of. It is possible for SELECT queries to create row-level locks. Many developers are not aware of this quirky behavior. In fact, according to the MySQL Documentation:

If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

I discussed this back on Aug 08, 2011 : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

RECOMMENDATIONS

Spend time searching for SELECTs that do JOINs of InnoDB and MyISAM tables (hopefully you have none). If any, please convert the MyISAM tables to InnoDB so that InnoDB locks can be better monitored and better managed.
Search carefully for deadly embrace situations
Avoid doing mysqldumps with --lock-tables and --single-transaction mixed.

With these recommendations, you can hopefully assure that the deadlock status in SHOW ENGINE INNODB STATUS\G should be updated with a degree of regularity.

Mysql – Finding and fixing InnoDB index corruption

Thanks Rolando and Michael for your responses.

To close the loop, here's the answer I came up with for my original questions:

Q: How can I determine if other tables have similar index corruption?
A: Use CHECK TABLE. I ran mysqlcheck -c on all of the relevant InnoDB tables to find out which ones had index corruption
Q: What's the most efficient way to fix the corrupt indexes?
A: Use OPTIMIZE TABLE to rebuid the InnoDB table that have corrupt indexes. This causes a complete table rebuild which fixes the corruption.

Best Answer

Related Solutions

Mysql – How long can Deadlock info hold in innodb status page

RECOMMENDATIONS

Mysql – Finding and fixing InnoDB index corruption

Related Question