MySQL Lock error when executing mysqldump program.

What Problems? 

When you execute mysqldump program, You may have a lock problem in MySQL Instance.
Then, Service session may not update data in MySQL.

What for?

You see example image.

Session id 253372(in Blue box.) is executing select query for 4319 sec. Session ID 257695 is blue box session's next session. ( in Purple box) Session's executing time is 1109.
The Purple box's Session's Query is very important. It is not simple query. It is FLUSH TABLES.
"FLUSH TABLES" needs exclusive lock about all tables singly.
Because of not finishing  select query, Purple box's session is waiting for finishing Session ID 253372(in Blue Box)
So, Session ID 257695 (in Purple Box)'s status is "Waiting for table flush" . State of the session with the query running afterwards is "Wating for table flush".

So, When you execute "FLUSH TABLES" ?
1. When you execute "FLUSH TABLES " explicitly
2. When you execute "mysqldump " with snapshot options.

What can I do for fixing Problem?

If you see this situation, You should kill session Long select query or "FLUSH TABLES"
The important thing is not to execute long select query when you execute myqldump in snapshot options.

MySQL - FLUSH TABLES WITH READ LOCK

By issuing this command, it is possible to obtain a global lock.

mysql> FLUSH TABLES WITH READ LOCK ;

This command can be maintained consistent data at the time the instruction is executed.


Follow the example.

First, Execute query (flush with read lock).


session1> flush tables with read lock ; Query OK, 0 rows affected (0.00 sec)

And then execute insert query  during flush command executing.


session2>insert into inno_xxx values (1,' xxx') ; -- Waiting... session2 is blocked.

In this situation, execute "show processlist;" on session3.

session3> show full processlist ;
+------+------+-----------+---------+---------+------+------------------------------+-----------------------------------------+
| Id   | User | Host      | db      | Command | Time | State                        | Info                                    |
+------+------+-----------+---------+---------+------+------------------------------+-----------------------------------------+
| 7220 | root | localhost | baktest | Query   |    2 | Waiting for global read lock | insert into inno_xxx values (1,'xxx') |
| 7224 | root | localhost | NULL    | Query   |    0 | init                         | show full processlist                   |
| 7237 | root | localhost | NULL    | Sleep   |    2 |                              | NULL                                    |
+------+------+-----------+---------+---------+------+------------------------------+-----------------------------------------+
3 rows in set (0.00 sec)

session2 ( thread id 7220 ) state is waiting for global read lock.


The lock is release when executing unlock command on session1.

session1> UNLOCK TABLE ;