"object already exists" error

[Eric Faulhaber]

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();
}
}
}

[Eric Faulhaber]

Hi Thomas,

I dug further into this problem and found that the root cause is the
use of the global constraints map in the org.h2.schema.Schema class to
manage local temporary table constraints. It looks like a similar
issue was addressed recently for indexes, but not for constraints.

It is legal to use the same name for a local temporary table across
different sessions, but this results in Schema.getUniqueName()
internally generating the same sequence of candidate names for a
constraint on such tables. At this time, concurrent access to the
constraints and temporaryUniqueNames collections is not threadsafe.
As a result, a non-unique name can be returned by this method.

I am working on a patch which will store constraints for local
temporary tables in the Session object, similar to what is done with
indexes. This way, all access to the map is done within a single
Session only. Also, concurrent access to the shared
temporaryUniqueNames set will have to be synchronized. Please let me
know if you have any concerns with this approach.

Thanks,
Eric Faulhaber

[Thomas Mueller]

Hi,

> I dug further into this problem and found that the root cause is the
> use of the global constraints map in the org.h2.schema.Schema class to
> manage local temporary table constraints. It looks like a similar
> issue was addressed recently for indexes, but not for constraints.

You are right... Sounds like a bug.

I'm not sure what is the best way to solve this problem. One solution
is to add a field "private HashMap localTempTableConstraints" to the
session. Another approach is to add one 'schema' to each session. That
way part of the code can be re-used. But at the moment I would
probably not do that, it sounds more complex.

> concurrent access to the
> constraints and temporaryUniqueNames collections is not threadsafe.
> As a result, a non-unique name can be returned by this method.

That sounds like a bug as well.

> I am working on a patch which will store constraints for local
> temporary tables in the Session object, similar to what is done with
> indexes. This way, all access to the map is done within a single
> Session only. Also, concurrent access to the shared
> temporaryUniqueNames set will have to be synchronized. Please let me
> know if you have any concerns with this approach.

I would like to fix this problem as soon as possible. Please let me
know how far you are with the solution. If you can't finish within the
next days, I will fix it myself.

Thanks a lot for your help!

Regards,
Thomas

[Eric Faulhaber]

Hi Thomas,

On Sun, Dec 14, 2008 at 9:52 AM, Thomas Mueller <thomas.to...@gmail.com> wrote:

Hi,

> I dug further into this problem and found that the root cause is the
> use of the global constraints map in the org.h2.schema.Schema class to
> manage local temporary table constraints.  It looks like a similar
> issue was addressed recently for indexes, but not for constraints.

You are right... Sounds like a bug.

I'm not sure what is the best way to solve this problem. One solution
is to add a field "private HashMap localTempTableConstraints" to the
session. Another approach is to add one 'schema' to each session. That
way part of the code can be re-used. But at the moment I would
probably not do that, it sounds more complex.

Please find my patch attached.  I went with the approach of adding the localTempTableConstraints map to the Schema.  I also synchronized access to the temporaryUniqueNames set.  I believe the table locking you do in AlterTableAddConstraint.tryUpdate() already is sufficient to guarantee that constraint names on persistent tables are unique.

To test the result, I ran the original test case I sent earlier, with 1,000,000 total create/drop operations concurrent across 100 threads (i.e., 100 threads, 10,000 iterations each).  It completed without the "object already exists" error recurring.

I plan to to continue to test this in a highly concurrent load testing environment, as an embedded database within the context of the application I'm using it for.
 

> concurrent access to the
> constraints and temporaryUniqueNames collections is not threadsafe.
> As a result, a non-unique name can be returned by this method.

That sounds like a bug as well.

> I am working on a patch which will store constraints for local
> temporary tables in the Session object, similar to what is done with
> indexes.  This way, all access to the map is done within a single
> Session only.  Also, concurrent access to the shared
> temporaryUniqueNames set will have to be synchronized.  Please let me
> know if you have any concerns with this approach.

I would like to fix this problem as soon as possible. Please let me
know how far you are with the solution. If you can't finish within the
next days, I will fix it myself.

Hopefully, you can use what I've sent.  Please let me know if I've missed anything.
 

Thanks a lot for your help!

My pleasure.  Thank *you* for a great open source database for Java!
 

Regards,
Thomas

Regards,
Eric

[Thomas Mueller]

Hi Eric,

I have now integrated the patch and committed it in the trunk at
http://code.google.com/p/h2database/source/checkout . Sorry it was not
included in the last release. Thanks again for solving this problem!

Regards,
Thomas