My application uses Hibernate as ORM. I am trying to execute few mysql locking function within my application like GET_LOCK, IS_FREE_LOCK, RELEASE_LOCK (https://dev.mysql.com/doc/refman/5.6/en/locking-functions.html).
However I am running into following issue:
Hibernate: SELECT COALESCE(GET_LOCK(?, ?), 0)
WARN [org.hibernate.util.JDBCExceptionReporter,main] (JDBCExceptionReporter.java:77) - SQL Error: 90022, SQLState: 90022
ERROR [org.hibernate.util.JDBCExceptionReporter,main] (JDBCExceptionReporter.java:78) - Function "GET_LOCK" not found; SQL statement:
SELECT COALESCE(GET_LOCK(?, ?), 0) [90022-186]
Tests run: 2, Failures: 0, Errors: 1, Skipped: 1, Time elapsed: 5.626 sec <<< FAILURE! - in com.lock.dao.LockDaoTest
testGetLock(com.lock.dao.LockDaoTest) Time elapsed: 5.397 sec <<< ERROR!
org.hibernate.exception.GenericJDBCException: could not execute query
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.command.Parser.readJavaFunction(Parser.java:2341)
at org.h2.command.Parser.readFunction(Parser.java:2393)
at org.h2.command.Parser.readTerm(Parser.java:2727)
at org.h2.command.Parser.readFactor(Parser.java:2259)
at org.h2.command.Parser.readSum(Parser.java:2246)
at org.h2.command.Parser.readConcat(Parser.java:2216)
at org.h2.command.Parser.readCondition(Parser.java:2066)
at org.h2.command.Parser.readAnd(Parser.java:2038)
at org.h2.command.Parser.readExpression(Parser.java:2030)
at org.h2.command.Parser.readFunction(Parser.java:2543)
at org.h2.command.Parser.readTerm(Parser.java:2727)
at org.h2.command.Parser.readFactor(Parser.java:2259)
at org.h2.command.Parser.readSum(Parser.java:2246)
at org.h2.command.Parser.readConcat(Parser.java:2216)
at org.h2.command.Parser.readCondition(Parser.java:2066)
at org.h2.command.Parser.readAnd(Parser.java:2038)
at org.h2.command.Parser.readExpression(Parser.java:2030)
at org.h2.command.Parser.parseSelectSimpleSelectPart(Parser.java:1942)
at org.h2.command.Parser.parseSelectSimple(Parser.java:1974)
at org.h2.command.Parser.parseSelectSub(Parser.java:1868)
at org.h2.command.Parser.parseSelectUnion(Parser.java:1689)
at org.h2.command.Parser.parseSelect(Parser.java:1677)
at org.h2.command.Parser.parsePrepared(Parser.java:433)
at org.h2.command.Parser.parse(Parser.java:305)
at org.h2.command.Parser.parse(Parser.java:277)
at org.h2.command.Parser.prepareCommand(Parser.java:242)
at org.h2.engine.Session.prepareLocal(Session.java:446)
at org.h2.engine.Session.prepareCommand(Session.java:388)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1189)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:72)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:277)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
at org.hibernate.loader.Loader.doQuery(Loader.java:673)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2213)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:811)
at com.distributed.lock.manager.ApplicationLockDao.getLock(ApplicationLockDao.java:41)
at com.distributed.lock.manager.ApplicationLockDao$$FastClassByCGLIB$$860b7963.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:698)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:631)
Here is my Dao code:
Query sqlQuery = getSession().createSQLQuery("SELECT COALESCE(GET_LOCK(:lock, :timeout), 0)");
sqlQuery.setString("lock", lock);
sqlQuery.setInteger("timeout",1));
Integer lockAcquired = (Integer) sqlQuery.uniqueResult();
I am not sure how to execute locking functions using Hibernate. Any help/pointer is appreciated.
Thanks in advance.
Advertisement
Answer
I found the problem. It was with H2 database which I was trying to use for unit tests and not with hibernate. Reference : https://proj.goldencode.com/issues/2347
After removing H2 dependency and directly using mysql, I could successfully run the tests with just a change in the return type :
Query sqlQuery = getSession().createSQLQuery("SELECT COALESCE(GET_LOCK(:lock, :timeout), 0)");
sqlQuery.setString("lock", lock);
sqlQuery.setInteger("timeout",1));
BigInteger lockAcquired = (BigInteger) sqlQuery.uniqueResult();