On Fri, 2006-09-01 at 14:16, 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.
It strikes me that this is antithetical to the entire purpose of having
a container with CMT. As a developer, I shouldn't need to initiate
anything wrt transaction boundaries.
Aren't there also other things that could cause an update within the
transaction (like calling a statement with a trigger, or a stored
procedure)? It seems like it's impossible to know for an arbitrary set
of calls within a transaction whether the database has modified the data
the select should see -- so executing the statement outside of the
expected transaction seems dangerous to me in any case.
-Scott
> --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
> >>>
> >>>
> >>>
> >>>