Supporting Custom Isolation Levels With JPA

This post provides a workaround for supporting custom isolation levels with JPA. If you use the HibernateJpaDialect (provided by spring)  and try to set an isolation level different from the default one you would get an exception “Standard JPA does not support custom isolation levels – use a special JpaDialect”

The above error would be seen when your configuration is as below

<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager" lazy-init="true">
   <property name="entityManagerFactory" ref="entityManagerFactory"/>
</bean>

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
          lazy-init="true">
        <property name="persistenceUnitName" value="spmsoftwareunit"/>
        <property name="persistenceXmlLocation" value="classpath:com/spmsoftware/sampleapp/conf/persistence.xml"/>
        <property name="dataSource" ref="oracleDataSourceFactoryBean"/>
        <property name="persistenceProviderClass" value="org.hibernate.ejb.HibernatePersistence"/>
        <property name="jpaDialect">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect"/>
        </property>
        <property name="jpaProperties">
            <props>
                <prop key="hibernate.id.new_generator_mappings">true</prop>
                <prop key="hibernate.cache.use_second_level_cache">false</prop>
                <prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
                <prop key="hibernate.jdbc.batch_size">3</prop>
                <prop key="hibernate.jdbc.batch_versioned_data">true</prop>
                <prop key="hibernate.jdbc.factory_class">com.spmsoftware.sampleapp.persistence.OracleBatchingBatcherFactory</prop>
                <prop key="hibernate.hbm2ddl.auto">none</prop>
                <prop key="hibernate.show_sql">true</prop>
            </props>
        </property>
    </bean>

and the transaction is applied as

@Transactional(isolation=Isolation.SERIALIZABLE, propagation=Propagation.REQUIRED)

WorkAround

We can extend HibernateJpaDialect to modify the connection instance before the transaction starts

import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.orm.jpa.vendor.HibernateJpaDialect;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionException;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceException;
import java.sql.Connection;
import java.sql.SQLException;

public class HibernateExtendedJpaDialect extends HibernateJpaDialect {

    private Logger logger = LoggerFactory.getLogger(HibernateExtendedJpaDialect.class);

    /**
     * This method is overridden to set custom isolation levels on the connection
     * @param entityManager
     * @param definition
     * @return
     * @throws PersistenceException
     * @throws SQLException
     * @throws TransactionException
     */
    @Override
    public Object beginTransaction(final EntityManager entityManager,
            final TransactionDefinition definition) throws PersistenceException,
            SQLException, TransactionException {
        Session session = (Session) entityManager.getDelegate();
        if (definition.getTimeout() != TransactionDefinition.TIMEOUT_DEFAULT) {
            getSession(entityManager).getTransaction().setTimeout(definition.getTimeout());
        }

        entityManager.getTransaction().begin();
        logger.debug("Transaction started");

        session.doWork(new Work() {

            public void execute(Connection connection) throws SQLException {
                logger.debug("The connection instance is {}", connection);
                logger.debug("The isolation level of the connection is {} and the isolation level set on the transaction is {}",
                        connection.getTransactionIsolation(), definition.getIsolationLevel());
                DataSourceUtils.prepareConnectionForTransaction(connection, definition);
            }
        });

        return prepareTransaction(entityManager, definition.isReadOnly(), definition.getName());
    }

}

The above implementation takes care of setting the isolation level specified on the transaction definition by calling DataSourceUtils.prepareConnectionForTransaction(connection, definition);

Since the isolation level is changed on the connection instance, resetting it is important. This would have to be done in the data source since the connection gets closed as soon as the transaction is committed. There is no way to intercept the JpaTransactionManager code flow to reset the isolation level after commit is done.
By decorating the data source and setting the isolation level before the connection is given by the connection pool, one can achieve resetting of the isolation level on the connection instance to default (which in most of the cases is read committed).

Advertisement

Oracle JDBC driver and connection thread safety

