Spring JDBCTemplate Table Locking with MySQL

Question

Hi! I just migrating one of our applications from pure JDBC to Spring's JDBCTemplate. I was wondering how to create a write lock for a table. Do i just execute a "LOCK TABLE foo" Query or is there a generalisized way for doing this in JDBCTemplate?

Thanks!

Answer 1

JdbcTemplate uses a DataSource, so it's not guaranteed that you will use the same connection for the LOCK TABLE statement and whatever you're going to do in the next call to JdbcTemplate. So it's important that you do this in a transaction. Set up a PlatformTransactionManager, either a DataSourceTransactionManager on the JdbcTemplate's DataSource, or a JtaTransactionManager if the JdbcTemplate is using a container-provided JNDI DataSource. You can annotate your method as @Transactional or create a transaction programmatically using the PlatformTransactionManager.