Last active
August 22, 2018 19:03
-
-
Save digulla/edb8095b7cfa63da3a1e to your computer and use it in GitHub Desktop.
Test case for multi-threaded access to an H2 database
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<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