[eclipselink-dev] "table is locked by another user" problem on Symfoware

[Dies Koper <diesk@xxxxxxxxxxxxxxxxxxx>]

Hi Tom,

I have searched the Symfoware manual for more information about this
issue, but could not find any. (maybe because I don't know where to look).

So I wrote a simple JDBC test application to try out a few patterns.
I have been able to reproduce both the hang while getting a connection
and a table locked error:

1. One connection
   If I use a single connection, I can create a table and do a select on
it. Also works for a global temporary table.

2. Two subsequent connections
   If I use a single connection to create a table, close the connection,
then get a new connection, I can do a select on the table. (even if I
don't close the statement used to do the CREATE TABLE).

3. Two simultaneous connections [table locked error]
   If I use a single connection to create a table, leave the connection
open, open a second connection and use that connection to do a select on
the table, the statement.execute method threw an SQLException with a
message saying that the table is being used exclusively by another user.
However, since then I've changed my application a few times and have not
been able to reproduce this any more. (of course I still see this
message when I run the JPA JUnit tests). Not sure what's different.

4. One connection with transaction
   If I use a single connection to create a table enclosed in
con.setAutoCommit(false) and con.commit() calls, I can use the same
connection to do an insert and select on the table (without the
con.setAutoCommit(false) and con.commit() calls).

5. One connection with transaction [schema/table locked error]
   If I open a connection, invoke con.setAutoCommit(false) on it, then
do a CREATE TABLE followed by an INSERT, the stmt.execute method throws
an SQLException with a message saying that the table in the schema is
being used exclusively by another user.
   The difference between this pattern and 4. is that here I do not
invoke con.commit() before the INSERT. Also, the error message is a bit
different from the one in 3., it has a different ID number and includes
the schema name, but other than that it says the same.

6. Two connections with ongoing transaction [hang at getConnection]
   If I open a connection, call con.setAutoCommit(false) on it, and use
it to create a table, then try to open a second connection without
calling con.commit() on the first, the DriverManager.getConnection
method does not come back. I can also not connect to the database
through its console, so it must be the server (DB) that's not returning
a connection.
   If I do the above without creating the table (i.e. get the second
connection straight after invoking con.setAutoCommit(false) on the
first) it does not hang.

7. Two connections with ongoing transaction [hang at rs.next]
   If I use a single connection to create a table (enclosed in
con.setAutoCommit(false) and con.commit() calls), I can successfully
insert a row and select it. I can see the row in the result set. I leave
this connection open, open a second connection and use that connection
to do a select on the table. This time, the ResultSet#next() method
hangs. I can resolve this by calling commit again on the first
connection before I do the select on the second.

I ran most of these patterns with a default isolation level of
READ_COMMITTED.

I am sending off questions to the Symfoware development team about these
issues, in particular pattern 6.
In the meantime, if you have any suggestions for temporary work-arounds
that I could apply to EclipseLink, I would love to try.

Thanks!
Dies