Test case for multi-threaded access to an H2 database

H2TransactionTest.java

package de.pdark.h2.txtest;

import static org.junit.Assert.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.h2.jdbcx.JdbcDataSource;
import org.junit.Test;

public class H2TransactionTest {

    @Test
    public void testSetupDatabase() throws Exception {
        setupDataSource( "testSetupDatabase" );
        createTable();
        
        withConnection( new DatabaseCallback<Void>() {
            public Void call( Connection conn ) throws Exception {
                insert( conn, 1, "test" );
                assertEquals( "       1 test", dump( conn ) );
                return null;
            }
        } );
    }
    
    @Test
    public void testUpdateDatabase() throws Exception {
        setupDataSource( "testUpdateDatabase" );
        createTable();

        withConnection( new DatabaseCallback<Void>() {
            public Void call( Connection conn ) throws Exception {
                insert( conn, 1, "test" );
                assertEquals( "       1 test", dump( conn ) );
                
                update( conn, 1, "updated" );
                assertEquals( "       1 updated", dump( conn ) );
                return null;
            }
        } );
    }

    @Test
    public void testSerialThreads() throws Exception {
        setupDataSource( "testSerialThreads" );
        createTable();

        withConnection( new DatabaseCallback<Void>() {
            public Void call( Connection conn ) throws Exception {
                insert( conn, 1, "test" );
                assertEquals( "       1 test", dump( conn ) );
                
                return null;
            }
        } );
        
        DatabaseThread thread = new DatabaseThread( 
            new UpdateOp( 1, "updated" ),
            new ValidateOp( 1, "updated" )
        );
        thread.start();
        thread.join( 10000 );
        thread.throwExceptions();
    }
    
    @Test
    public void testNestedTransactions() throws Exception {
        setupDataSource( "testNestedTransactions" );
        createTable();
        
        insertDemoRow();

        // 1.3.176: Timeout trying to lock table
        // 1.4.182: Timeout trying to lock table
        runNestedTransations();
    }
    
    @Test
    public void testNestedTransactionsMVCC() throws Exception {
        setupDataSource( "testNestedTransactionsMVCC;MVCC=TRUE" );
        createTable();
        
        insertDemoRow();
        
        // 1.3.176: Timeout trying to lock table
        // 1.4.182: Timeout trying to lock table
        runNestedTransations();
    }
    
    @Test
    public void testNestedTransactionsMultiThreaded() throws Exception {
        setupDataSource( "testNestedTransactionsMULTI_THREADED;MULTI_THREADED=TRUE" );
        createTable();
        
        insertDemoRow();
        
        // 1.3.176: Timeout trying to lock table
        // 1.4.182: Timeout trying to lock table
        runNestedTransations();
    }
    
    @Test
    public void testNestedTransactionsLockMode3() throws Exception {
        setupDataSource( "testNestedTransactionsLOCK_MODE3;LOCK_MODE=3" );
        createTable();
        
        insertDemoRow();
        
        // 1.3.176: Timeout trying to lock table
        // 1.4.182: Timeout trying to lock table
        runNestedTransations();
    }

    private void runNestedTransations() throws Exception {
        withConnection( new DatabaseCallback<Void>() {
            public Void call( Connection conn ) throws Exception {
                update( conn, 1, "outer" );
                assertEquals(
                    "       1 outer\n" + 
                    "       2 test2", dump( conn ) );

                DatabaseThread thread = new DatabaseThread( 
                    new UpdateOp( 2, "inner" ),
                    new ValidateOp( 2, "inner" )
                );
                thread.start();
                thread.join( 10000 );
                thread.throwExceptions();

                assertEquals(
                    "       1 outer\n" + 
                    "       2 inner", dump( conn ) );
                
                return null;
            }
        } );
    }

    private void insertDemoRow() throws Exception {
        withConnection( new DatabaseCallback<Void>() {
            public Void call( Connection conn ) throws Exception {
                insert( conn, 1, "test" );
                insert( conn, 2, "test2" );
                assertEquals( "       1 test\n" + "       2 test2", dump( conn ) );
                
                return null;
            }
        } );
    }
    
    private class UpdateOp implements DatabaseCallback<Void> {
        
        private long pk;
        private String name;
        
        public UpdateOp( long pk, String name ) {
            this.pk = pk;
            this.name = name;
        }
        
        public Void call( Connection conn ) throws Exception {
            update( conn, pk, name );
            return null;
        }
    }
    
    private class ValidateOp implements DatabaseCallback<Void> {
        
        private long pk;
        private String name;
        
        public ValidateOp( long pk, String name ) {
            this.pk = pk;
            this.name = name;
        }
        
