Hi all,

I am trying to take advantage of JBoss 3.2's optimistic locking technology (in particular, it's version and timestamp strategies.) However, when I use the strategy to insert a large number of CMR-based records, the version numbers become out of synch until eventually, JBoss issues me the following error:

javax.ejb.EJBException: Update failed. Expected one affected row: rowsAffected=0id=90
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreEntityCommand.execute(JDBCStoreEntityCommand.java:140)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.storeEntity(JDBCStoreManager.java:645)
at org.jboss.ejb.plugins.CMPPersistenceManager.storeEntity(CMPPersistenceManager.java:462)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.storeEntity(CachedConnectionInterceptor.java:388)
at org.jboss.ejb.EntityContainer.storeEntity(EntityContainer.java:700)
at org.jboss.ejb.GlobalTxEntityMap.syncEntities(GlobalTxEntityMap.java:159)
at org.jboss.ejb.EntityContainer.synchronizeEntitiesWithinTransaction(EntityContainer.java:165)
at org.jboss.ejb.EntityContainer.findLocal(EntityContainer.java:595)
at sun.reflect.GeneratedMethodAccessor103.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.ejb.EntityContainer$ContainerInterceptor.invokeHome(EntityContainer.java:1051)
at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:73)
at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome(EntitySynchronizationInterceptor.java:207)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invokeHome(CachedConnectionInterceptor.java:215)
at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:73)
at org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome(EntityInstanceInterceptor.java:90)
at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockInterceptor.java:79)

etc.

I looked at JBoss source code of JDBCStoreEntityCommand.java and found that this error is thrown if no database records were modified during an SQL update.

Investigating further, I grepped my database logfile for the list of queries executed during the transaction and came up with the following list:

