Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-dev] questions while running JPA JUnit tests 
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) 

_______________________________________________
eclipselink-dev mailing list
eclipselink-dev@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-dev

Back to the top