How to automatically free cursors with Hibernate?

Question

I have read that hibernate 4.0 until 4.1.3 had the issue of not freeing/deallocate (I am not proficient in database management) the OracleSql cursors after closing the connection or after executing the statement. Although I have increased the maximum cursors on the DataBase, it was just a matter of time until the maximum number of cursors was reached yet again. I am pretty sure this is where the problem resides because the build fails and not when running a particular test or test class.
Is it still a known problem in Hibernate 5? And if yes, how do I manually free the cursors?
I am using, for sake o completeness:
- Hibernate-core-5.0.11 - from what I've read this should handle the cursors
- Hibernate-entitymanager-5.0.11
- Hibernate-jpa-2.1-api-1.0.0
- Oracle database 11.2.0.1

The Repository that (I suppose) causes the problems:

package org.mypackage.status;
import java.util.stream.Stream;
import org.mypackage.entity.status.Status;
import org.mypackage.entity.status.StatusContract;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

public interface StatusContractRepository extends CrudRepository<StatusContract, Long>{
    @Query("select cs from StatusContract cs")
    Stream<StatusContract> streamAll();

    @Query("select cs from StatusContract cs where cs.name=?1")
    StatusContract findOneByName(final Status name);
}

The Service function that I am testing and which uses the aforementioned repository ):

@Service
public class StatusService {

    private final StatusContractRepository statusContractRepository;
    private final ContractRepository contractRepository;

    public StatusService(final StatusContractRepository    statusContractRepository, final ContractRepository contractRepository) {
        this.statusContractRepository = Objects.requireNonNull(statusContractRepository, "statusContractRepository must not be null.");
        this.contractRepository = Objects.requireNonNull(contractRepository, "contractRepository must not be null.");
    }

    public String setContractStatus(final DocComm lastDocComm) {        
        Contract contract = lastDocComm.getContract();

        if (lastDocComm.getAnswer() != null) {
            switch (lastDocComm.getAnswer().getDocumentType().getCode()) {
                case DocumentType.A_REQ_CLARIFY:
                    contract.setStatus(statusContractRepository.findOneByName(Status.CLAR_RECEIVED));
                    break;
                case DocumentType.A_REQ_DOC_ADDITIONAL:
                    contract.setStatus(statusContractRepository.findOneByName(Status.CLAR_RECEIVED));
                    break;
                // there are more cases; they have the same behavior
                default: 
                    break;
           }
        } else if (lastDocComm.getRequest() != null) {
            switch (lastDocComm.getRequest().getDocumentType().getCode()) {
                case DocumentType.REQ_TERMS:
                    contract.setStatus(statusContractRepository.findOneByName(Status.REQ_TERM));
                    break;
                case DocumentType.REQ_MODIFY:
                    contract.setStatus(statusContractRepository.findOneByName(Status.REQ_MODIFY));
                    break;
                // there are more cases; they have the same behavior
                // the first few calls are alright, or when I run specifically one call 
                // the problem occurs at build when it calls all the functions  
                default: 
                    break;
            }
        }
        contractRepository.save(contract);
        return contract.getStatus().getFullName();
    }
}

The Log from NetBeans:

testChangeStatus_1
[WARN ] 2017-04-18 09:07:13 [o.h.e.j.s.SqlExceptionHelper:127]- SQL Error: 604, SQLState: 60000
2017-04-18 09:07:13 [o.h.e.j.s.SqlExceptionHelper:129]- ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded

