Hi Gordon,
Seems that the persistence alias is very slow today. I sent out a bit
different proposal few mins ago. But I think the proposal below makes
more sense. Attached are proposed changes to ResultSetMappingQuery to
implement the first part. Please review. We can implement the hint to
execute it in outside transaction later on.
Thanks,
Mitesh
Gordon Yorke wrote:
> Marina,
> Given that Native Queries are generally going to be used for special cases having them part of the transaction is probably best although it will sacrifice efficiency for users who are doing simple selects. We should not rollback the changes though (unless you are talking about the UR1 branch?) but for now should have all Native Queries "begin an early transaction" in the UnitOfWork as the ModifyAllQuery does. In the future we can add a hint to allow users to have native queries that can read outside of the transaction.
> --Gordon
>
> -----Original Message-----
> From: Marina.Vatkina_at_Sun.COM [mailto:Marina.Vatkina_at_Sun.COM]On Behalf Of
> Marina Vatkina
> Sent: Friday, September 01, 2006 3:01 PM
> To: persistence_at_glassfish.dev.java.net
> Cc: Binod P G
> Subject: Re: [Issue 1059] New - native queries not being committed
>
>
> Gordon,
>
> As the changes to use non-tx connections broke existing functionality,
> we can't require users to add hints to get it back. It's possible
> though to add hints to request new functionality.
>
> As this bug is a show-stopper for UR1 release, can we rollback the
> original changes?
>
> thanks,
> -marina
>
> Gordon Yorke wrote:
>
>> Having a query use the transactional connection in TopLink is not as simple as it seems and caries with it some side effects on subsequent queries. I think we should have an option to force the query into the transaction that can be initiated by the user and we should attempt to detect pessimistic lock clauses.
>> --Gordon
>>
>> -----Original Message-----
>> From: Marina.Vatkina_at_Sun.COM [mailto:Marina.Vatkina_at_Sun.COM]On Behalf Of
>> Marina Vatkina
>> Sent: Friday, September 01, 2006 1:37 PM
>> To: persistence_at_glassfish.dev.java.net
>> Cc: Binod P G
>> Subject: Re: [Issue 1059] New - native queries not being committed
>>
>>
>> Hi Mitesh,
>>
>> I don't think that the answer is in the connection pool code. Non-tx connections
>> are not suitable for select ... for update, as such selects must be transactional.
>>
>> Gordon,
>>
>> Unless there is a complete parser in place that can understand what exactly a
>> native query is doing, I would think that native queries should be excluded
>> from non-tx connections optimizations.
>>
>> thanks,
>> -marina
>>
>> Mitesh Meswani wrote:
>>
>>
>>> Gordon Yorke wrote:
>>>
>>>
>>>
>>>> Hello Mitesh,
>>>> In July (around the 20th) code was changed where if the SELECT was executed before a flush() or bulk update in the transaction then it would use the non-tx connection. (Select for update was not considered).
>>>>
>>>> Is it possible that the connection pool was giving us the tx connection anyway?
>>>>
>>>>
>>> We will have to wait for Binod's reply. But, looking at the fisheye logs
>>> it does not seem that there was any change in the jdbcra module either
>>> in trunk <http://fisheye5.cenqua.com/changelog/glassfish/jdbcra> or in
>>> the branch
>>> <http://fisheye5.cenqua.com/changelog/%7Ebr=SJSAS90_FCS_BRANCH/glassfish/jdbcra>
>>> post Marrch 2006 so most probably the code change above should be the
>>> root cause
>>>
>>>
>>>
>>>> A bug should be entered for the custom SQL SELECT FOR UPDATE case.
>>>>
>>>>
>>>>
>>> Issue 1059 corresponds to this issue. Any hints on how it can be resolved?
>>>
>>> Thanks,
>>> Mitesh
>>>
>>>
>>>
>>>> --Gordon
>>>>
>>>> -----Original Message-----
>>>> From: Mitesh.Meswani_at_Sun.COM [mailto:Mitesh.Meswani_at_Sun.COM]On Behalf Of
>>>> Mitesh Meswani
>>>> Sent: Friday, September 01, 2006 3:10 AM
>>>> To: persistence
>>>> Cc: Binod P G
>>>> Subject: Re: [Issue 1059] New - native queries not being committed
>>>>
>>>>
>>>> Hi Tom, Gordon,
>>>>
>>>> I am trying to investigate this issue. I observed that current toplink
>>>> code uses non-tx connection to execute the query. To experiment I
>>>> changed toplink code to use the transactional connection
>>>> (maindataSource) instead of non-tx connection (readDatasource) to
>>>> execute the query and the hang goes away.
>>>> Looks like the cause for this issue is one of following.
>>>> (1). Toplink was changed to use non-tx connection between June 29th
>>>> (date of merge that corresponds to build 8) to Aug 30 (date of merge
>>>> that corresponds to build 9). Do you think it is the case? Or any other
>>>> changes that you might suspect?
>>>> (2). The non-tx connection pool impl of glassfish has changed and is not
>>>> properly delisting the connection.
>>>>
>>>> Hi Binod,
>>>> Stepping throu gh toplink's code, I observed that toplink does properly
>>>> close the non-tx connection after executing the query. Do you think
>>>> anything has changed in the connection pool impl in this area between
>>>> UR1 build 8 and 9 that correposnds to (2) above ? Any other hints you
>>>> can think of to debug this issue?
>>>>
>>>> Thanks,
>>>> Mitesh
>>>>
>>>> sdo_at_dev.java.net wrote:
>>>>
>>>>
>>>>
>>>>
>>>>> https://glassfish.dev.java.net/issues/show_bug.cgi?id=1059
>>>>> Issue #|1059
>>>>> Summary|native queries not being committed
>>>>> Component|glassfish
>>>>> Version|9.0peur1
>>>>> Platform|All
>>>>> OS/Version|All
>>>>> URL|
>>>>> Status|UNCONFIRMED
>>>>> Status whiteboard|
>>>>> Keywords|
>>>>> Resolution|
>>>>> Issue type|DEFECT
>>>>> Priority|P3
>>>>> Subcomponent|entity-persistence
>>>>> Assigned to|mvatkina
>>>>> Reported by|sdo
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> ------- Additional comments from sdo_at_dev.java.net Thu Aug 31 18:40:04 +0000 2006 -------
>>>>> Starting with nightly builds for 9.0_01 this week, native queries are no longer
>>>>> being committed. I don't know the exactly nightly it was introduce, but
>>>>> somewhere between promoted build 8 and the August 30 nightly build.
>>>>>
>>>>> See the attachment for the test case. Essentially, we have this code (executed
>>>>> within a stateless session bean with default transaction semantics):
>>>>>
>>>>> Query q = em.createNativeQuery("select object(c) ... for update");
>>>>> Object o = q.getSingleResult();
>>>>>
>>>>> The first time this executes, the row lock is placed on the database, and the
>>>>> session bean executes -- this should end the transaction and commit the database
>>>>> work. However, that doesn't happen, and hence a second call to the same code
>>>>> will hang because it can't access that row in the database.
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: issues-unsubscribe_at_glassfish.dev.java.net
>>>>> For additional commands, e-mail: issues-help_at_glassfish.dev.java.net
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
/*
* The contents of this file are subject to the terms
* of the Common Development and Distribution License
* (the "License"). You may not use this file except
* in compliance with the License.
*
* You can obtain a copy of the license at
* glassfish/bootstrap/legal/CDDLv1.0.txt or
*
https://glassfish.dev.java.net/public/CDDLv1.0.html.
* See the License for the specific language governing
* permissions and limitations under the License.
*
* When distributing Covered Code, include this CDDL
* HEADER in each file and include the License file at
* glassfish/bootstrap/legal/CDDLv1.0.txt. If applicable,
* add the following below this CDDL HEADER, with the
* fields enclosed by brackets "[]" replaced with your
* own identifying information: Portions Copyright [yyyy]
* [name of copyright owner]
*/
// Copyright (c) 1998, 2006, Oracle. All rights reserved.
package oracle.toplink.essentials.queryframework;
import java.util.*;
import oracle.toplink.essentials.internal.localization.ExceptionLocalization;
import oracle.toplink.essentials.exceptions.QueryException;
import oracle.toplink.essentials.exceptions.DatabaseException;
import oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl;
import oracle.toplink.essentials.sessions.DatabaseRecord;
/**
* <p><b>Purpose</b>:
* Concrete class to perform read using raw SQL and the SQLResultSetMapping.
* <p>
* <p><b>Responsibilities</b>:
* Execute a selecting raw SQL string.
* Returns a List of results. Each item in the list will be another list
* consisting of the expected populated return types in the order they were
* specified in the SQLResultSetMapping
*
* @see SQLResultSetMapping
* @author Gordon Yorke
* @since TopLink Java Essentials
*/
public class ResultSetMappingQuery extends ObjectBuildingQuery {
protected String resultSetMappingName;
protected SQLResultSetMapping resultSetMapping;
/**
* PUBLIC:
* Initialize the state of the query.
*/
public ResultSetMappingQuery() {
super();
}
/**
* PUBLIC:
* Initialize the query to use the specified call.
*/
public ResultSetMappingQuery(Call call) {
this();
setCall(call);
}
/**
* PUBLIC:
* Initialize the query to use the specified call and SQLResultSetMapping
*/
public ResultSetMappingQuery(Call call, String sqlResultSetMappingName) {
this();
setCall(call);
this.resultSetMappingName = sqlResultSetMappingName;
}
/**
* INTERNAL:
* Clone the query.
*/
public Object clone() {
ResultSetMappingQuery cloneQuery = (ResultSetMappingQuery)super.clone();
cloneQuery.resultSetMapping = this.resultSetMapping;
cloneQuery.resultSetMappingName = this.resultSetMappingName;
return cloneQuery;
}
/**
* INTERNAL:
* Convert all the class-name-based settings in this ResultSetMapping to actual class-based
* settings. This method is used when converting a project that has been built
* with class names to a project with classes.
* @param classLoader
*/
public void convertClassNamesToClasses(ClassLoader classLoader){
resultSetMapping.convertClassNamesToClasses(classLoader);
};
/**
* PUBLIC:
* Used to define a store procedure or SQL query.
*/
/* public void setCall(Call call) {
if (call instanceof SQLCall){
((SQLCall)call).setSQLString(((SQLCall)call).getCallString().replace('?','#'));
}
super.setCall(call);
}
*/
/**
* PUBLIC:
* This will be the SQLResultSetMapping that is used by this query to process
* the database results
*/
public void setSQLResultSetMapping(SQLResultSetMapping resultSetMapping){
this.resultSetMapping = resultSetMapping;
this.resultSetMappingName = resultSetMapping.getName();
}
/**
* PUBLIC:
* This will be the SQLResultSetMapping that is used by this query to process
* the database results
*/
public void setSQLResultSetMappingName(String name){
if (name == null && this.resultSetMapping == null){
throw new IllegalArgumentException(ExceptionLocalization.buildMessage("null_sqlresultsetmapping_in_query"));
}
this.resultSetMappingName = name;
}
/**
* INTERNAL:
* This method is used to build the results. Interpreting the
* SQLResultSetMapping.
*/
protected List buildObjectsFromRecords(List databaseRecords){
List results = new ArrayList(databaseRecords.size() );
SQLResultSetMapping mapping = this.getSQLResultSetMapping();
for (Iterator iterator = databaseRecords.iterator(); iterator.hasNext();){
if (mapping.getResults().size()>1){
Object[] resultElement = new Object[mapping.getResults().size()];
DatabaseRecord record = (DatabaseRecord)iterator.next();
for (int i = 0;i<mapping.getResults().size();i++){
resultElement[i] = ((SQLResult)mapping.getResults().get(i)).getValueFromRecord(record, this);
}
results.add(resultElement);
}else if (mapping.getResults().size()==1) {
DatabaseRecord record = (DatabaseRecord)iterator.next();
results.add( ((SQLResult)mapping.getResults().get(0)).getValueFromRecord(record, this));
}else {
return results;
}
}
return results;
}
/**
* INTERNAL:
* Executes the prepared query on the datastore.
*/
public Object executeDatabaseQuery() throws DatabaseException {
if (getSession().isUnitOfWork()) {
UnitOfWorkImpl unitOfWork = (UnitOfWorkImpl)getSession();
// Note if a nested unit of work this will recursively start a
// transaction early on the parent also.
// if (isLockQuery()) {
if ((!unitOfWork.getCommitManager().isActive()) && (!unitOfWork.wasTransactionBegunPrematurely())) {
unitOfWork.beginTransaction();
unitOfWork.setWasTransactionBegunPrematurely(true);
}
// }
if (unitOfWork.isNestedUnitOfWork()) {
// execute in parent UOW then register normally here.
UnitOfWorkImpl nestedUnitOfWork = (UnitOfWorkImpl)getSession();
setSession(nestedUnitOfWork.getParent());
Object result = executeDatabaseQuery();
setSession(nestedUnitOfWork);
Object clone = registerIndividualResult(result, unitOfWork, false, null);
if (shouldUseWrapperPolicy()) {
clone = getDescriptor().getObjectBuilder().wrapObject(clone, unitOfWork);
}
return clone;
}
}
session.validateQuery(this);// this will update the query with any settings
if (getQueryId() == 0) {
setQueryId(getSession().getNextQueryId());
}
Vector rows = getQueryMechanism().executeSelect();
setExecutionTime(System.currentTimeMillis());
// If using 1-m joins, must set all rows.
return buildObjectsFromRecords(rows);
}
/**
* INTERNAL:
* Prepare the receiver for execution in a session.
*/
protected void prepare() {
if ((!shouldMaintainCache()) && shouldRefreshIdentityMapResult()) {
throw QueryException.refreshNotPossibleWithoutCache(this);
}
getQueryMechanism().prepare();
getQueryMechanism().prepareExecuteSelect();
}
/**
* PUBLIC:
* This will be the SQLResultSetMapping that is used by this query to process
* the database results
*/
public SQLResultSetMapping getSQLResultSetMapping(){
if (this.resultSetMapping == null && this.resultSetMappingName != null){
this.resultSetMapping = this.getSession().getProject().getSQLResultSetMapping(this.resultSetMappingName);
}
return this.resultSetMapping;
}
/**
* PUBLIC:
* Return the result set mapping name.
*/
public String getSQLResultSetMappingName() {
return this.resultSetMappingName;
}
}