Eclipse Community Forums: EclipseLink » EL issues SELECT 1 after every exception [SYBASE]

Home » Eclipse Projects » EclipseLink » EL issues SELECT 1 after every exception [SYBASE](EL issues SELECT 1 after every exception [SYBASE])

Show: Today's Messages :: Show Polls :: Message Navigator

EL issues SELECT 1 after every exception [SYBASE] [message #1384784]

Sun, 01 June 2014 14:01

Faz Mu

Messages: 2
Registered: May 2014

Junior Member

Why does EL issues SELECT 1 after every exception ? Is there a way to avoid the issuing of this SELECT 1 as it is hanging/lockig the table and the connection is not being released? Thanks!

Report message to a moderator

Re: EL issues SELECT 1 after every exception [SYBASE] [message #1384854 is a reply to message #1384784]

Mon, 02 June 2014 14:17

Chris Delahunt

Messages: 1237
Registered: July 2009

Senior Member

The "SELECT 1" is issued after exceptions to determine if the exception is a communication exception that would necessitate throwing away the connection. If the test passes, the connection can be reused and placed back in the pool or the query retried. This should not cause a problem, as the select is not issued against a table, so nothing should be locked, and the connection itself should be returned to the connection pool (or released if there was a problem with it). So if you are having issues with connections, you should post the settings you are using to configure your pool, and detail what your issues are. To prevent the select, set the pingSQL in the DatabasePlatform to null using a customizer with the setPingSQL(null) method.

Best Regards,
Chris

[Updated on: Mon, 02 June 2014 14:18]

Report message to a moderator

Re: EL issues SELECT 1 after every exception [SYBASE] [message #1385026 is a reply to message #1384854]

Tue, 03 June 2014 17:57

Faz Mu

Messages: 2
Registered: May 2014

Junior Member

Looks like the problem comes down to the commit piece, when I try to insert a record into the table that already exists, it fails with the unique constraint issue. Along with that what happens is my sql is not rollback and so the chained mode is not set back to off.
I get to see the below in the db logs,

set CHAINED on
DELETE FROM xx WHERE (xx = @p0)
commit
commit
set CHAINED off
SELECT xx FROM xx
set CHAINED on
INSERT INTO xx (xx) VALUES (@p0x)
SELECT 1
If you notice the above, its clear that the set chained mode is not set back to off again and this is holding the connection to be locked (along with the SELECT 1).

And the app logs are as below, even though am explicitly issuing a rollback, it doesn't effct cause while the commit fails it seems the transaction is inactivated.

Is Tran ACtive? true // Before issuing commit()
Exception while Committing - Is Tran ACtive? false // Tran status after commit() failed
Exception while Committing, check-- >javax.persistence.RollbackException: // error thrown while committing
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.2.v20101206-r8635):
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.sybase.jdbc3.jdbc.SybBatchUpdateException: JZ0BE:
BatchUpdateException: Error occurred while executing batch statement:
Attempt to insert duplicate key row in object 'x' with unique index 'pk_x'

Report message to a moderator

Re: EL issues SELECT 1 after every exception [SYBASE] [message #1385123 is a reply to message #1385026]

Wed, 04 June 2014 13:11

Chris Delahunt

Messages: 1237
Registered: July 2009

Senior Member

In http://stackoverflow.com/questions/23990355/jpa-eclipselink-issues-select-1 you mention this works on WebLogic. What are the differences between your settings?

As EclipseLink has nothing to do with setting the CHAINED on or off, I'm unsure what this means. You are using a resource_local em, so the transaction should be rolled back on the error. Check your settings to make sure the connections are configured properly and the driver version behaves correctly.

Report message to a moderator

Previous Topic:	positional parameter in query
Next Topic:	Lazy-loaded basic attribute never refreshed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 28 16:02:30 GMT 2017

.:: Contact :: Home ::.