[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3644) Add support for "WITH UR" isolation clause on DB2

[Ricardo Fernandes (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3644?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=32665#action_32665 ] 

Ricardo Fernandes commented on HHH-3644:
----------------------------------------

Fortunately I've found a way around this issue by using Hibernate's Interceptors.

The idea is to create a specific DB2 interceptor which transform the queries before they are sent to the database.
The interceptor code goes  like this:

/**
 * A class for intercepting the calls to a DB2 database in order
 * to change the query string for including some special instructions
 * useful in DB2, according to the following rules:<br>
 * <ul>
 * <li>FOR READ ONLY - added to all select queries</li>
 * <li> WITH XX - where XX stands for one of the 4 different isolation
 * levels known by DB2: CS, RS, RR or UR
 * </li>
 * </ul> 
 * @author Ricardo Fernandes
 *
 */
public class DB2Interceptor extends EmptyInterceptor {

	/** */
	private static final long serialVersionUID = 1L;

	@Override
	public String onPrepareStatement(String str) {
	
	  // Transform the original query into lower case
		String compstr = str.toLowerCase();
		
		// Check if we're dealing with a simple select statement
		if (compstr.matches("^select.*") && !compstr.matches(".*for update.*")) { 
			
			// We are, so add the 'for read only' clause
			if (!compstr.matches(".*for read only.*")) {
				str += " for read only";
			
			}
		
			// Checks whether the 'client' asked for a specific isolation level
			// to be applied to this query
			TransactionManager manager = ThreadTools.getTransactionManager();
			if (manager != null && manager.getIsolationLevel() != TransactionManager.ISOLATION_DEFAULT) {
				String suffix = null;
				String expr = null;
				switch(manager.getIsolationLevel()) 
				{
				case TransactionManager.ISOLATION_READ_COMMITTED:
					// The same as CURSOR STABILITY (CS) in the DB2 world 
					suffix = " with cs ";
					expr = ".*with cs.*";
					break;
				case TransactionManager.ISOLATION_REPEATABLE_READ:
					// The same as READ STABILITY (RS) in the DB2 world
					suffix = " with rs ";
					expr = ".*with rs.*";
					break;
				case TransactionManager.ISOLATION_SERIALIZABLE:
					// The same as REPEATABLE READ (RR) in the DB2 world
					suffix = " with rr ";
					expr = ".*with rr.*";
					break;
				case TransactionManager.ISOLATION_UNCOMMITTED_READ:
					suffix = " with ur ";
					expr = ".*with ur.*";
					break;
				default:
					// This is empty
					break;
				}
				
				// Check if we should apply the isolation level to the query 
				if (suffix != null && !compstr.matches(expr)) {
					str += suffix;
				}
			}
		}
		return str;
	}
}

TransactionManager is a class used for storing state information for the current execution thread. 
In this case it holds the isolation level that should be applied to the current query.
It also has a set of constants for defining the isolation level.
ThreadTools is wrapper for a Hashtable associated with the current thread. 
HibernateSingleton is a singleton for storing application scope variables (such as the sessionFactory)
A sample usage of these classes might be:

public List sampleQuery(String classname)
    List list = null;
    try {
			ThreadTools.getTransactionManager().setIsolationLevel(TransactionManager.ISOLATION_UNCOMMITTED_READ);
			Criteria criteria = HibernateSingleton.instance().getSessionFactory().getCurrentSession().createCriteria(classname);
			list = criteria.list();
		} catch (Exception e) {
			log.error("Error executing query for " + classname, e);
			throw new DatabaseException("Error executing query for " + classname, e);
		}
		finally {
			// Reset the isolation level
			ThreadTools.getTransactionManager().resetIsolationLevel();
		}
		return list;
}

The interceptor might be associated with the hibernate's session factory on startup. Something like this:
...
       // Configure the session factory
       Configuration configuration = new Configuration().configure("hibernate.cfg.xml");
	configuration.setProperty("hibernate.transaction.factory_class", "org.hibernate.transaction.JDBCTransactionFactory");
	configuration.setProperty("hibernate.current_session_context_class", "thread");
	configuration.setInterceptor(new DB2Interceptor());
	sessionFactory = configuration.buildSessionFactory();
	HibernateSingleton.instance().setSessionFactory(sessionFactory);
...

Hope this may help others having the same issues that I had.

Best regards,
Ricardo Fernandes

> Add support for "WITH UR" isolation clause on DB2
> -------------------------------------------------
>
>                 Key: HHH-3644
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3644
>             Project: Hibernate Core
>          Issue Type: Sub-task
>          Components: core
>    Affects Versions: 3.3.1
>         Environment: HIbernate 3.3.1 / DB2 9.x
>            Reporter: Ricardo Fernandes
>   Original Estimate: 2 days
>  Remaining Estimate: 2 days
>
> The question is basically the same as Steve's, i.e. to force DB2 to use as few locks as possible on heavy load scenarios. 
> Although some might consider the use of the WITH UR clause a bad approach (since it allows dirty reads), the fact is that there are some cases where this is in fact acceptable, such as computing the total amount of rows of a query (for information purposes) or producing a high-level listing of items with very little detailed info. The bottom line is: if I need to trade performance for extremely accurate data, I surely want to be able to decide when this should happen.
> I've already performed the changes that makes it possible to use both the FOR READ ONLY and the WITH UR clauses and I will be submitting a patch shortly so you can have a look at it. The strategy I've used was basically the following:
> 1. Added two new methods on the Dialect class:
> String getDatabaseReadOnlyString(String sql) - for adding the READ ONLY clause
> String getDirtyReadsString(String sql) - for adding the WITH UR clause
> 2. Changed the Query interface in order to allow the user to say whether he/she wants the query to allow dirty reads:
> Query setAllowDirtyReads(boolean allowDirtyReads);
> 3. Added a default implementation on the AbstractQueryImpl which initializes the flag a false
> 4. Added a similar attribute on the QueryParameters class
> 5. Changed the prepareQueryStatement() method of the Loader class (just after the useLimit part in order:
> a) Ask the dialect for the getDatabaseReadOnlyString() is there are no LockModes set (as did Steve)
> b) Ask the dialect for the getDirtyReadsString() is the queyParameters allows dirty reads.
> All the tests were well succeeded.
> Hope you find these changes, at least, worth looking at.
> Best Regards,
> Ricardo

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira