TRUNCATE TABLE statement sometimes hangs

Question

Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue?

I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB.

The hanging statement is simply:

TRUNCATE TABLE sampledb.datatable;

What can cause this to happen, and how could I fix it?

Another one observation is if the table have some data, may be one or two rows, then the truncate query works successfully. Else the table have a lot of data, query becomes hang.

Answer 1

Without further information it is difficult to say, and I'm not expert specifically on MySQL/MariaDB, but the general reasons for a query unexpectedly taking longer than usual to run are:

• Locks on that table: the query could be sat waiting for other transactions, that hold locks on that table, to complete. In this case the statement will pause, using no CPU or I/O resource, until the competing locks are released.
• Locks on other tables: if you have foreign keys elsewhere referring to that table, the truncate operation will need to make sure it is not dropping rows that are still referred to. This means that locks on those other tables could cause it to pause too.
• Differences in referential integrity checks: if your new DB has FKs defined referring to that table where the old one didn't, that could also explain a difference in truncate speed. If the table being truncated and/or the referring tables are very large this could be far from instant (if this is the reason for your different observed behaviours you'd expect to see the statement impose some IO and/or CPU load).