Oracle JDBC developer guide recommends not to share connections across multiple threads. Here is small excerpt from the Oracle guide

“Oracle JDBC drivers provide full support for, and are highly optimized for, applications that use Java multithreading. Controlled serial access to a connection, such as that provided by connection caching, is both necessary and encouraged. However, Oracle strongly discourages sharing a database connection among multiple threads. Avoid allowing multiple threads to access a connection simultaneously. If multiple threads must share a connection, use a disciplined begin-using/end-using protocol.”

Self Injection in Spring

Since Spring AOP is proxy based, only ‘external’ method calls coming in through the proxy will be intercepted. i.e. This means that ‘self-invocation’, i.e. a method within the target object calling some other method of the target object, won’t lead to an actual transaction at runtime even if the invoked method is marked with @Transactional.

Why @Autowired does not work

@Service
public class UserService implements Service{
   @Autowired
   private Service self;
}

The above code throws a NoSuchBeanDefinitionException. Logically speaking @Autowired doesn’t work because the bean can’t be injected until it has been fully constructed. This means that Spring has to have injected all properties of the bean. Effectively the above code creates a circular dependency because Spring tries to instantiate the bean and when it does, it discovers that it needs to Autowire another bean. When it tries to find that bean it can’t because the bean hasn’t been added to the list of initialized beans (because it’s currently being initialized).

Alternatives

Below are couple of approaches which work.

@Service(value = "someService")
public class UserService implements Service{
   @Resource(name = "someService")
   private Service self;
}

or

@Service
public class UserService implements Service {

    @Autowired
    private ApplicationContext applicationContext;

    private Service self;

    @PostConstruct
    private void init() {
        self = applicationContext.getBean(UserService);
    }
}

Spring transaction behavior in private & internal methods

This post is to detail on the spring behavior when @Transactional annotations are applied on private methods or when transactional methods are called from within a method in the same class.

Transactional Private Methods

public class Bean {
  public void doStuff() {
     doPrivateStuff();
  }
  @Transactional
  private void doPrivateStuff() {

  }
}

...

Bean bean = (Bean)appContext.getBean("bean");
bean.doStuff();

@Transactional will have no effect if used to annotate private methods. The proxy generator will ignore them.
As per the Spring manual

Method visibility and @Transactional
When using proxies, you should apply the @Transactional annotation only to methods with public visibility. If you do annotate protected, private or package-visible methods with the @Transactional annotation, no error is raised, but the annotated method does not exhibit the configured transactional settings. Consider the use of AspectJ if you need to annotate non-public methods.

Calling internal transactional methods

public class Bean {
   public void doStuff() {
     doTransactionStuff();
   }

   @Transactional
   public void doTransactionStuff() {

   }
}

This will open a transaction:

Bean bean = (Bean)appContext.getBean("bean");
bean.doTransactionStuff();

This will not:

Bean bean = (Bean)appContext.getBean("bean");
bean.doStuff();

As per the Spring manual
Note: In proxy mode (which is the default), only ‘external’ method calls coming in through the proxy will be intercepted. This means that ‘self-invocation’, i.e. a method within the target object calling some other method of the target object, won’t lead to an actual transaction at runtime even if the invoked method is marked with @Transactional!

Consider the use of AspectJ mode if you expect self-invocations to be wrapped with transactions as well. In this case, there won’t be a proxy in the first place; instead, the target class will be ‘weaved’ (i.e. its byte code will be modified) in order to turn @Transactional into runtime behavior on any kind of method.

Summary

The @Transactional annotation defines a transaction boundary, and Spring will start/rollback/commit the transaction every time a @Transactional-annotated method is entered or exited.
If you execute a @Transactional-annotated method, and that method then calls another method in another class, and that one calls another one, which eventually does some database work, then you’re still inside the transaction.

Estimating the memory usage of a java object

JDK 1.5 introduces the java.lang.instrument package which provides services that allow java programming agents to instrument programs running on the JVM. In simple words an agent is an interceptor in front of your main method, executed in the same JVM and loaded by the same system classloader, and governed by the same security policy and context.

