PreparedStatement.execute() hangs in Spring Unit Test

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.

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 execute() method. The result of the test ends up being "Lock wait timeout exceeded; try restarting transaction"

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.

Some relevant code is below, let me know if more code is necessary.

@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<GrantedAuthorityImpl> auths = new ArrayList<GrantedAuthorityImpl>();
        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<GrantedAuthorityImpl> authsFromDb = new ArrayList<GrantedAuthorityImpl>();

        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");



    }

---

In order to use raw JDBC Connections with Spring transaction management you need to obtain them as DataSourceUtils.getConnection(dataSource), see DataSourceTransactionManagement. Perhaps that's the cause.

So, the problem is that the Connection obtained via dataSource.getConnection() and used in the test code is not the same as a Spring-managed connection used in the code being tested. Thus, queries executed in these connections belong to different transactions, and executing queries in many transactions from a single thread often leads to deadlocks.

When using DataSourceUtils you get the same Spring-managed connection as the code being tested, so that all your queries are executed in a single transaction.

---

I see several problems with your testing:

1. Your tests are testing many things at once. What you need to look into are isolation (also known as mocking) frameworks so that your tests can be more granular.
2. Performing database tests are tricky (I have little experience testing this layer). You are probably better off abstracting a little so that you don't actually use real resources when performing tests. If you find that it's a must to use real resources, they should be extremely simple and have a clean database to run against to avoid data from polluting your test results.
3. Never repeat strings in your tests, just like production code (ie "Chris"). You may need to make a reference elsewhere. Depending on the testing framework, you may be allowed to have a base class with shared objects, etc. that you can customize to your heart's content.