Search code examples
mysqljpaspring-data-jpaeclipselink

EclipseLink: ConnectionPools and native queries

We are using Spring (Eclipselink) on MariaDB. Our SQL via ORM result in a long lasting DB query. Therefore I need to refine it into a nativequery - which is no big deal by itself. Nevertheless the Resultset is limited by a LIMIT and I need a total counter for all found records. For querying the total counter I found for MariaSQL the following solution.

My question:

Is it save to query the two SQL command separately or should I send them once with a UNION combined?

The question arises due to the fact that between my query and the SELECT FOUND_ROWS() the another query might interfere (with a request from the same Microservice) and dilute the result.

Solution

  • If both queries are done in the same transaction, the MVCC of INNODB should guarantee, that the results will not be influenced by other transactions.

    see: https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html