2015년 10월 4일 일요일

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.



댓글 없음:

댓글 쓰기