The MySQL documentation recommends that when dumping an InnoDB with mysqldump to use –single-transaction to get a transactionally consistent snapshot of the database with minimal blocking of other transactions. Unfortunately, this only works if you can somehow prevent any other connection from executing a schema change during the dump:

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

While a –single-transaction dump is in process, to ensure a valid
dump file (correct table contents and binary log coordinates), no
other connection should use the following statements: ALTER TABLE,
CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent
read is not isolated from those statements, so use of them on a table
to be dumped can cause the SELECT that is performed by mysqldump to
retrieve the table contents to obtain incorrect contents or fail.

What is a good way of ensuring that no schema changes happen during a dump? My use case is that I periodically (cronjob) back up my database every hour using the mysqldump utility.

I thought that maybe using mysqldump's default of –lock-tables instead would give me transactional consistency, but:

The –single-transaction option and the –lock-tables option are
mutually exclusive because LOCK TABLES causes any pending transactions
to be committed implicitly.

Is that saying that if someone runs a mysqldump –lock-tables against an InnoDB that it can actually cause an ongoing transaction in another session to no longer be transactional? I hope not — that seems insane. Elsewhere:

https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html#lock-tables-and-transactions

LOCK TABLES and UNLOCK TABLES interact with the use of transactions as
follows:

LOCK TABLES is not transaction-safe and implicitly commits any active
transaction before attempting to lock the tables.

This makes sense within the context of a single DB session. If you in the middle of a transaction on your session and do this, then it implicitly commits your open transaction first. It isn't clear what it does, if anything, to transactions on other sessions. I'm hoping that it wouldn't do anything, or rather that it would be consistently ordered with respect to any ongoing transactions on other sessions.

Am I just reading too much into the mysqldump docs and using –lock-tables achieves transactional consistency?

Are the mysqldump docs simply explaining that doing –single-transaction together with –lock-tables is nonsensical because –lock-tables would immediately and implicitly close the single transaction? (Couldn't the reverse order work? But maybe it would be pointless?)

Is mysqldump –lock-tables otherwise transactionally consistent?

Thanks!