Hello,
I am using a multi-threaded, in-memory database with local temporary
tables only. Each table has a bigint primary key. I hit a problem
(eventually) creating the primary key, when I create/drop such tables
many times in multiple threads at once:
Error with table tt0
org.h2.jdbc.JdbcSQLException: General error:
java.lang.RuntimeException: object already exists [50000-104]
at org.h2.message.Message.getSQLException(Message.java:103)
at org.h2.message.Message.convert(Message.java:257)
at org.h2.command.Command.executeUpdate(Command.java:230)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:163)
at MultiThreadTempTableTest.run(MultiThreadTempTableTest.java:
46)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.lang.RuntimeException: object already exists
at org.h2.message.Message.getInternalError(Message.java:179)
at org.h2.schema.Schema.add(Schema.java:175)
at org.h2.engine.Database.addSchemaObject(Database.java:839)
at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate
(AlterTableAddConstraint.java:251)
at org.h2.command.ddl.AlterTableAddConstraint.update
(AlterTableAddConstraint.java:88)
at org.h2.command.ddl.CreateTable.update(CreateTable.java:174)
at org.h2.command.CommandContainer.update
(CommandContainer.java:71)
at org.h2.command.Command.executeUpdate(Command.java:207)
... 3 more
A test case follows. It expects two parameters: the number of
threads to launch, and the number of create/drop table iterations to
execute in each thread sequentially. I generated the above error
after several attempts using 2 threads, 10000 iterations each.
Despite many attempts, I did not see the error when using only one
thread.
The test case does not share Connection objects across threads, so is
it possible that the primary key constraints on local temporary tables
are accessible outside their local contexts? If two different threads
each create a primary key with the same name, and these are stored
within a shared namespace, that could explain the above error.
Thanks,
Eric Faulhaber
--------------------------
import java.sql.*;
public class MultiThreadTempTableTest
implements Runnable
{
private final int iterations;
private final String tableName;
private final String createString;
private final String dropString;
MultiThreadTempTableTest(int threadNum, int iterations)
{
this.iterations = iterations;
StringBuilder buf;
buf = new StringBuilder("tt");
buf.append(threadNum);
this.tableName = buf.toString();
buf = new StringBuilder();
buf.append("create local temporary table ");
buf.append(tableName);
buf.append("(id bigint not null, primary key (id))");
this.createString = buf.toString();
buf = new StringBuilder();
buf.append("drop table ");
buf.append(tableName);
this.dropString = buf.toString();
}
public void run()
{
for (int i = 0; i < iterations; i++)
{
Connection conn = null;
try
{
conn = getConnection();
Statement stmt = conn.createStatement();
stmt.execute(createString);
stmt.execute(dropString);
}
catch (SQLException exc)
{
System.err.println("Error with table " + tableName);
exc.printStackTrace();
}
finally
{
try
{
if (conn != null && !conn.isClosed())
{
conn.close();
}
}
catch (SQLException exc)
{
System.err.println("Error closing connection");
exc.printStackTrace();
}
}
}
}
private Connection getConnection()
throws SQLException
{
Connection conn = DriverManager.getConnection(
"jdbc:h2:mem:_temp;DB_CLOSE_DELAY=-1;MULTI_THREADED=1");
boolean ac = conn.getAutoCommit();
if (!ac)
{
conn.setAutoCommit(true);
}
return conn;
}
public static void main(String[] args)
{
try
{
int numThreads = Integer.parseInt(args[0]);
int iterations = Integer.parseInt(args[1]);
Class.forName("org.h2.Driver");
for (int i = 0; i < numThreads; i++)
{
Runnable r = new MultiThreadTempTableTest(i, iterations);
Thread t = new Thread(r);
t.start();
}
}
catch (Exception exc)
{
exc.printStackTrace();
}
}
}
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