        public Void call( Connection conn ) throws Exception {
            String actual = get( conn, pk );
            assertEquals( name, actual );
            return null;
        }
    }
    
    private class DatabaseThread extends Thread {
        
        private DatabaseCallback<?>[] callbacks;
        private Exception exception;

        public DatabaseThread( DatabaseCallback<?> ... callbacks ) {
            this.callbacks = callbacks;
        }
        
        public void throwExceptions() throws Exception {
            if( null != exception ) {
                throw exception;
            }
        }

        @Override
        public void run() {
            try {
                withConnection( new DatabaseCallback<Void>() {
                    public Void call( Connection conn ) throws Exception {
                        for( DatabaseCallback<?> callback : callbacks ) {
                            callback.call( conn );
                        }
                        
                        return null;
                    }
                } );
            } catch( Exception e ) {
                this.exception = e;
            }
        }
    }
    
    protected String dump( Connection conn ) throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement( "select PK, NAME from DEMO order by PK" );
            rs = stmt.executeQuery();
            
            StringBuilder buffer = new StringBuilder();
            String delim = "";
            while( rs.next() ) {
                buffer.append( delim ).append( String.format( "%8d %s", rs.getLong( 1 ), rs.getString( 2 ) ) );
                delim = "\n";
            }
            
            return buffer.toString();
        } finally {
            rs = close( rs );
            stmt = close( stmt );
        }
    }
    
    protected String get( Connection conn, long pk ) throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement( "select NAME from DEMO where PK = " + pk );
            rs = stmt.executeQuery();

            if( ! rs.next() ) {
                throw new Exception( "Expected 1 row but was 0" );
            }
            
            String result = rs.getString( 1 );
            if( rs.next() ) {
                throw new Exception( "Expected 1 row but was more" );
            }
            
            return result;
        } finally {
            rs = close( rs );
            stmt = close( stmt );
        }
    }
    
    protected void insert( Connection conn, long pk, String name ) throws Exception {
        executeSql( conn, "insert into DEMO(PK, NAME) values(" + pk + ", '" + name + "')" );
    }
    
    protected void update( Connection conn, long pk, String name ) throws Exception {
        executeSql( conn, "update DEMO set NAME = '" + name + "' where PK = " + pk );
    }

    private void createTable() throws Exception {
        executeSql( "create table if not exists DEMO ( PK bigint, NAME varchar(64))" );        
        executeSql( "delete from DEMO" );        
    }

    private JdbcDataSource dataSource;
    
    private void setupDataSource( String name ) throws Exception {
        dataSource = new JdbcDataSource();
        dataSource.setURL( "jdbc:h2:./tmp/" + name );
        dataSource.setUser( "sa" );
        dataSource.setPassword( "" );
    }

    private void executeSql( final String query ) throws Exception {
        withConnection( new DatabaseCallback<Void>() {

            public Void call( Connection conn ) throws Exception {
                executeSql( conn, query );
                return null;
            }
        } );
    }
    
    protected void executeSql( Connection conn, final String query ) throws Exception {
        PreparedStatement stmt = null;
        try {
            System.out.println( query );
            stmt = conn.prepareStatement( query );
            stmt.execute();
        } finally {
            stmt = close( stmt );
        }
    }

    private <T> T withConnection( DatabaseCallback<T> callback ) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit( false );
//            conn.setTransactionIsolation( Connection.TRANSACTION_SERIALIZABLE );
            
            T result = callback.call( conn );
            
            conn.commit();
            
            return result;
        } catch( Exception e ) {
            if( null != conn ) {
                conn.rollback();
            }
            throw e;
        } finally {
            stmt = close( stmt );
            conn = close( conn );
        }
    }
    
    private interface DatabaseCallback<T> {
        T call( Connection conn ) throws Exception;
    }
    
    private ResultSet close( ResultSet rs ) {
        if( null != rs ) {
            try {
                rs.close();
            } catch( Exception e ) {
                e.printStackTrace();
            }
        }
        return null;
    }
    
    private Connection close( Connection conn ) {
        if( null != conn ) {
            try {
                conn.close();
            } catch( Exception e ) {
                e.printStackTrace();
            }
        }
        return null;
    }

    private PreparedStatement close( PreparedStatement stmt ) {
        if( null != stmt ) {
            try {
                stmt.close();
            } catch( Exception e ) {
                e.printStackTrace();
            }
        }
        return null;
    }
}

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>de.pdark</groupId>
    <artifactId>h2-tx-test</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    
    <dependencies>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.184</version>
            <!-- 
            <version>1.3.173</version>
            <version>1.3.176</version>
            <version>1.4.182</version>
             -->
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>