Bdtyktl

I have a webserver with a MySQL database. It holds several databases serving my various projects. Since Upgrading Ubuntu to 16.04 it has given me a lot of trouble in general. This question specifically is about a situation that a table is locked with a table level lock with no obvious reason why the lock is not removed quickly.

I have a lot of stuck queries like so:

581723  algebrainc_ro   localhost   algebrainc  Query   10309   Waiting for table level lock    SELECTn  *nFROM inventorynWHERE inventory_id = '21601'

581724  algebrainc_ro   localhost   algebrainc  Query   10309   Waiting for table level lock    SELECTn  *nFROM inventorynWHERE inventory_id = '21592'

581725  algebrainc_ro   localhost   algebrainc  Query   10309   Waiting for table level lock    SELECTn  *nFROM inventorynWHERE inventory_id = '21602'

581726  algebrainc_ro   localhost   algebrainc  Query   10309   Waiting for table level lock    SELECTn  *nFROM inventorynWHERE inventory_id = '21596'

581729  algebrainc_ro   localhost   algebrainc  Query   10306   Waiting for table level lock    SELECT inventory_id, ebay_title FROM inventory WHERE ebay_id = '32999992936'

581730  algebrainc  localhost   algebrainc  Query   10282   Waiting for table level lock    SELECT n  complete_status status,n  ebay_transactions.inventory_id,n  ebay_transactions.ebay_id, n  quantity, purchase_price, n  ebay_title nFROM ebay_transactions, inventory nWHERE n  ebay_transactions.inventory_id = inventory.inventory_id n  AND created_date > date_add( now(), interval -7 day )nORDER BY n  created_date

After a while of poking around and looking for nothing in performance_schema such as table_handles and metadata_locks, I did not find much.

After a while I realized that it happens because of mysqldump processes that I do periodically to dump one particular table. I had a dozen stuck mysqldump processes (called by cron) like these:

ichudov   1178  0.0  0.0  29004  3248 pts/46   Ss+  20:24   0:00 mysqldump -ualgebrainc -px xxxxxxxx algebrainc XXXXXXXX_posts

ichudov   1394  0.0  0.0  29004   900 pts/26   Ss+  Jun23   0:00 mysqldump -ualgebrainc -px xxxxxxxx algebrainc XXXXXXXX_posts

ichudov   2537  0.0  0.0  29004   920 pts/22   Ss+  Jun24   0:00 mysqldump -ualgebrainc -px xxxxxxxx algebrainc XXXXXXXX_posts

(table name masked for privacy)

After killing mysqldump processes, table locks were removed and everything went back to normal.

But why do mysqldump processes lock things up and do not work?

Cause: table level lock during mysqldump which is the reason for slowness since the table is using MyISAM engine

Resolution: Convert MyISAM to Innodb, for row level locking,

ALTER TABLE 'table_name' ENGINE=INNODB;

Refer: myisam lock during mysqldump