Re: Odd behaviour with LOCK TABLES

[Ingo Strüwing]

Hi,

this is how MySQL locking works. It is independent from the storage engine.

MySQL locking is deadlock free because we lock all tables that we plan to work with in a single lock statement. Only the locked tables can be used until the lock is released (except of temporary tables).

Usually MySQL takes care for this at the start of every command. It knows, which tables will be used throughout the command and locks all in a single go. At the end of the command the lock is released.

If you use an explicit LOCK TABLE statement, the lock persists until UNLOCK TABLES. No locking/unlocking will/can be done with the commands in between. Consequently the commands can use the locked tables only (plus temporary tables).

You will need to do the backup in a second connection, while the first connection holds the read locks on the tables. The second connection can aquire different locks for its commands.

Regards
Ingo

Ingo Strüwing, Senior Software Developer - Storage Engines
MySQL AB, www.mysql.com