query: begin;
query: SELECT t0_sa.actId FROM action t0_sa
query: SELECT t0_sr.roleId FROM role t0_sr
query: SELECT name FROM sequence WHERE name='ActionAssignment'
query: SELECT COUNT(*) FROM sequence WHERE name='ActionAssignment'
query: INSERT INTO sequence (name, index) VALUES ('ActionAssignment', 0)
query: SELECT COUNT(*) FROM actionassignment WHERE actAssId=10
query: INSERT INTO actionassignment (actAssId, is_assigned, action_id, entry_id, role_id, versionCount) VALUES (10, 'false', null, null, null, 1)
query: SELECT actId,versionCount FROM action WHERE (actId=10) OR (actId=20) OR (actId=30) OR (actId=40) OR (actId=50) OR (actId=60) OR (actId=70) OR (actId=80) OR (actId=90) OR (actId=100) OR (actId=110) OR (actId=120) OR (actId=130) OR (actId=140) OR (actId=150) OR (actId=160) OR (actId=170) OR (actId=180) OR (actId=190) OR (actId=200) OR (actId=210) OR (actId=220) OR (actId=230) OR (actId=240) OR (actId=250)
query: SELECT roleId,versionCount FROM role WHERE (roleId=10) OR (roleId=20) OR (roleId=30) OR (roleId=40) OR (roleId=50) OR (roleId=60) OR (roleId=70) OR (roleId=80) OR (roleId=90)
query: UPDATE sequence SET index=10 WHERE name='ActionAssignment'
query: UPDATE actionassignment SET action_id=10, role_id=10, versionCount=2 WHERE actAssId=10 AND versionCount=1
query: SELECT name FROM sequence WHERE name='ActionAssignment'
query: SELECT COUNT(*) FROM actionassignment WHERE actAssId=20
query: INSERT INTO actionassignment (actAssId, is_assigned, action_id, entry_id, role_id, versionCount) VALUES (20, 'false', null, null, null, 1)
query: UPDATE sequence SET index=20 WHERE name='ActionAssignment'
query: UPDATE actionassignment SET action_id=10, role_id=20, versionCount=2 WHERE actAssId=20 AND versionCount=1
query: SELECT name FROM sequence WHERE name='ActionAssignment'
query: SELECT COUNT(*) FROM actionassignment WHERE actAssId=30
query: INSERT INTO actionassignment (actAssId, is_assigned, action_id, entry_id, role_id, versionCount) VALUES (30, 'false', null, null, null, 1)
query: UPDATE sequence SET index=30 WHERE name='ActionAssignment'
query: UPDATE actionassignment SET action_id=10, role_id=30, versionCount=2 WHERE actAssId=30 AND versionCount=1
query: SELECT name FROM sequence WHERE name='ActionAssignment'
query: SELECT COUNT(*) FROM actionassignment WHERE actAssId=40
query: INSERT INTO actionassignment (actAssId, is_assigned, action_id, entry_id, role_id, versionCount) VALUES (40, 'false', null, null, null, 1)
query: UPDATE sequence SET index=40 WHERE name='ActionAssignment'
query: UPDATE actionassignment SET action_id=10, role_id=40, versionCount=2 WHERE actAssId=40 AND versionCount=1
query: SELECT name FROM sequence WHERE name='ActionAssignment'
query: SELECT COUNT(*) FROM actionassignment WHERE actAssId=50
query: INSERT INTO actionassignment (actAssId, is_assigned, action_id, entry_id, role_id, versionCount) VALUES (50, 'false', null, null, null, 1)
query: UPDATE sequence SET index=50 WHERE name='ActionAssignment'
query: UPDATE actionassignment SET action_id=10, role_id=50, versionCount=2 WHERE actAssId=50 AND versionCount=1
query: SELECT name FROM sequence WHERE name='ActionAssignment'
query: SELECT COUNT(*) FROM actionassignment WHERE actAssId=60
query: INSERT INTO actionassignment (actAssId, is_assigned, action_id, entry_id, role_id, versionCount) VALUES (60, 'false', null, null, null, 1)
query: UPDATE sequence SET index=60 WHERE name='ActionAssignment'
query: UPDATE actionassignment SET action_id=10, role_id=60, versionCount=2 WHERE actAssId=60 AND versionCount=1
query: SELECT name FROM sequence WHERE name='ActionAssignment'
query: SELECT COUNT(*) FROM actionassignment WHERE actAssId=70
query: INSERT INTO actionassignment (actAssId, is_assigned, action_id, entry_id, role_id, versionCount) VALUES (70, 'false', null, null, null, 1)
query: UPDATE sequence SET index=70 WHERE name='ActionAssignment'
query: UPDATE actionassignment SET action_id=10, role_id=70, versionCount=2 WHERE actAssId=70 AND versionCount=1
query: SELECT name FROM sequence WHERE name='ActionAssignment'
query: SELECT COUNT(*) FROM actionassignment WHERE actAssId=80
query: INSERT INTO actionassignment (actAssId, is_assigned, action_id, entry_id, role_id, versionCount) VALUES (80, 'false', null, null, null, 1)
query: UPDATE sequence SET index=80 WHERE name='ActionAssignment'
query: UPDATE actionassignment SET action_id=10, role_id=80, versionCount=2 WHERE actAssId=80 AND versionCount=1
query: SELECT name FROM sequence WHERE name='ActionAssignment'
query: SELECT COUNT(*) FROM actionassignment WHERE actAssId=90
query: INSERT INTO actionassignment (actAssId, is_assigned, action_id, entry_id, role_id, versionCount) VALUES (90, 'false', null, null, null, 1)
query: UPDATE sequence SET index=90 WHERE name='ActionAssignment'
query: UPDATE actionassignment SET action_id=10, role_id=90, versionCount=2 WHERE actAssId=90 AND versionCount=1
query: SELECT name FROM sequence WHERE name='PolicyEntry'
query: SELECT COUNT(*) FROM sequence WHERE name='PolicyEntry'
query: INSERT INTO sequence (name, index) VALUES ('PolicyEntry', 0)
query: SELECT COUNT(*) FROM policyentry WHERE polEntId=10
query: INSERT INTO policyentry (polEntId, action_id, policy_id, versionCount) VALUES (10, null, null, 1)
query: UPDATE sequence SET index=10 WHERE name='PolicyEntry'
query: UPDATE policyentry SET action_id=10, versionCount=2 WHERE polEntId=10 AND versionCount=1
query: UPDATE actionassignment SET entry_id=10, versionCount=3 WHERE actAssId=90 AND versionCount=1

The last line is causing the exception. It's performing an update with the assumption that the value of the CMP version count for that record is currently 1. It is not, however, as around 10 lines from the bottom it was set to 2 for the same record. JBoss, methinks, is not synchronizing its version counters properly.

Does anyone have similar problems or come up with a work-around?

Darryl Staflund