Spring JDBCTemplate table locking using MySQL

Question

I am just porting one of our applications from pure JDBC to Spring JDBCTemplate. I was wondering how to create a write lock on a table. Am I just doing a "LOCK TABLE foo" query, or is there a generic way to do this in a JDBCTemplate?

Thanks!

Answer 1

JdbcTemplate uses a DataSource, so it does not guarantee that you will use the same connection for the LOCK TABLE statement and everything that you are going to do the next time you call JdbcTemplate. Therefore, it is important that you do this in a transaction. Configure PlatformTransactionManager, or DataSourceTransactionManager to JDbcTemplate DataSource, or JtaTransactionManager if JdbcTemplate uses the container-supplied JNDI data source. You can annotate your method as @Transactional or create a transaction programmatically using PlatformTransactionManager.