We can create an instrumentation agent in Java by writing a class containing a premain(String, Instrumentation) method. This method is called by the JVM on startup and an instance of Instrumentation is passed in.

import java.lang.instrument.Instrumentation;

public class ObjectSizeFetcher {
    private static Instrumentation instrumentation;

    public static void premain(String args, Instrumentation inst) {
        instrumentation = inst;
    }

    public static long getObjectSize(Object o) {
        return instrumentation.getObjectSize(o);
    }
}

Since an agent has to be packaged in a jar file, compile the above class and package it in a jar file. Add a MANIFEST.MF file in the jar file under META-INF folder. The MANIFEST.MF file should contain the below line
Premain-Class: ObjectSizeFetcher

Use getObjectSize method

public class C {
   private int x;
   private int y;

   public static void main(String [] args) {
     System.out.println(ObjectSizeFetcher.getObjectSize(new C()));
   }
}

Invoke the above class C using the below command
java -javaagent:<path to objectsizefetcheragent.jar> C

Note that the instrumentation interface gives you a flat size of the object and does not recursively estimate the size of the objects that it references. To calculate the deep size of an object have a look at this article from java specialist.

Useful TimeUnit enum in java

JDK 1.5 introduces the TimeUnit enum that represents time durations at a given unit of granularity and provides utility methods to convert across units, and to perform timing and delay operations in these units.

Although it is part of the java.util.concurrent package, the TimeUnit enum is useful in many contexts outside of concurrency.

One of the clear advantages include code readability. Below are few code samples to demonstrate this

Code Sample 1 –
Instead of implementing the convertMillisToDays as below

/**
    * Convert provided number of milliseconds into number of days.
    *
    * @param numberMilliseconds Number of milliseconds to be converted into days.
    * @return Number of days corresponding to number of provided milliseconds.
    */
   private static long convertMilliSecondsToDaysViaSingleMagicNumber(final long numberMilliseconds)
   {
      // 86400000 = 86400 seconds in a day multipled by 1000 ms per second
      return numberMilliseconds / 86400000;
   }

A more readable code would look like

/**
    * Convert provided number of milliseconds into number of days.
    *
    * @param numberMilliseconds Number of milliseconds to be converted into days.
    * @return Number of days corresponding to number of provided milliseconds.
    */
   private static long convertMillisecondsToDaysViaTimeUnit(final long numberMilliseconds)
   {
      return TimeUnit.MILLISECONDS.toDays(numberMilliseconds);
   }

Code Sample 2 –
Instead of calling

Thread.sleep(5000); //Sleeping for 5 seconds

A more readable code would look like

TimeUnit.SECONDS.sleep(5);

The enum constant of theTimeUnit implies the applicable unit of time, so only a base number needs to be provided. The implication here, of course, is that more obvious numbers can be provided for sleeping rather than needing to worry about expressing a large number in milliseconds or even remembering that the method requires the time be specified in milliseconds.

Besides SECONDS, other time unit representations provided by TimeUnit include DAYS, HOURS,MICROSECONDS, MINUTES, NANOSECONDS, and MILLISECONDS.

Two other related useful methods available in TimeUnit are TimeUnit.timedJoin(Thread,long)[convenience method for Thread.join] and TimeUnit.timedWait(Thread,long) [convenience method forObject.wait].

This post is inspired from The Highly Useful Java TimeUnit Enum.

Global Temporary Table In Oracle

Introduction

Global temporary tables are types of database tables which can privately store data, persistently for a session or transaction. The data flushes out at defined instant automatically. They often find their application in the situations where data fetch and passage is not possible in single stretch.

Note that only table data is session specific, but physically table is available in all sessions.

Syntax

CREATE GLOBAL TEMPORARY TABLE <Table-name>
(
[COLUMN DEFINTION]
) ON COMMIT [DELETE | PRESERVE] ROWS;

