MySQL Bugs: #8820: mysqldump --quick --lock-tables cancels out the --lock-tables

Bug #8820	mysqldump --quick --lock-tables cancels out the --lock-tables
Submitted:	26 Feb 2005 8:41	Modified:	26 Feb 2005 16:00
Reporter:	David Dick	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	4.1.10	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
when you supply the --quick and --lock-tables options to mysqldump, it does not lock any tables.  --opt is advertised as using these two combinations as well and --opt does not lock tables either.

Assuming people are using this tool to achieve a backup with all tables in a consistent state, this is quite dangerous.

How to repeat:
This can be seen by running the commands and viewing the results with ethereal.  

'mysqldump --h db.example.com --u dave --ppassword --lock-tables db_name ' can be seen to lock every table in the database.

'mysqldump --h db.example.com --u dave --ppassword --lock-tables --quick db_name' does not lock anything

'mysqldump --h db.example.com --u dave --ppassword --opt db_name' does not lock anything

Suggested fix:
when --quick and --lock-tables are supplied, mysqldump should still lock all tables

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

In all cases --lock-tables forces mysqldump to lock the tables properly.
--quick does not affect locking behaviour of --lock-tables

The only effect of --quick option is that mysqldump uses mysql_use_result() instead of mysql_store_result() 
Therefore it have no effect on the locking behaviour of the session mysqlump is using.

This can be easily observed by running parrallel session for the duration of the mysqldump.

$ mysqldump db.big_table 

From another terminal:
$ mysql> LOCK TABLES db.big_table WRITE; -- It waits for the mysqldump to finish

> This can be seen by running the commands and viewing the results with ethereal. 

How ethereal is related to the internal locking mechanizm of MySQL? 
ethereal is a ethernet packets traffic analyzer

An daddition to the test case:

terminal1:
mysqldump --quick --lock-tables db big_table

terminal2:
mysql> lock tables big_table write;

terminal3:
mysql> show processlist;
+----+------+-----------+------+---------+------+----------------+----------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State          | Info     |
+----+------+-----------+------+---------+------+----------------+----------------------------------------------------+
| 10 | root | localhost | bugs | Query   |   87 | Locked         | lock tables big_table write     |
| 19 | root | localhost | NULL | Query   |    0 | NULL           | show processlist     |
| 20 | root | localhost | bugs | Query   |   99 | Writing to net | SELECT /*!40001 SQL_NO_CACHE */ * FROM `big_table` |
+----+------+-----------+------+---------+------+----------------+------------------------------------------------

Session 20 which belongs to mysqldump reads from the table
Session 10 waits for Session 20 to release the lock on table