Mysqldump with single transaction and skip lock tables locking the tables. help needed

[Amol Sane]

Hi,

I am facing an issue with mysqldump where while dumping a table it is blocking the application.
To be precise below is the dump options I am using while taking backup

mysqldump -ubackup_user --password=xxx --single-transaction --quick --opt --skip-lock-tables --routines --triggers --all-databases --master-data=2

Now the backup runs smoothly till the dump of large table named 'watchme' starts.
During `watchme` table dump the queries from application(on only one table variant) are waiting for metadata lock. Below is the processlist snapshot.
When I kill the backup query the queue gets clear.

mysql> show processlist;
+---------+-----------+---------------------+--------------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-----------+---------------------+--------------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| 2828747 | backup_user | localhost | Prod_db | Query | 7407 | Sending data | SELECT /*!40001 SQL_NO_CACHE */ * FROM `watchme` |
| 2832644 | Prod_user | 10.176.130.22:51417 | Prod_db | Query | 7005 | Waiting for table metadata lock | LOCK TABLES variant WRITE |
| 2832827 | Prod_user | 10.176.130.21:45597 | Prod_db | Query | 6791 | Waiting for table metadata lock | UPDATE variant SET value = 'a:5:{s:32:\"5a63513f064d4434874c0d4a67bdd31d\";s:35:\"users_by_companyi |
| 2832837 | Prod_user | 10.176.130.22:51573 | Prod_db | Query | 6781 | Waiting for table metadata lock | UPDATE variant SET value = 'a:5:{s:32:\"5a63513f064d4434874c0d4a67bdd31d\";s:35:\"users_by_companyi |
| 2832939 | Prod_user | 10.176.130.22:51642 | Prod_db | Query | 6661 | Waiting for table metadata lock | UPDATE variant SET value = 'a:5:{s:32:\"5a63513f064d4434874c0d4a67bdd31d\";s:35:\"users_by_companyi |
| 2832991 | Prod_user | 10.176.130.21:45700 | Prod_db | Query | 6599 | Waiting for table metadata lock | UPDATE variant SET value = 'a:5:{s:32:\"5a63513f064d4434874c0d4a67bdd31d\";s:35:\"users_by_companyi |
| 2833097 | Prod_user | 10.176.130.21:45803 | Prod_db | Query | 6476 | Waiting for table metadata lock | UPDATE variant SET value = 'a:5:{s:32:\"5a63513f064d4434874c0d4a67bdd31d\";s:35:\"users_by_companyi |
| 2833106 | Prod_user | 10.176.130.22:51786 | Prod_db | Query | 6466 | Waiting for table metadata lock | UPDATE variant SET value = 'a:5:{s:32:\"5a63513f064d4434874c0d4a67bdd31d\";s:35:\"users_by_companyi |
| 2833158 | Prod_user | 10.176.130.22:51820 | Prod_db | Query | 6405 | Waiting for table metadata lock | LOCK TABLES variant WRITE |

Below is the lock status on the table during backup.

mysql> show open tables where In_use=1;
+--------------------+----------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+----------+--------+-------------+
| Prod_db | watchme | 1 | 0 |
+--------------------+----------+--------+-------------+
1 row in set (0.00 sec)

Note : The mysql version is 5.5.27.
Both variant and watchme are innodb tables

Now my question is mysqldump using a --single-transaction and --skip-lock-table then why it is locking the table?.Is it true that --skip-lock-table has no effect on metadata locking done by mysqldump?
So can anyone help me finding what is the exact problem and how to tackle this situation.



Edited 2 time(s). Last edit at 11/29/2013 05:20AM by Amol Sane.