The default create statement is the equivalent of adding the clause ON COMMIT DELETE ROWS. An alternative is to specify ON COMMIT PRESERVE ROWS.

ON COMMIT DELETE ROWS sets the life of the data contained by the table to a single TRANSACTION. The data is automatically flushed away after each COMMIT/ROLLBACK is executed. These are Transaction-specific Temporary tables.
On the other hand, ON COMMIT PRESERVE ROWS restricts the life of the data to a single SESSION. Data is preserved in the table for a session only. These are Session-specific Temporary tables.

Simple Example

--Create a simple GTT with default options:

CREATE GLOBAL TEMPORARY TABLE
temp_gtt (id NUMBER(20)) ;

--Insert some data and do a SELECT

BEGIN
  FOR i in 1..10 LOOP
    INSERT INTO temp_gtt values (i) ;
  END LOOP ;
END ;

SELECT count(*) FROM temp_gtt;

--result:

COUNT(*)
----------------------
10

--Do a commit and run the query again

COMMIT;

SELECT count(*) FROM temp_gtt;

--result:

COUNT(*)
----------------------
0

Important points about GTT

1. In Oracle temporary table and global temporary table are synonymous. You cannot create a temp table without the keyword “global”
2. GTT data is private to a session. Although there is a single table definition, each session uses a GTT as if it was privately owned. Truncating data in a GTT will not affect other users sessions.
3. Depending on the table definition, data in a GTT will either be removed or retained after a commit. However it is always removed when the session terminates even if the session ends abnormally.
4. Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session.
5. The table and any associated indexes are stored in the users temporary tablespace. Starting with 11g Oracle introduced the option to specify the temp tablespace for a GTT (but not its indexes).
6. Views can be created against temporary tables and combinations of temporary and permanent tables.
7. Foreign key constraints are not applicable in case of Temporary tables
8. Temporary tables can have triggers associated with them.
9. Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
10. Putting data in a temporary table is more efficient than placing this data in a permanent table. This is primarily due to less redo activity when a session is applying DML to temporary tables. DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Oracle writes data for temporary tables into temporary segments and thus doesn’t require redo log entries. Oracle writes rollback data for the temporary table into the rollback segments (also known as the undo log). Even though redo log generation for temporary tables will be lower than permanent tables, it’s not entirely eliminated because Oracle must log the changes made to these rollback segments. To summarize – “log generation should be approximately half of the log generation (or less) for permanent tables.”
11. Temporary tables cannot be partitioned.
12. If you use a Global Temporary Table in your application code, watch where and how you run GATHER_TABLE_STATS on it.
1. If your GTT has been defined as ON COMMIT DELETE ROWS, the GATHER_TABLE_STATS call will result in rows being deleted. This is because the GATHER_TABLE_STATS issues an implicit commit.
2. If your GTT has been defined as ON COMMIT PRESERVE ROWS, the GATHER_TABLE_STATS will not delete rows in the table.

Unindexed Foreign Keys In Oracle

Reading this excellent book on Oracle architecture – Expert One-On-One Oracle – By Thomas Kyte, I came through an interesting detail on deadlocks. The author mentions that based on his experience unindexed foreign keys are the number one causes of deadlocks.

There are two cases where Oracle will place a full table lock on a child table after modification of the parent table:

•          If I update the parent tableʹs primary key (a very rare occurrence if you follow the rules of relational databases that primary keys should be immutable), the child table will be locked in the absence of an index.
•          If I delete a parent table row, the entire child table will be locked (in the absence of an index) as well.

Deleting a parent table row 

Lets take an example of the second scenario which is more common. If I delete a row in table P, then the child table, C, will become locked ‐ preventing other updates against C from taking place for the duration of my transaction (assuming no one else was modifying C, of course; in which case my delete will wait). This is where the blocking and deadlock issues come in. By locking the entire table C, I have seriously decreased the  concurrency in my database ‐ no one will be able to modify anything in C. In addition, I have increased the probability of a deadlock, since I now ʹownʹ lots of data until I commit.

