Monday, August 30, 2010

A lot of optimism with a little pessimism

org.springframework.orm.hibernate3.HibernateOptimisticLockingFailureException: Object of class [xyz.WlanServiceTemplate] with identifier [17]: optimistic locking failed; nested exception is org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect):

This StaleObjectStateException is quiet common when optimistic locking strategy (FIRST WIN) is applied in persistence. Generally it indicates the caller side has a out-of-date copy than the one in DB. It prevents the lost-update issue, which is OK for UI since the use could reload and retry. But for backend, this StaleObjectStateException may be annoying because LAST WIN may be acceptable. One straightforward but cumbersome approach is to catch the exception, rollback and retry again until succeed.

A much better approach is to apply pessimistic lock (session.get(id, LockMode.UPGRADE) in such scenario, so called "a lot of optimism with a little pessimism" which neatly avoid the need to EVER catch or recover from StaleObjectExceptions!

Thursday, August 12, 2010

Custom BIT functions in HQL


public class MySQLDialect extends MySQLInnoDBDialect {

public MySQLDialect() {
super();
registerFunction("bit_or", new StandardSQLFunction("BIT_OR", Hibernate.INTEGER));
registerFunction("bitwise_and", new VarArgsSQLFunction(Hibernate.INTEGER, "", "&", ""));
registerFunction("bitwise_or", new VarArgsSQLFunction(Hibernate.INTEGER, "", "|", ""));
}
}

String queryString = "select bit_or(cc.protocol) as protocolBitmask "
+ "from CtlCountry cc, ApHardwareTypeProfile atp, ApProfileRadio radio "
+ "where cc.platformName=atp.ctlPlatformName "
+ "and atp.profileType=:profileType "
+ "and atp.profileType=radio.profile "
+ "and cc.countryCode=:country "
+ "and radio.radioIndex=:radioIndex "
+ "and bitwise_and(cc.protocol, radio.radioCap) = cc.protocol";

MySQL's BIT_OR(), BIT_AND() operators are like COUNT(), AVG(), they apply bit operations on all matched rows. Bitwise operators &, | apply on expression.

A strange "java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode" error occurred when I used upper case function name "BIT_OR" in HQL. After I changed it to use lower case "bit_or", it works.