[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
[eclipselink-dev] "table is locked by another user" problem on Symfoware
|
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