In this blog, I will provide answers to the Q & A for the Troubleshooting locking issues webinar.
First, I want to thank you for attending the May, 12 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:
Q: Do you have the links to those other info sources?
A: Yes, they are listed in the “More Information” slide. In the PDF, all the links are active. If you speak Russian, you can also check this presentation by Dmitry Lenev. He also did a similar presentation in English for MySQL Connect, but now all the content is gone from the official website, so only chance to find his slides in English is to search web archives.
Q: Are you going to discuss metadata locks?
A: Yes. I discuss them in slides 11-16.
Q: Why do row locking when table level lock is already set by InnoDB. My question was table level lock is already set. You update 100 rows in that table, but InnoDB locks these 100 rows. Why? The table is already locked . . .
A: Table lock, which you saw on slide #20, is set by InnoDB only for a short time and almost immediately released. But the transaction not closed yet, and InnoDB still needs to protect updated rows from modifications by other transactions. Why can’t it be done with table-level lock only? Imagine you have a table with 1,000,000 rows. All have an ID from 1 to 1,000,000 (and other fields). Now imagine you need to update the row with ID=1. In the case of table lock, the whole table is locked while you are performing this one update. If another connection wants to update a row with ID=202, it has to wait. In the case of row-level locks, the two queries do not interfere each other and can apply in parallel.
Q: How do you avoid locks on alter, without resetting that transaction?
A: If you are using version 5.6 and up, many ALTER commands are non-locking. See the overview of online DDL in the user manual. However, if you want to use an ALTER variation that cannot be done online, you can use the utility pt-online-schema-change from Percona Toolkit. Note that ALTER will take longer than the regular “blocking” variant, but it will not block your other connections.
Q: This is not a question, but there is a typo on the slide – it should be Intention Locks, not Intension Locks
A: Thank you! I fixed this and the wrong table name in slide #28. Please download updated version of slides.
Q: Why does the ALTER table operation have to wait forever? It should start once the transaction finished, but I know that the lock will remain. Why doesn’t it unlock when the transaction is finished?
A: Of course it doesn’t wait forever! It was just an acronym for “waits very long time,” which can happen if you have a very busy application, with many threads updating the same table. Or if you don’t close transactions.
Q: Is the metadata_locks table enabled by default?