The main problem that I want to solve is to perform a task that generates several temporary tables in MySQL that must remain long enough to extract results from Java after they are created. Due to the size of the data involved, the task must be completed in batches. Each batch is a call to a stored procedure called through JDBC. The entire process can take half an hour or more for a large data set.
To provide access to temporary tables, I run the entire task, start to finish, in one Spring transaction using TransactionCallbackWithoutResult. Otherwise, I could get another connection that does not have access to temporary tables (this would happen sometimes before I wrapped everything in a transaction).
This worked fine in my development environment. However, in production, I received the following exception:
java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
This happened when another task tried to access some of the same tables during the execution of my lengthy transaction. What confuses me is that a long-running transaction only inserts or updates temporary tables. Access only to non-temporary tables is selected only. From what documentation I can find, Spring's default transaction isolation level should not force MySQL to block in this case.
So my first question is, is this the right approach? Can I guarantee that I get the same connection again using the Hibernate template without a long transaction?
If a long-term transaction approach is right, what should I check in terms of isolation levels? As I understand it, the default isolation level in Spring / MySQL transactions should not block tables to which only calls are available? What can I do to debug which tables cause a conflict and prevent a transaction from locking these tables?
source
share