I looked in the source code and see
final List<Object> l = qb.toQuery(session).setLockOptions(LockOptions.UPGRADE).list()
however for some reason I still have this issue. Any ideas?
When exactly do you see the issue? Only under high load, with many concurrent users? Or also in a single-threaded environment? What's your Hibernate and MySQL config?
I haven't detected exactly when it happens. We currently have a site that is in development stage and only have about 10 users on the app. We do have multiple clients so it is possible that requests are being done concurrently.
Here's my persistence.xml
<persistence-unit name="pluggedin" transaction-type="RESOURCE_LOCAL">
<property name="hibernate.bytecode.provider" value="javassist"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>
<property name="hibernate.cache.use_second_level_cache" value="true"/>
<property name="hibernate.cache.use_query_cache" value="true"/>
<property name="hibernate.cache.use_minimal_puts" value="false"/>
<property name="hibernate.cache.use_structured_entities" value="true"/>
<property name="hibernate.cache.provider_class" value="net.sf.ehcache.hibernate.SingletonEhCacheProvider"/>
<property name="hibernate.show_sql" value="false"/>
<property name="hibernate.query.substitutions" value="true 1, false 0"/>
<property name="hibernate.jdbc.batch_size" value="15"/>
<property name="hibernate.jdbc.use_streams_for_binary" value="true"/>
<property name="hibernate.max_fetch_depth" value="1"/>
<property name="hibernate.default_batch_fetch_size" value="32"/>
<property name="hibernate.default_entity_mode" value="pojo"/>
<property name="hibernate.generate_statistics" value="true"/>
<property name="hibernate.query.jpaql_strict_compliance" value="false"/>
<property name="hibernate.ejb.event.post-insert" value="org.hibernate.ejb.event.EJB3PostInsertEventListener,org.hibernate.envers.event.AuditEventListener"/>
<property name="hibernate.ejb.event.post-update" value="org.hibernate.ejb.event.EJB3PostUpdateEventListener,org.hibernate.envers.event.AuditEventListener"/>
<property name="hibernate.ejb.event.post-delete" value="org.hibernate.ejb.event.EJB3PostDeleteEventListener,org.hibernate.envers.event.AuditEventListener"/>
<property name="hibernate.ejb.event.pre-collection-update" value="org.hibernate.envers.event.AuditEventListener"/>
<property name="hibernate.ejb.event.pre-collection-remove" value="org.hibernate.envers.event.AuditEventListener"/>
<property name="hibernate.ejb.event.post-collection-recreate" value="org.hibernate.envers.event.AuditEventListener"/>
<property name="org.hibernate.envers.audit_strategy" value="org.hibernate.envers.strategy.ValidityAuditStrategy"/>
<property name="org.hibernate.envers.audit_table_suffix" value="_Audit"/>
<property name="org.hibernate.envers.revision_field_name" value="revision"/>
<property name="org.hibernate.envers.revision_on_collection_change" value="false"/>
<property name="org.hibernate.envers.revision_type_field_name" value="revisionType"/>
<property name="org.hibernate.envers.audit_strategy_validity_end_rev_field_name" value="revisionEnd"/>
<property name="org.hibernate.envers.store_data_at_delete" value="true"/>
Hmm well with 10 users I think it may be hard to have two requests at exactly the same time modifying the same data. But still possible.
Anyway, without some means to reproduce the bug, I'm afraid I won't be of much help.
We use Envers 3.6.10.Final on MS SQL Server with snapshot isolation turned on. We have a highly concurrent system with multiple reads and edits going on, but on separate rows. We were getting deadlocks and we did some investigation.
The changes introduced in comment 9 of this thread result in 'with (updlock, rowlock)' appearing in SQL select statements issued by hibernate through the dialect, causing deadlocks in MS SQL Server. Therefore we have created an alternative audit strategy without this change. It appears to stop the 'with ' statements from being issued. This removes the locks and allows our system to run at full concurrency with no deadlocks.
Updating line 77 in ValidityEntityStrategy was not entirely clean. I had to extend it with method copies due to some private variables and methods. Also, implementing AuditStrategy directly did not work, due to code in AuditMetadataGenerator... 'instanceof ValidityAuditStrategy'.
I thought this was interesting and worth posting, since the change appears to have been driven by a different concurrency requirement.
Would you like a JIRA for this?
Well, the rows need to be fixed so that the audit data is properly written. You need a write lock on the row with the previous revision to set the end rev properly. Otherwise you may get exceptions like the original one.
"We have a highly concurrent system with multiple reads and edits going on, but on separate rows."
This is the key point - I am synchronising at the java level.
Locking the database row causes deadlocks when a table scan hits it. Our DBAs have advised me to ensure everything is indexed propery to prevent table scans; an near-impossible goal. We have a HyperJAXB3 generated JPA model, resulting in over 200 tables. Hibernate will not (I believe) create all required indexes for me. We get about 90% indexed via automated scripts, but the last 10% would be very hard. Also, the risk of a change in model and forgotten index with resulting deadlock is high.
All that risk and complexity is eliminated by simply removing the locking as I have done. A neat and elegant solution with no obvious downside. Intensive testing has confirmed this. Works for us.
Ok, we could introduce a flag for that then, with a very clear warning in the manual If you'd have time to submit a patch maybe, it would be great.
Btw. surprising though that app-level locking works better for you than db-level locking. The DB-locks should be row-level as well, not?