1

I need to know if is neccesary or recommended to try a LOCK TABLE before a REPAIR TABLE, because sometimes we need to make REPAIR of tables when users are working on the application.

As I have tested, it is possible (if the table is not really badly corrupted, like a .MYI missing), but, official documentation of MySQL dont say's anything about this scenario (at least in the https://dev.mysql.com/doc/refman/5.7/en/repair-table.html documentation).

1

Demo:

Screen 1:

mysql> create table mytable (id int primary key) engine=myisam;

mysql> insert into mytable values (1);

mysql> select sleep(60) from mytable;
(waits)

Screen 2:

mysql> set lock_wait_timeout=1;
Query OK, 0 rows affected (0.00 sec)

mysql> repair table mytable;
+---------------+--------+----------+--------------------------------------------------------+
| Table         | Op     | Msg_type | Msg_text                                               |
+---------------+--------+----------+--------------------------------------------------------+
| test2.mytable | repair | Error    | Lock wait timeout exceeded; try restarting transaction |
| test2.mytable | repair | status   | Operation failed                                       |
+---------------+--------+----------+--------------------------------------------------------+

What's going on here is that any SQL query acquires a shared metadata lock on the referenced table. So there is implicitly a shared table lock on the table created by the SELECT in the first window. Other concurrent sessions can also acquire shared metadata locks without blocking each other.

The REPAIR TABLE, like other DDL statements, requires an exclusive metadata lock. Any metadata lock held by another session blocks it. In other words, a DDL statement will be blocked until there is no metadata lock on the table by any other session.

So no, you don't need to use LOCK TABLES explicitly. REPAIR TABLE waits for its own table lock automatically.


I agree with the answer from Rick James that MyISAM tables are obsolete. I don't use MyISAM tables if I can avoid them.

1
  • Appreciated your help and help from Rick James, I agree too, we are in transition to InnoDB engine. For now, I needed to resolve this problem.
    – Keos
    Jan 7 at 16:23
1

It smells like your table is using ENGINE=MyISAM. (This smell is confirmed by "like a .MYI missing".) InnoDB has been the preferred engine for several years now. I strongly recommend changing.

After a non-graceful shutdown (eg, crash), MyISAM almost always needs tables to be REPAIRed. InnoDB, on the other hand, automatically repairs the damage caused by the crash, and does it much faster.

Also, because of the "transactions", InnoDB has almost no need for LOCK TABLES.

Bottom line: With InnoDB, your question becomes moot.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.