Home › Blogs › Andrew Wills's blog ›

A Warning About uPortal 3 and MS SQL Server

By Andrew Wills
August 22, 2008

Here's something you really should know if you use the MS SQL Server database platform and you're considering an upgrade to uPortal 3: you will need to enable the ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT options in your portal database. If you don't enable them, you'll encounter a nasty issue in the area of portlet preferences.

You can make these changes by issuing the following commands in SQL Server:


ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

In a nutshell, these settings instruct SQL Server not to lock database rows for read operations, greatly improving concurrency. For a more detailed explanation, consult Microsoft's documentation in SQL Server Developer Center.

Without these settings, the portlet preferences implementation in uPortal 3 will generate deadlocks under load, as multiple processes attempt to lock related sets of rows in SQL Server. When these deadlocks are detected, the database server will choose one or more deadlock victims and abort them. The user will see broken channels in the portal UI, and you'll see stack traces that contain the following in the logs:


[...]
Caused by: org.hibernate.exception.LockAcquisitionException: could not load an entity: [org.jasig.portal.portlet.dao.jpa.PortletDefinitionImpl#15]
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:82)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.loader.Loader.loadEntity(Loader.java:1874)
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:48)
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:42)
    at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3044)
    at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefreshEventListener.java:123)
    at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefreshEventListener.java:39)
    at org.hibernate.impl.SessionImpl.fireRefresh(SessionImpl.java:902)
    at org.hibernate.impl.SessionImpl.refresh(SessionImpl.java:886)
    at org.hibernate.ejb.AbstractEntityManagerImpl.refresh(AbstractEntityManagerImpl.java:263)
    at sun.reflect.GeneratedMethodAccessor180.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:346)
    at $Proxy48.refresh(Unknown Source)
    at sun.reflect.GeneratedMethodAccessor180.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:193)
    at $Proxy38.refresh(Unknown Source)
    at org.jasig.portal.portlet.dao.jpa.JpaPortletEntityDao.createPortletEntity(JpaPortletEntityDao.java:98)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:301)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
    at org.springframework.orm.jpa.JpaInterceptor.invoke(JpaInterceptor.java:96)
    ... 73 more
Caused by: java.sql.SQLException: Transaction (Process ID 131) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
    at net.sourceforge.jtds.jdbc.TdsCore.getNextRow(TdsCore.java:764)
    at net.sourceforge.jtds.jdbc.JtdsResultSet.next(JtdsResultSet.java:593)
    at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:168)
    at org.hibernate.loader.Loader.doQuery(Loader.java:697)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
    at org.hibernate.loader.Loader.loadEntity(Loader.java:1860)
    ... 102 more

As I mentioned above, this issue only occurs "under load." You definitely won't see it with a single user, and you probably won't see it with just two or three users; but it doesn't seem to take much more than that. The issue has been observed even with only a dozen or so users hitting the server. Also take note: it seems that several users who are all logged in with the same portal account will not trigger the problem. This issue can go undetected by load simulators if all the virtual users share the same credentials.

Portlet preferences in uPortal 3 are implemented with Hibernate, which ultimately creates and executes the SQL statements that participate in the deadlocks. There may be a way to either enhance the portlet preferences Java code or adjust the Hibernate configuration to solve this issue without enabling the options described above. If there is, I'm unaware of it.

This problem seems to be an issue with the MS SQL Server database platform only. I have not seen it on Oracle or Postgres, though admittedly uPortal 3 is a new release and my experience with uP3 on these or any RDBMS only goes so far.

I've added a warning about this issue to the uPortal 3 Manual.

Your Blogmaster:

drew's picture

Andrew Wills