MySQL Forums
Forum List  »  Backup

Mysqldump with single transaction and skip lock tables locking the tables. help needed
Posted by: Amol Sane
Date: November 29, 2013 04:55AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Mysqldump with single transaction and skip lock tables locking the tables. help needed
15572
November 29, 2013 04:55AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.