[INFO ] 2017-04-18 09:07:13 [o.h.e.i.DefaultLoadEventListener:129]- HHH000327: Error performing load command : org.hibernate.exception.GenericJDBCException: could not extract ResultSet
[INFO ] 2017-04-18 09:07:13 [o.h.e.i.DefaultLoadEventListener:129]- HHH000327: Error performing load command : org.hibernate.exception.GenericJDBCException: could not extract ResultSet
[INFO ] 2017-04-18 09:07:13 [o.h.e.i.DefaultLoadEventListener:129]- HHH000327: Error performing load command : org.hibernate.exception.GenericJDBCException: could not extract ResultSet
[INFO ] 2017-04-18 09:07:14 [o.m.a.AbstractEntityListener:63]- DECLARE return_code number; BEGIN SP_STERGE_INREGISTRARE ('COMMUNICATION_DOC',6515, 'null', to_date('2017-04-18','YYYY-mm-dd'), 'FO', return_code); END; 
[INFO ] 2017-04-18 09:07:14 [o.m.a.AbstractEntityListener:67]- SQL_CALL_RESULT: 1
[INFO ] 2017-04-18 09:07:14 [o.m.a.AbstractEntityListener:63]- DECLARE return_code number; BEGIN SP_STERGE_INREGISTRARE ('CONTRACT',9824, 'null', to_date('2017-04-18','YYYY-mm-dd'), 'FO', return_code); END; 
[INFO ] 2017-04-18 09:07:14 [o.m.a.AbstractEntityListener:67]- SQL_CALL_RESULT: 1
[INFO ] 2017-04-18 09:07:14 [o.m.a.AbstractEntityListener:63]- DECLARE return_code number; BEGIN SP_STERGE_INREGISTRARE ('DOCUMENT',21551, 'null', to_date('2017-04-18','YYYY-mm-dd'), 'FO', return_code); END; 
[INFO ] 2017-04-18 09:07:14 [o.m.a.AbstractEntityListener:67]- SQL_CALL_RESULT: 1
[INFO ] 2017-04-18 09:07:14 [o.m.a.AbstractEntityListener:63]- DECLARE return_code number; BEGIN SP_STERGE_INREGISTRARE ('DOCUMENT',21552, 'null', to_date('2017-04-18','YYYY-mm-dd'), 'FO', return_code); END; 
[INFO ] 2017-04-18 09:07:14 [o.m.a.AbstractEntityListener:67]- SQL_CALL_RESULT: 1
testChangeStatus_2
[WARN ] 2017-04-18 09:07:14 [o.h.e.j.s.SqlExceptionHelper:127]- SQL Error: 604, SQLState: 60000
2017-04-18 09:07:14 [o.h.e.j.s.SqlExceptionHelper:129]- ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded
...

This goes on for a number of other test functions.
If there's any need for further details, I will hastily provide them.

score 0 · Answer 1 · answered Apr 18 '17 at 08:27

0

could you please provide your JPA/Hibernate code that is using DB cursors. I think that you should call close() method (probably on org.hibernate.ScrollableResults) in your Java code to close cursor object.

answered Apr 18 '17 at 08:27

bbelovic

61
4

I am using Controller - Service - Repository architecture. And currently testing the **Service** functionality, there is no need to test the Repository, _Hibernate_: insert, listAll, etc. Of course there are custom findByMyCustomField with a specific @Query What part of the code should I provide? The Service or the Repository? I am asking this because I think I'll better understand the problem, if, say only the Repository should be provided. – Rares Sabin Rusu Apr 18 '17 at 09:17
I have misread your request. I am not using any cursor objects per se. We have just a bunch of repositories which are used by the services. From what I've read, _Hibernate_ doesn't explicitly close/free the cursors fetched automatically when executing a statement. – Rares Sabin Rusu Apr 18 '17 at 09:25
could you post that part of code that is working with database cursors? Be it Java repository class or some database procedure language call. Also could you check that the code which is working with cursors is freeing cursors correctly? – bbelovic Apr 18 '17 at 10:45
As you can see I am not _using/opening_ the cursors anywhere explicitly. And I can't figure out where the cursors _are/should_ be freed by Hibernate so I can Overwrite(?) the method. – Rares Sabin Rusu Apr 18 '17 at 11:32
Could some else application be using db server and not closing cursors? Does Oracle have something like statistics table where you can see all opened connections to database and what these connections are doing (what queries are executing) ? – bbelovic Apr 18 '17 at 11:54
I have talked to the colleagues managing the database. The cursor leak problem occurs only on build, i.e. when all the tests are run / all the calls are made. So the answer is no 99%, there is no other application that doesn't close the cursors. – Rares Sabin Rusu Apr 18 '17 at 11:58
And you are right, I understood that the cursor must be closed after you've done working with it, but I can't figure out how... And as a side question, isn't _Hibernate_ supposed to **automatically close** them? – Rares Sabin Rusu Apr 18 '17 at 12:11
I think application has to close cursors on its own, cursors are not closed automatically by Hibernate. It might be the case some of your tests (or the code under the test) do not close cursor object and it leak. – bbelovic Apr 18 '17 at 13:27

How to automatically free cursors with Hibernate?

1 Answers1