Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Test case for multi-threaded access to an H2 database
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;
}
}
<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>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment