2

All,

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/locking the table and the connection is not being released?

Please let me know why does this happen and is there a way to get past this?
Thanks!

[UPDATE]
Sure, its RESOURCE_LOCAL and I use JPAEclipseCustmizer to set the connection,

<persistence version="2.0" ...>
<persistence-unit name="test" transaction-type="RESOURCE_LOCAL">
....
<properties> 
<property name="eclipselink.session.customizer" value="xxx.JpaEclipseLinkSessionCustomizer" />
</properties>

And the error log, if you notice, there is no trace of end unit of work commit which I believe may be tagged to the issue. And after this excpetion, the SELECT 1 is blocking other processes as I see from Database Proceeses list

[EL Finer]: 2014-06-01 01:15:24.73--ClientSession(454114973)--Connection(1046369599)--Thread(Thread[Timer-0,10,main])--Begin batch statements [EL Fine]: 2014-06-01 01:15:24.731--ClientSession(454114973)--Connection(1046369599)--Thread(Thread[Timer-0,10,main])--INSERT INTO xxx (name) VALUES (?) [EL Fine]: 2014-06-01 01:15:24.732--ClientSession(454114973)--Connection(1046369599)--Thread(Thread[Timer-0,10,main])-- bind => [xxx] [EL Finer]: 2014-06-01 01:15:24.733--ClientSession(454114973)--Connection(1046369599)--Thread(Thread[Timer-0,10,main])--End Batch Statements [EL Fine]: 2014-06-01 01:15:24.74--ClientSession(454114973)--Thread(Thread[Timer-0,10,main])--SELECT 1 [EL Warning]: 2014-06-01 01:15:24.744--ClientSession(454114973)--Thread(Thread[Timer-0,10,main])--Local Exception Stack: 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 'xxx' with unique index 'pk_xxx'

Error Code: 0
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeJDK12BatchStatement(DatabaseAccessor.java:830)

[UPDATE 2]
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'
Faz
  • 534
  • 1
  • 9
  • 27
  • Can you explain what you mean by blocking? The select 1 looks like just a health check on the connection, verifying that it is still open and can continue to be used. What is the exact issue you are seeing, as a select 1 should not be 'blocking' other processes. Are you out of connections, is it the database that isn't responding, is it your java app? – Chris Jun 02 '14 at 13:55
  • Its the database thats not responsing, when I see the processes list, it just highlights that "Select 1" is blocking other processes from running. Looks like its holding some kinda lock (with connection not being released) and eventually prevents all other processes depending on the foresaid select query – Faz Jun 02 '14 at 14:45
  • 1
    I tried to have a workarouns, where in before commit the record am checking if the record already exists. If it already esists, it will skip the commit (which inturn skips the exception and SELECT 1) and in this case its working fine where in the SELECT 1 never appears and so no problem at all. This is strange as to why the SELECT 1 is behaving like this as you ppl say it should never lock the table.. Is there a way to change / avoid this select from happening? – Faz Jun 02 '14 at 16:32
  • 1
    This is discussed here: http://www.eclipse.org/forums/index.php?t=thread&frm_id=111 but you really need to track down why "select 1" is causing you locking issues on the database. Since the original exception is logged after the select 1 statement, it appears control is returned to the java app and so the statement should have been closed. If it is still open somehow, you will have to check your database driver to see if there are issues with it. Could just be that you have not configured the database pool correctly and are running out of connections? – Chris Jun 02 '14 at 18:19
  • Sure, I will give it a try again. But this works fine in weblogic, fails only with the tomcat. – Faz Jun 02 '14 at 18:23
  • Then it shouldn't be an issue with "Select 1".. since it is used regardless of the server. Compare your datasource configuration and find out what is actually the problem - how are you checking the database is not responding, how many connections is it configured to handle and how many are open? – Chris Jun 02 '14 at 18:26
  • And am pretty much sure that the connections are not falling short, only its operating only with those connections and not trying to establish any new connections. Max of 10 connections are allowed and only 4 are open (each connection assocaited with a server since its in a cluster env). And I get to see the database processes list in the tool as it depicts that the current processes which is querying 'select 1" is blocked. – Faz Jun 02 '14 at 18:30
  • @Chris - have tested and provided the update under **[UPDATE 2]** section. Could you pls throw some light on this? – Faz Jun 03 '14 at 16:37
  • set CHAINED off/on isn't something JPA providers do. You mentioned somewhere that this works on weblogic, so maybe look at what is happening differently there vs on Tomcat, and the settings used on both. – Chris Jun 04 '14 at 13:01
  • @Faz did you discovered "why the SELECT 1 is behaving like this as you ppl say it should never lock the table"? – Mariano Paniga May 20 '22 at 10:05

1 Answers1

1

That is not the cause of your problems. It comes from your connection pool manager, that keeps the connection alive. Such a query cannot lock any table, as no table is involved.

Probably you should close the EntityManager (even if an exception occurs).

V G
  • 18,822
  • 6
  • 51
  • 89
  • Thanks Andrei, Small correction - I meant to say the connection is hanging and not getting released. My bad for using the statement table lock here... Yes whenever there is a excpetion, I do rollback and close the EntityManager. This is taken care of, but eventhough I close it, I see the SELECT is not getting released and blocking the other processes from running. Any ideas? – Faz Jun 02 '14 at 09:12
  • This mainly happens when I try to persist something (duplicates - unique constarint voilation) into the table. When I persist something like the above and issue a commit, EL issues a select 1 with the excpetion and tats it the connection isn't released.. – Faz Jun 02 '14 at 09:15
  • Can you post your persistence.xml (is that JTA or RESOURCE_LOCAL?) and how you get the EntityManager and the logs for the case when a UniqueConstraint occurs? – V G Jun 02 '14 at 09:16
  • Sure, its RESOURCE_LOCAL and I use – Faz Jun 02 '14 at 09:19
  • Just change you answer with the additional details. – V G Jun 02 '14 at 09:24