--single-transaction -lock-tables mysqldump options - what's going on internally?
I found other posts similar to this, but wanted a little more information about these mysqldump options. I realized that -single-transaction and -lock-tables are mutually exclusive operations. Below are my questions regarding these parameters.
a) Suppose I decide to use the -lock-tables option. In this case, mysqldump acquires read locks on all tables. Thus, any other process attempting to write to the tables will enter a blocking (waiting) state. But if mysqldump is taking a very long time, are processes waiting indefinitely?
I tried this experiment, for example I have a Java program (JDBC) that writes to a mysql database table called MY_TEST. I went into mysql console and issued "LOCK TABLES MY_TEST READ;" commands manually. So the Java process is blocked waiting for the lock to be released. My question is, will there be a connection time or some such problem if the read lock is not released for a long time? I waited two minutes and did not notice any errors and the java process continued fine as soon as the lock was released using the "UNLOCK tables" command. Is this behavior specific to the mysql java driver, or can I expect the same from a C program using the mysql driver?
b) My second question is about the -single-transaction option. Suppose I have 10 InnoDB tables, of which 3 tables are related to each other (using FK) and the rest are independent but still use the InnoDB engine. Is there a single transaction for only three tables that are linked with FK? or I can expect the state of the 7 independent tables to be exactly what it was when the 3 interdependent tables were dropped.
source to share
a.) I believe the answer is yes, at the mysql level, connections will wait indefinitely for mysqldump to release table locks. You can control this a bit at the application level by using a connection pool with a validation query that asks for table locks and sets a fetch timeout on whatever you want. For example, this is fairly easy to do in c3p0. However, in the absence of other information, I would not recommend this approach; seems pretty kludgey. I have not used the mysql C driver, so I cannot say for sure, but I would assume it is similar to Java. This is all due to the fact that mysqldump is not a good option for real-time backups of systems with non-trivial amounts of data and activity.
b. All dumped tables will be dumped as part of a single transaction, resulting in a consistent snapshot of all tables participating in the dump. The primary foreign key relationship will not be transactional. Using a single transaction is a viable option for hot backups.
source to share