--single-transaction --lock-tables options of mysqldump- what happens internally?

Question

Hello,

I did find other posts similar to this, but wanted a little extra information on these options of mysqldump. I have understood that the --single-transaction and --lock-tables are mutually exclusive operations. Following are my questions regarding these options.

a) Suppose I have chosen to use --lock-tables option. In this case the mysqldump acquires a read lock on all the tables. So any other process trying to write to the tables will go into a blocked (wait) state. But if the mysqldump takes a really long time, would the processes that are waiting continue to wait indefinitely?

I tried this experiment for example- I have a Java (JDBC) program writing to a mysql database table called MY_TEST. I logged in to mysql console and issue "LOCK TABLES MY_TEST READ;" command manually. So the Java process got blocked waiting for the lock to get released. My question is would there be a connection time out or any such problem if the read lock does not get released for long time? I waited for two minutes and did not notice any error and the java process continued normally once the lock was released using "UNLOCK tables" command. Is this behavior specific to the java mysql driver or can I expect the same thing from a C program using mysql driver?

b) My second question is on the --single-transaction option. Suppose I have 10 InnoDB tables, out of which 3 tables are related to each other (using FK) and the others being independent but still using InnoDB engine. Does single transaction apply only for the 3 tables which are inter-related using FK? or can I expect the state of the 7 independent tables to be exactly how it was when the 3 inter-dependent tables were dumped.

Thanks!
-Keshav

Answer 1

a.) I believe the answer is yes, at the mysql level the connections will wait indefinitely for mysqldump to release the table locks. You can control this a bit at the application level by using a connection pool with a validation query that queries against the tables getting locked and setting the timeout for retrieval to whatever you want. This would be pretty easy to do in c3p0 for example. However, in the absence of other information, I would not recommend this approach; it seems pretty kludgey. I've not used the mysql C driver so I can't so for certain, but I would assume similar behavior to Java. All of this is why for mysqldump is not a good option for a live backup of systems with non-trivial amounts of data and activity.

b. All tables dumped will be dumped as part of a single transaction, thereby yielding a consistent snapshot for all of the tables participating in the dump. Primary-foreign key relationships will have no bearing on the transaction. Using single-transaction is viable option for hot backups.