title: PreparedStatement.execute() hangs in Spring Unit Test
body: <p>I'm having some trouble with my unit tests in a Spring MVC application.  In full disclosure, there's a good chance I'm designing my unit tests incorrectly given my lack of experience writing a test suite from the ground up.</p>
  
  <p>The way I have it designed currently is, for instance, to test a user service, the test suite uses raw SQL statements to verify the data is inserted/retrieved/updated correctly.  The problem I'm having is that after the frist prepared statement is executed, subsequent statements hang on the <code>execute()</code> method.  The result of the test ends up being <strong>"Lock wait timeout exceeded; try restarting transaction"</strong></p>
  
  <p>Based on what I've read online, this is likely a transaction management issue and somebody isn't releasing a lock but I'm not sure how best to do that or even where to do that.</p>
  
  <p>Some relevant code is below, let me know if more code is necessary.</p>
  
  <pre><code>@RunWith(SpringJUnit4ClassRunner.class)
  @ContextConfiguration(locations={"/applicationContext-base.xml", "/application-security.xml"})
  @TransactionConfiguration(transactionManager="txManager")
  @Transactional
  public class TestUserService {
  
      @Autowired
      UsersService userService;
  
      @Autowired
      DataSource dataSource;
  
      Connection connection;
  
      @Before
      public void setup() throws Exception{
          connection = dataSource.getConnection();
      }
  
      @Test
      public void testCreateUser() throws Exception{
  
          Collection&lt;GrantedAuthorityImpl&gt; auths = new ArrayList&lt;GrantedAuthorityImpl&gt;();
          auths.add(new GrantedAuthorityImpl(SecurityConstants.ROLE_USER));
  
  
          User user = new User("testUser", "testpassword", true, true, true, true, auths, "salt");
  
          User tmp = userService.createUser(user);
  
  
          PreparedStatement ps = connection.prepareStatement("select id, username, password, created, enabled, salt from users where id = ?");
          PreparedStatement ps2 = connection.prepareStatement("select user, authority from user_authorities where user = ?");
  
          ps.setLong(1, tmp.getId());
          ps2.setLong(1, tmp.getId());
  
          ResultSet rs = ps.executeQuery();
          ResultSet rs2 = ps2.executeQuery();
  
          rs.first();
          rs2.first();
  
          Collection&lt;GrantedAuthorityImpl&gt; authsFromDb = new ArrayList&lt;GrantedAuthorityImpl&gt;();
  
          rs.first();
          do{
              authsFromDb.add(new GrantedAuthorityImpl(rs2.getString("authority")));
          }while(rs2.next());
  
          User tmp2 = new User(rs.getString("username"), rs.getString("password"), rs.getBoolean("enabled"), true, true, true, authsFromDb, rs.getString("salt"));
  
          Assert.assertEquals(tmp.getUsername(), tmp2.getUsername());
          Assert.assertEquals(tmp.getId(), tmp2.getId());
          Assert.assertEquals(tmp.getPassword(), tmp2.getPassword());
          Assert.assertEquals(tmp.getSalt(), tmp2.getSalt());
          Assert.assertEquals(tmp.getAuthorities(), tmp2.getAuthorities());
          Assert.assertEquals(tmp.isEnabled(), tmp2.isEnabled());
  
      }
  
      @Test
      public void testSaveUser() throws Exception{
          long createdTime = System.currentTimeMillis();
          String insertionQry = "insert into users (username, password, created, enabled, salt) values ('chris', 'somepassword'," + createdTime + ",1,'salt')";
  
  
          PreparedStatement ps = connection.prepareStatement(insertionQry, Statement.RETURN_GENERATED_KEYS);
          ps.execute();
          ResultSet rs = ps.getGeneratedKeys();
          rs.first();
          long id = rs.getLong(1);
  
          Assert.assertEquals(true, id != 0);
  
          String loadQry = "select id, username, password, created, enabled, salt from users where id = " + id;
  
          ps = connection.prepareStatement(loadQry);
          rs = ps.executeQuery();
  
          rs.first();
  
          Assert.assertEquals(rs.getString("username"), "chris");
          Assert.assertEquals(rs.getString("password"), "somepassword");
          Assert.assertEquals(rs.getBoolean("enabled"), true);
          Assert.assertEquals(rs.getString("salt"), "salt");
  
  
          User user = new User("second_username", "newpassword", false, true, true, true, AuthorityUtils.NO_AUTHORITIES, "secondsalt");
          user.setId(rs.getLong("id"));
  
          userService.saveUser(user);
  
          ps = connection.prepareStatement(loadQry);
          rs = ps.executeQuery();
  
          rs.first();
  
          Assert.assertEquals(rs.getString("username"), "second_username");
          Assert.assertEquals(rs.getString("password"), "newpassword");
          Assert.assertEquals(rs.getBoolean("enabled"), false);
          Assert.assertEquals(rs.getString("salt"), "secondsalt");
  
  
  
      }
  </code></pre>