1

Can I check whether a global read lock is in place?

My application uses MySQL (actually MariaDB) on InnoDB. I'm going to run mysqldump --all-databases, which will impose a global read lock for an hour. I'd like my application to check for the existence of this lock, but it doesn't appear to show up in anything I've tried (unless a request which is waiting on the lock already exists):

  • SHOW ENGINE INNODB STATUS
  • SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS
  • SHOW FULL PROCESSLIST
  • As your using innodb, I'd just change your mysqldump to add --single-transaction --master-data=2 and then there will only be the briefest of read locks on normally system myisam tables. Your application shouldn't even notice the difference. If you want to continue your current path, check the error messages as the condition. – danblack Jul 24 at 0:11
  • Thanks, @danblack but I'm making the dump in order to set up a slave, so that's not going to get me what I need. (I need a binlog position that's consistent with the dump data.) – JellicleCat Jul 24 at 16:38
  • the binlog position is recorded with --master-data. Its perfectly suitable for creating slaves. – danblack Jul 24 at 22:30
  • @danblack thanks for getting back to me. I understand that it does record the binlog position, but I was under the belief that without a global read lock, the dump might include writes which occur while the dump is being made, which could make the dump and binlog position inconsistent. Have I misunderstood how the dump is made? Is it instead an actual snapshot of a moment (i.e. perfectly consistent)? – JellicleCat Jul 25 at 1:24
  • 1
    its a consistent snapshot with the notable exceptions of table modifications (because the aren't transnational) and myisam/create drop/modify users and every other non-transnational statement. Transactions that start after the mysqldump --start-transaction are not included in the dump, but will be applied when you start replication from the master's binary log as expected. – danblack Jul 25 at 1:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.