[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
Re: [eclipselink-dev] questions while running JPA JUnit tests
|
Hi Andrei,
I'm looking at the Symfoware database manual. The only option for
temporary tables is global.
Its syntax is:
CREATE GLOBAL TEMPORARY TABLE <tablename> (<elements>)
[ON COMMIT (DELETE/PRESERVE) ROWS]
ON (<databasespace>/DEFAULT DBSPACE) <number of users>
Multiple <databasespace> <number of users> pairs can be specified.
The description for the ON COMMIT part is:
The row deletion specification specifies when to delete rows from the
temporary table. If DELETE ROWS is specified or if this specification is
omitted, the temporary table becomes available in the transaction and
its rows are deleted when the transaction ends. If PRESERVE ROWS is
specified, the temporary table becomes available in the session and its
rows are deleted when the session ends.
About the number of users it says:
If creating temporary tables, specify the number of temporary tables to
be created in the database space: Specify the number of users so that
total number of users in each database space is equal to the application
program multiplicity for using temporary tables at the same time.
Google search for: symfoware temporary table
returns (among other stuff)
http://sourceforge.jp/projects/dbviewer/svn/view/DBViewer/trunk/src/zigen/plugin/db/core/TableSearcher.java?view=markup&root=dbviewer&pathrev=85
that contains:
// TABLE-TYPE
// "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
// "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
Not sure what is it exactly, but looks promising.
Unfortunately that was a false hit:
DBViewer is software that supports multiple databases. It's doing some
DB specific stuff for Symfoware in one place, therefore the google hit,
but the comment above applies to all its databases, some of which
apparently support a table type called "LOCAL TEMPORARY". Symfoware does
not.
Thanks,
Dies
Andrei Ilitchev wrote:
Hi Dies,
Local temporary table is visible only in the database session
(connection) that has created it.
That means several threads may create local temp. tables with the same
name.
The lifespan of such temporary table may be that of either transaction
or connection.
Global temporary tables created in the same namespace as regular tables
- there can't be 2 global temp. tables with the same name.
However the contents of the table are visible only to the database
session (connection) that inserted them (I see only my data).
Because the table is possibly shared it can't be dropped.
Some db support local (Sybase) other global (DB2) temporary tables.
Judging on Symfoware's db approach to table creation (no one can use the
table until creating connection is done with it)
it looked like the temp. table would be probably local (creating
connection is the only one that has access to the temp. table).
Could there be a setting allowing temporary table to only exist until
transaction is completed?
Google search for: symfoware temporary table
returns (among other stuff)
http://sourceforge.jp/projects/dbviewer/svn/view/DBViewer/trunk/src/zigen/plugin/db/core/TableSearcher.java?view=markup&root=dbviewer&pathrev=85
that contains:
// TABLE-TYPE
// "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
// "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
Not sure what is it exactly, but looks promising.
Thanks,
Andrei
----- Original Message ----- From: "Dies Koper" <diesk@xxxxxxxxxxxxxxxxxxx>
To: "Dev mailing list for Eclipse Persistence Services"
<eclipselink-dev@xxxxxxxxxxx>
Sent: Thursday, September 24, 2009 8:11 PM
Subject: Re: [eclipselink-dev] questions while running JPA JUnit tests
Hi Andrei,
Thank you for your suggestion.
Unfortunately it did not work. When it does the INSERT on the
temporary table, the driver returns an error saying the table is in
use by another user.
From what I understand from the code (only had a brief look), the
effect of the local setting over global is that the table is dropped
instead of the data in it being deleted at the end of its use.
What I think I need is a setting to have the creation of the temporary
table be executed in a separate transaction (even separate connection
as a transaction on the current connection might already have
started?) from the execution of the INSERT above and following DELETE
(or DROP, which again would need to be done in a separate transaction).
I can see
ExpressionQueryManager#buildStatementsForUpdateAllForTempTables and
#buildStatementsForDeleteAllForTempTables methods build the statements
and pass a mode to them. If we could check this mode when the
statements are executed we can obtain a separate connection and
enclose calls with mode
SQLModifyAllStatementForTempTable.CLEANUP_TEMP_TABLE in begin/commit
transaction calls.
What do you think?
Thanks,
Dies
Andrei Ilitchev wrote:
Try specifying temporary tables as local rather than global.
----- Original Message ----- From: "Dies Koper"
<diesk@xxxxxxxxxxxxxxxxxxx>
To: "Dev mailing list for Eclipse Persistence Services"
<eclipselink-dev@xxxxxxxxxxx>
Sent: Thursday, September 24, 2009 8:21 AM
Subject: Re: [eclipselink-dev] questions while running JPA JUnit tests
Hi Tom,
I finally got this test suite to stop hanging and locking.
I ended up putting the transaction begin/commit statements here:
- In SchemaManager#createObject/dropObject(in the if
(shouldWriteToDatabase()) block).
- In SequenceDefinition#createOnDatabase I put
"checkIfExist(session)" in a try-finally block, with a rollback in
the finally clause, followed by a beginTransaction. This was
necessary because in the case of sequence objects (not sequence
tables), checkIfExist did a SELECT nextval and createOnDatabase did
a CREATE SEQUENCE, so I needed to separate the DML from the DDL.
With that I still had problems with the global temporary table. A
number of tests in this suite rely on updateAll and deleteAll.
With global temporary tables disabled
(shouldAlwaysUseTempStorageForModifyAll() returns false), I get no
hangs/locks but I get an SQLException (see last week's e-mail "same
table in from clauses of query and subquery").
With global temporary tables implemented, creation of the table goes
fine, but the following INSERT fails because the table is "locked".
I'm not sure if I can (or should) put this table's create statement
in a transaction, I suppose this whole query could already be
running in a transaction.
Another issue with Symfoware's temporary tables is that the table
space name for the table must be specified at creation time. It does
not default to the table space that creation of normal tables
default to. So we'd need a way for the user to define this table space.
In what cases are updateAll/deleteAll required exactly? Are they
related to JPA 2.0 functions? Are they required for JPA 1.0
functionality too?
As I see no clean solution I wonder what the impact is of making
this a limitation for this platform for now.
With the global table disabled, I pass 176 tests (72%) of
EntityManagerJUnitTestSuite. The 45 errors are all due to the
problem above. Good news I suppose.
I'll try some more suites tomorrow.
If I no longer run into locking issues, what will the final solution
look like? Can I add a method createObjectsInTransactions() to the
DB platforms, defaulting to false of course, true for Symfoware,
that begins/commits/rolls back transactions in the locations I
described above?
Thanks,
Dies
Dies Koper wrote:
Hi Tom,
I am trying to enclose DDL calls with transactions. Inside
schemaManager.createSequences() there are mixed DDL and DML (select
and inserts on the sequence table/object), so just I'm moving the
transaction calls deeper into the call stack.
The locking error I get now is from the SELECT statement on table
CMP3_ENTITYB_SEQ in the following call in SchemaManager#createObject.
databaseObjectDefinition.createOnDatabase(getSession());
I did put a getSession().beginTransaction(); before this call, so
I'm not sure yet what the problem is. I'll investigate a bit more
and let you know.
Thanks,
Dies
The idea is that we would add calls that begin and commit
transactions around table creation calls.
Lets see if it works before we design the final solution:
- Find the
org.eclipse.persistence.tools.schemaframework.TableCreator.replaceTables(session,
schemaManager) method
- Add transactional boundaries
public void
replaceTables(org.eclipse.persistence.sessions.DatabaseSession
session, SchemaManager schemaManager) {
session.beginTransaction();
replaceTablesAndConstraints(schemaManager, session);
schemaManager.createSequences();
session.commitTransaction();
}
- Run
org.eclipse.persistence.testing.tests.jpa.advanced.EntityManagerJUnitTestSuite
- Ensure your logging is at FINER or FINEST and look for log
messages like the following around the code that does DDL generation:
[EL Finer]:
ServerSession(16925102)--Connection(14004497)--Thread(Thread[main,5,main])--begin
transaction
<a whole lot of DDL here>
[EL Finer]:
ServerSession(16925102)--Connection(14004497)--Thread(Thread[main,5,main])--commit
transaction
- make sure there are no DDL statements outside of those boundries
- see if it makes any difference to your tests
Assuming we cannot solve the issue with transactions, the
problem really comes down to the session that is passed into the
constructors for SchemaManager. That is the session used to
execute DDL. Changing the API is likely the cleanest way to do
that, but I am a bit concerned about the number of files we will
have to change to achieve that. I'll have to give some thought
to a solution that combines a reasonable number of changes to
test files with logical API.
I am still not sure what is causing the hangs and lock errors, so
if you could first suggest some temporal changes to a subset of
tests that I can apply and try to see if that solves all issues,
that would be great.
We may be able to add a temporary solution by creating some
kind of static session that we use for DDL generation and using
that session instead of the ones passed into the SchemaManager
constructor. Then we can try logging it in and out as DDL calls
are made. I am a bit concerned about that effect that will have
on how long the tests take to run, but I'll try to do some
experimentation.
How do sessions relate to the JDBC connections?
I am hoping that we can find a DB transaction related solution
since a solution like that will fit much better into the
EclipseLink schema creation architecture.
In JPA, an EntityManager holds a construct called a
ClientSession. A ClientSession communicates with a construct
called a ServerSession to make us of connections (an
EntityManagerFactory holds the ServerSession). A ServerSession
holds a number of pools of connections. (for reading, for writing,
for sequences)