The probability that some other session will become blocked on C is now much higher; any session that tries to modify C will get blocked. Therefore, Iʹll start seeing lots of sessions that hold some pre‐existing locks getting blocked in the database. If any of these blocked sessions are, in fact, holding a lock that my session needs ‐ we will have a deadlock. The deadlock in this case is caused by my session obtaining many more locks then it ever needed.

Other Issues

By indexing the foreign key column, we can remove this locking issue all together. In addition to this table lock, an un‐indexed foreign key can also be problematic in the following cases:

• When you have an ON DELETE CASCADE and have not indexed the child table. For example, EMP is child of DEPT. DELETE DEPTNO = 10 should CASCADE to EMP. If DEPTNO in EMP is not indexed, you will get a full table scan of EMP. This full scan is probably undesirable, and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted.
• When you query from the parent to the child. Consider the EMP/DEPT example again. It is very common to query the EMP table in the context of a DEPTNO. If you frequently run the following query, say to generate a report, youʹll find that not having the index in place will slow down the queries:

select * from dept, emp where emp.deptno = dept.deptno and dept.deptno = :X;

I don’t need an indexed foreign key

So, when do you not need to index a foreign key? The answer is, in general, when the following conditions are met:

• You do not delete from the parent table.
• You do not update the parent tableʹs unique/primary key value (watch for unintended updates to the primary key by tools!
• You do not join from the parent to the child (like DEPT to EMP)

If you satisfy all three above, feel free to skip the index ‐ it is not needed. If you do any of the above, be aware of the consequences. This is the one very rare time when Oracle tends to ʹover‐lockʹ data.

Oracle Database Change Notification and OCI JDBC Driver

Oracle supports a feature called query change notification which allows an application to register queries with the database for either object change notification (default) or query result change notification. This feature is a good candidate for an application to implement caching.

The JDBC drivers can register SQL queries with the database and receive notifications in response to the following:

• DML or DDL changes on the objects associated with the queries
• DML or DDL changes that affect the result set

The notifications are published when the DML or DDL transaction commits (changes made in a local transaction do not generate any event until they are committed).

To use Oracle JDBC driver support for Database Change Notification, perform the following:

1. Registration: You first need to create a registration.
2. Query association: After you have created a registration, you can associate SQL queries with it. These queries are part of the registration.
3. Notification: Notifications are created in response to changes in tables or result set. Oracle database communicates these notifications to the JDBC drivers through a dedicated network connection and JDBC drivers convert these notifications to Java events.

Some good resources to understand this feature can be found from the oracle documentation.

This feature is not supported yet by the OCI JDBC driver (version 11.2.0.1.0). The registration step fails with an exception “java.sql.SQLException: Unsupported feature”. This can also be verified by decompiling the driver and having a look at the oracle.jdbc.driver.PhysicalConnection doRegisterDatabaseChangeNotification() method.
Note that the thin driver supports this feature.

Oracle Client Result Query Cache

Oracle 11g introduces a number of features to expand the use of memory caching in order to improve query performance and decrease the overall memory requirements of queries and applications. In addition to the features that support server-side caching, there is also a new feature to support a client-side result cache that allows OCI clients to fetch result sets stored in process memory instead of requiring access to the server’s memory. The “Client Result Cache” is enabled and configured at the database level and available to all OCI-based clients.

Using the client’s memory to cache query result sets significantly improves the performance of repetitive queries by OCI-based clients that would otherwise require a cache hit on the server-side. The client cache utilizes per-process memory on the OCI client and its contents can be shared across multiple sessions and threads. In certain situations, this will greatly reduce the number of round-trips between the client and the database server, reducing CPU consumption on the server and greatly increasing the response times. Situations that will benefit most from the client cache are queries that use small lookup tables or mostly read-only tables. 

Note that this feature is not supported in Standard Edition.