Search code examples
mysqlindexingdeadlockmysql-5.7

How indicies updates are executed in InnoDB mySQL 5.7?

Engine: MySQL 5.7 ( InnoDB )

Metadata about table:

  1. I have relatively big table with hundreds of thousands of rows and like 20 columns.
  2. Table lists child entities for parent entities.
  3. It's being partitioned based on client id (isolation of client data). We have 400 partitions defined for it, but in test env I am in, there are only 7 active clients - there is data in 7 out of 400 of these partitions.
  4. Table has few indicies: Primary (unique id of the row + clientId [partition key]), id of parent entity and by one more text field.

Steps to reproduce

  1. I am running mySql db dump restore utility in Docker container table finishes at 240Mb.
  2. Then I'm executing run migrations script, which alters structure of this table - updates columns which are not parts of the indicies. Namely - changes updatedAt and createdAt fields type from DATETIME to DATETIME(3).

Observable behaviour After this db restore from dump and applying of migration tests in our solution very frequently face deadlocks for indicies in this table.

We fixed this issue by making db dump with these changes, so migrations are not altering anything anymore.

Question: How indicies updates are executed - as part sql statements, which are finished (application code which awaits for them is finished with waiting and next code statements are executed)?

Our code is synchronous and awaits for places where we execute queries.

Solution

  • I think the answer to your question is that indexes are updated immediately. That is, if you INSERT/UPDATE/DELETE, the index is effectively updated before the query executed returns control to the client.

    InnoDB does some other things in the background, but these are transparent to the client. The client's next SQL query can take advantage of the new index entries immediately, even though InnoDB take some time to merge those index entries into place. You can read more details about this here: https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer.html

    But I have a feeling that this is actually not the solution to the locking problems you describe.