Re: [eclipselink-dev] questions while running JPA JUnit tests

[Dies Koper <diesk@xxxxxxxxxxxxxxxxxxx>]

Hi Tom,

I tried to get more clarification regarding the behaviour for global 
temporary tables, and the use of DROP statements when using connection 
pooling (by JDBC driver or application server) with the Symfoware developer.

The reply was that the global temporary tables too can't be dropped 
until the session is closed.

Also, a session is closed by closing the connection so when using 
connection pooling where you have no direct control over when a 
connection is closed it could happen that a table that was 
created/accessed can't be dropped. They acknowledge this disadvantage 
and therefore do not recommend the use of DDL operations from JDBC 
applications.

Regards,
Dies


Dies Koper wrote:
> Hi Tom,
>
> I just received a reply from the Symfoware team.
> It's not a detailed reply regarding the behaviours of each category in 
> your e-mail, but it does seem to explain the last lock errors I saw.
>
> In my question to them I had described that I saw lock errors in step 4 
> of the following sequence:
>
> 1. Obtain a connection
> 2. (re)create tables (DROP TABLE, CREATE TABLE)
> 3. start transaction (con.setAutoCommit())
>      access tables (INSERT, UPDATE, DELETE)
>    commit transaction (con.commit())
> 4. recreate tables created at 2. (DROP TABLE, CREATE TABLE)
> 5. access tables as in 3.
>
> My translation of his reply is that Symfoware has a concept of a 
> "session", and resources accessed from within a session cannot be 
> dropped, by design, until the session has been closed. He says that 
> could be the reason an error occurred at step 4.
> He adds that there is no effect with the other categories of statements 
> (referring to the four categories in your e-mail below).
>
> Does this make sense to you?
>
> Regarding the hang I saw in getConnection, he asked more information, so 
> that issue might be unrelated.
>
> Thanks,
> Dies
>
>
> Tom Ware wrote:
> > Hi Dies,
> >
> >   Sorry not to have gotten back to you at the end of the week last 
> > week - last week things were very busy around here.
> >
> >   I am hesitant to go down the "static session" path until we have 
> > fully explored the transaction route.  Building a "static session" 
> > into our schema generator in a reasonable way will be fairly involved.
> >
> >   I think we need to figure out the details of what we are allowed to 
> > do and what we are not allowed to do and then figure out where the 
> > test framework causes issues with these assumptions.
> >
> >   As I understand the information you have provided in the past, there 
> > are several categories of statements that have different rules in 
> > Symfoware:
> >
> > 1. CREATE TABLE
> > 2. DROP TABLE
> > 3. DML (INSERT, UPDATE, DELETE)
> > 4. Temporary tables
> >
> >   For each of these there are several possible behaviors
> >
> > 1. No Effect on permissions for later statements
> > 2. Transactional - If wrapped in a transaction, does not affect 
> > permissions after transaction is committed.  Can exist in the same 
> > transaction as other types of statments.
> > 3. Transactional (exclusive) - same as above, but needs its own 
> > transaction
> > 4. Connection-Local - Later statements that affect the same table must 
> > be in the same connection
> > 5. Connection must be closed before later statement can affect the 
> > same table
> >
> >   A definitive answer about what constraints we are trying to meet for 
> > each of these scenarios will help us.  Is there a good way to get 
> > that.  When we have a definitive answer, we can so some analysis of 
> > the test issues you are seeing to see if there are easy ways we can 
> > make the test framework behave in the way Symfoware expects.
> >
> >   A few other comments:
> >
> > - Andrei Ilitchev send a link about temp tables in Symfoware that 
> > contained some keywords like: "LOCAL TEMPORARY".  It is hard for us to 
> > understand alot of the docs we find for symfoware in the internet 
> > since they seem to be in Japanese. Is that keyword a valid one. It 
> > seems counter-intuitive to allow something called a temporary table 
> > and then not be allowed to create it and insert into in the same 
> > transaction.
> > - Is it the first access to a temporary table that fails, or does the 
> > first one succeed and a later one fails?
> >
> > - TableSpace: Do all tables need a tablespace, or just temporary 
> > tables?  Are you also getting an issue with our normal table creation?
> >
> > - You mentioned earlier you tried to experiment with the connection 
> > pool settings.  Remind me what your results are when you set the 
> > settings in the test persistence xmls to have a MIN and MAX of 1.
> >
> > - Update and Delete all statements are defined in the JPA 1.0 
> > specification in the JPQL.  e.g. Delete from Employee e where e.salary 
> > > 100000
> > - The types of statements that require Temporary tables are fairly 
> > limited. (Andrei can probably give a better comment, but I think they 
> > are the same ones that we rewrite to use a subquery and that were 
> > causing issues in your test because the subquery refers to the same 
> > table as the outer query.)  If we log this issue as a limitation, I do 
> > not think this would get in the way of certification.
> >
> > -Tom
> >
> > Dies Koper wrote:
> > > Hi Tom,
> > >
> > >  >   I am glad you are starting to get some success.
> > >
> > > I might have reached the limit of this success already.
> > > I can do a full run again (no hangs), with a success rate of 57.90% 
> > > (highest so far!), 56 failures and 495 errors.
> > >
> > > The main errors/failures I see are due to:
> > > - table locked issue (still)
> > > - temporary table issue
> > >
> > > I'm not sure why, but I see a certain table (for example CMP3_DEPT) 
> > > being dropped/created and successfully used, then it tries to drop 
> > > and recreate it, but the drop failed because the table's locked by 
> > > another user. This exception then prevents other tables from being 
> > > created, leading to other failures (like table not found).
> > >
> > >  From the log I can see the create and drop statements were executed 
> > > in transactions. Even the DML to it seems to all use transactions.
> > > I don't know if it means anything to you, but the logged numeric id 
> > > of the connection when it failed was the same as when the table was 
> > > successfully created earlier, but the id of the connection used for 
> > > the DML was different.
> > >
> > > I'd like to try the other idea you had with a static session. Could 
> > > you give more information?
> > > I suppose I can swap it in in the locations where I now begin/commit 
> > > the transactions?
> > > Can this static session use (unpooled) connections that can be closed 
> > > at "commit" time?
> > >
> > > > Did you take a look at any of the other calls to 
> > > > shouldWriteToDatabase()?  Will they require transactions as well?
> > >
> > > Yes I did.
> > > They don't require transactions for Symfoware because they relate to 
> > > creating/dropping constraints using ALTER TABLE (a syntax Symfoware 
> > > does not support) and to altering sequences (again, not supported by 
> > > Symfoware).
> > >
> > > > > 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.
> > > >
> > > > I am surprised that Delete All and Update All queries are not 
> > > > running in transactions already.  Maybe we should try to isolate a 
> > > > sequence of 
> > >
> > > Note that a "Delete All" consists of the following statements (at 
> > > least in the tests that I saw failing):
> > >
> > > 1. creation of the global table
> > > 2. insert some rows
> > > 3. select some rows
> > > 4. drop the global table (or delete * from the table, depending on 
> > > whether local temporary tables are used).
> > >
> > > All of this might be in one transaction, but the issue with Symfoware 
> > > was that DDL and DML can't be in the same transaction, so 1. needs to 
> > > run in a transaction and commit, then 2. and 3. in a new transaction, 
> > > then 4. (even putting just 1. and 4. in transactions does not seem to 
> > > help as 2. and 3. lock the table)
> > > These statements are created and added to a Vector, and I'm not sure 
> > > where and how these statements are executed (so I could add the 
> > > demarcations between them). I am hoping Andrei will have a good 
> > > suggestion. Or a better solution.
> > >
> > > > tests that cause the failure... (i.e. Does the first Delete all or 
> > > > Update All fail?  Or, is the temporary table already created by 
> > > > another Delete all or Update all?  Do we see the initial create 
> > > > demarked by transactions?  Is there an error in the initial create 
> > > > that causes issues?)
> > >
> > > The temporary table seems to have been created successfully. Here is 
> > > the relevant part of the log:
> > >
> > >     [junit] [EL Finer]: 
> > > ServerSession(14864562)--Thread(Thread[main,5,main])--client acquired
> > >     [junit] [EL Finest]: 
> > > UnitOfWork(31975400)--Thread(Thread[main,5,main])--Execute query 
> > > DeleteAllQuery(referenceClass=Project sql="DELETE FROM TL_CMP3_PROJECT")
> > >     [junit] [EL Finer]: 
> > > ClientSession(2776693)--Connection(14563222)--Thread(Thread[main,5,main])--begin 
> > > transaction
> > >     [junit] [EL Fine]: 
> > > ClientSession(2776693)--Connection(14563222)--Thread(Thread[main,5,main])--CREATE 
> > > GLOBAL TEMPORARY TABLE TL_CMP3_PROJECT (PROJ_ID INTEGER NOT NULL, 
> > > PROJ_TYPE VARCHAR(255), DESCRIP VARCHAR(255), PROJ_NAME VARCHAR(255), 
> > > VERSION INTEGER, LEADER_ID INTEGER, PRIMARY KEY (PROJ_ID)) ON 
> > > TESTDB01 10
> > >     [junit] [EL Fine]: 
> > > ClientSession(2776693)--Connection(14563222)--Thread(Thread[main,5,main])--INSERT 
> > > INTO TL_CMP3_PROJECT (PROJ_ID) SELECT PROJ_ID FROM CMP3_PROJECT WHERE 
> > > (PROJ_NAME = testUpdateAllProjects)
> > >     [junit] [EL Fine]: 
> > > ClientSession(2776693)--Connection(14563222)--Thread(Thread[main,5,main])--DELETE 
> > > FROM TL_CMP3_PROJECT
> > >     [junit] [EL Warning]: 
> > > UnitOfWork(31975400)--Thread(Thread[main,5,main])--Local Exception 
> > > Stack:
> > >     [junit] Exception [EclipseLink-4002] (Eclipse Persistence 
> > > Services - 2.0.0.qualifier): 
> > > org.eclipse.persistence.exceptions.DatabaseException
> > >     [junit] Internal Exception: java.sql.SQLException: [SymfoWARE 
> > > ODBC Driver][SymfoWARE Server] JYP2091E Table "TL_CMP3_PROJECT" of 
> > > schema "DEVELOP" being used exclusively by another user.
> > >     [junit] Error Code: -2091
> > >     [junit] Call: INSERT INTO TL_CMP3_PROJECT (PROJ_ID) SELECT 
> > > PROJ_ID FROM CMP3_PROJECT WHERE (PROJ_NAME = 'testUpdateAllProjects')
> > >     [junit] Query: DeleteAllQuery(referenceClass=Project sql="DELETE 
> > > FROM TL_CMP3_PROJECT")
> > >     [junit]     at 
> > > org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333) 
> > >
> > >
> > > > > 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.
> > > >
> > > > There is a table passed into the writeCreateTempTableSql() method.  
> > > > Can we use the table space from that table?
> > >
> > > I would like it to use that table's table space, but I don't know how 
> > > to find out dynamically what table space that is using. (I am 
> > > currently hard-coding it for now)
> > > Symfoware V10 (coming out soon) has a new "DEFAULT TABLESPACE" option 
> > > that I can specify. I'm not sure how it works, but there is also a 
> > > new CREATE DEFAULT TABLESPACE DDL statement, that I suppose it works 
> > > with.
> > > So I could specify that and inform the user to prepare a default 
> > > tablespace, but in that case I need to explain in the Wiki which kind 
> > > of JPA functions require it.
> > >
> > > Also, except for the table space I need to specify the number of 
> > > concurrent users that will use it. Is my understanding correct that 
> > > these tables are used within a transaction, so they will never be 
> > > accessed by multiple users?
> > >
> > > > > 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?
> > > >
> > > > UpdateAll and DeleteAll are both JPA 1.0 features and covered in the 
> > > > JPA 1.0 TCK.
> > >
> > > The spec does not use this terminology. Is it equivalent to an UPDATE 
> > > or DELETE statement with no WHERE clause? Or an UPDATE/DELETE 
> > > statement for an entity which has a particular relationship with 
> > > other entities?
> > >
> > > > > As I see no clean solution I wonder what the impact is of making 
> > > > > this a limitation for this platform for now.
> > > >
> > > > Have you looked into Andrei's suggestion of Local temporary tables.
> > >
> > > Yes, unfortunately it did not help.
> > >
> > > >  From an EclipseLink point of view, that is a possibility.  It is 
> > > > just a matter of figuring out what that means to you from a TCK 
> > > > point of view?
> > >
> > > No, more of a JPA1.0 /TLE point of view.
> > > I've based this Symfoware platform class on the Symfoware platform my 
> > > team developed for TLE. I cannot remember encountering this issue at 
> > > the time. If there is a chance the same problem would occur on TLE 
> > > (using JPA 1.0 functionality, not TLE/Toplink's additional stuff), 
> > > I'd like to know the impact to Symfoware users of that implementation.
> > >
> > > But also from a JPA 1.0/2.0 (=TCK?) point of view: to what extend is 
> > > the platform still usable without this function? If it only affects a 
> > > certain type of update/delete queries, and the range of functions 
> > > that is affected can be determined and clearly explained (from a 
> > > user's point of view), then I'd like to consider giving up on this 
> > > and focus on other functions.
> > >
> > > Unless it is a condition to ever get the Symfoware platform graduated 
> > > from incubation (is it?), I'm not thinking of TCK certification at 
> > > this time.
> > >
> > > > > 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?
> > > >
> > > > That is likely the best way to address this.  We could tweak the 
> > > > method name a bit, and of course, check the other calls for 
> > > > shouldWriteToDatabase() to see if they needed to be transactional as 
> > > > well.
> > >
> > > Ah, you don't like the method name. ;)
> > >
> > > Well lucky for us the final solution will be different.
> > > I won't propose createObjectsInStaticSession(). ;-p
> > >
> > > 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 replac