Measuring how many transactions are done on JTA application

Question

I had a web software running in a Jboss AS 7 container witch saves our data in a PostgreSQL 9.1 database via JPA, an its configuration delegated to JTA.

Last year it was adapted to run at AWS EC2 cloud. As the user demand grown our database usage growed too. As expected our database server becomes busy at rush times, an it affected the usage experience from our users.

After some replication researches on PostgreSQL we realise that PGPool2 could be a nice replication solution for our case: it offers Load Balancing for SELECT queries, and Replication for CUD operations ( UPDATE, INSERT and DELETE ) as well.

So far so good, except that it turns the software slow. If fact, as explicited in PGPool2 documentation, SELECT queries will not be load balanced if it was defined in explicit BEGIN/END transaction.

 For a query to be load balanced, all the following requirements must be met:
  - PostgreSQL version 7.4 or later
  - the query must not be in an explicitly declared transaction (i.e. not in a BEGIN ~ END block)
  - it's not SELECT nextval or SELECT setval
  - it's not SELECT INTO
  - it's not SELECT FOR UPDATE nor FOR SHARE
  - it starts with "SELECT" or one of COPY TO STDOUT, EXPLAIN, EXPLAIN ANALYZE SELECT...
  - ignore_leading_white_space = true will ignore leading white space.

Two questions:

How I could figure out our SELECT queries that was running in explicit transactions?
Does _javax.ejb.TransactionAttributeType.NOT_SUPPORTED_ fix the transaction scopes, granting that my SELECT method will be running as "transaction-free"?

score 1 · Accepted Answer · edited May 23 '17 at 11:57

How I could figure out our SELECT queries that was running in explicit transactions?

Turn on pgpool2 logging of SQL and connections:

Put the following statements into pgpool.conf (which you can setup via cp $prefix/etc/pgpool.conf.sample $prefix/etc/pgpool.conf):
```
log_per_node_statement
log_connections
```
Alternatively, turn on log tracing of JPA:

This requires a different method depending or your JPA implementation ( How to view the SQL queries issued by JPA? , JPA 2.0 (logging and tracing through) with Glassfish 3.0.1 and NetBeans 6.9.1: ).

This will log SQL, but will not log transaction start/commit/rollback.

Additionally, put your own debug logging code into methods which start & end transactions, so that you can see when transaction start/commit/rollback.

Does _javax.ejb.TransactionAttributeType.NOT_SUPPORTED_ fix the transaction scopes, granting that my SELECT method will be running as "transaction-free"?

If you are using Container Managed Transactions (annotations @TransactionManagement(CONTAINER) and @TransactionAttribute), then NOT_SUPPORTED will temporarily disassocate the JTA transaction from the current thread. Then the method will run with no transaction context.

Your subsequent JPA query will run outside of the JTA transaction - because the JTA transaction is not available for it to use.

If you already use a Transaction-Scoped EntityManager

Within your Stateless Session Bean you have an EntityManager annotated @PersistenceContext(type=PersistenceContextType.TRANSACTION), or annotated @PersistenceContext without type attribute (because TRANSACTION is the default):
- then that EM will lose it's persistence context within the NOT_SUPPORTED method because the PC is associated with the current transaction, which is no longer accessible
- so you cannot use such an EM in the method (e.g. to run queries or lookup cached objects)
- so you must use an additional application-managed EM within the NOT_SUPPORTED method
- you must create the app-managed EM from an EntityManagerFactory in a place where no JTA transaction is active (e.g. in the NOT_SUPPORTED method), because the app-managed EM will automatically associate itself with the current thread's JTA transaction during creation
- any objects returned from queries by the new app-managed EM will be in a different persistence context from the original EM, so you need great care to cleanly detach such objects from the PC (e.g. appMgdEM.clear() or appMgdEM.close() or appMgdEM.detach(someEntity)) if you are to modify/merge them with the original EM.
If you already use an Extended-Scoped EntityManager

Within your Stateful Session Bean you have an EntityManager annotated @PersistenceContext(type=PersistenceContextType.EXTENDED).
- then that EM will still have it's persistence context within the NOT_SUPPORTED method because the PC is associated with the stateful session bean
- but the EM is using a connection that is already in the middle of a "live" transaction
- so if you want to run queries outside of a transaction, you cannot use such an EM in the method
- so again, you must use an additional application-managed EM within the NOT_SUPPORTED method (same points apply as above).

Example

@Stateless
public class DepartmentManagerBean implements DepartmentManager {

    @PersistenceUnit(unitName="EmployeeService")
    EntityManager txScopedEM;

    @PersistenceUnit(unitName="EmployeeService")
    EntityManagerFactory emf;

    @TranactionAttribute(REQUIRED)
    public void modifyDepartment(int deptId) {
        Department dept = txScopedEM.find(Department.class, deptId);
        dept.setName("New Dept Name");
        List<Employee> empList = getEmpList();
        for(Employee emp : empList) {
            txScopedEM.merge(emp);
            dept.addEmployee(emp);
        }
        dept.setEmployeeCount(empList.size()); 
    }

    @TranactionAttribute(NOT_SUPPORTED)
    public void getEmpList() {
        EntityManager appManagedEM = emf.createEntityManager();
        TypedQuery<Employee> empQuery = appManagedEM.createQuery("...", Employee.class);
        List<Employee> empList = empQuery.getResultList();
        // ...
        appManagedEM.clear();
        return empList;
    }
}

Alternative/Adjusted Approach

The above has some restrictions on how you query and how you use resulting objects. It requires creating an EM "on the fly", if you use stateless session beans, and also requires entityManager.merge() to be called. It may not suit you.

A strong alternative is to redesign your application, so that you run all queries before the transaction starts. Then it should be possible to use a single Extended-Scoped EntityManager. Run the queries in "NOT_SUPPORTED" method 1 (no transaction), using extended-scope EM. Then run modifications in "REQUIRED" method 2 (with transaction), using the same extended-scope EM. A Transaction-Scoped EntityManaged wouldn't work (it would try to be transactional from the very start, and would have no PC in NOT_SUPPORTED methods).

Cheers :)

Thanks for the useful answer. In fact, I had found my self some of answers, but the consistency in your explanation makes me feel safe with my choices. Many thanks. — Miere, Jun 26 '13 at 22:54

score 1 · Answer 2 · answered Jun 25 '13 at 12:54

1

You may want to consider partitioning in JPA using EclipseLink data partitioning,

http://java-persistence-performance.blogspot.com/2011/05/data-partitioning-scaling-database.html

answered Jun 25 '13 at 12:54

James

19,367
9
72
129

I really appreciated the link. Very useful, dude! – Miere Jun 26 '13 at 22:55

Measuring how many transactions are done on JTA application

2 Answers2