Nested Transactions with MySQL Trigger 'Lock Wait Timeout' Errors

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

After upgrading to Confluence 3.5 or later when using MySQL, the following appears in the atlassian-confluence.log:

Caused by: net.sf.hibernate.exception.LockAcquisitionException: could not insert: [com.atlassian.crowd.embedded.hibernate2.HibernateMembership#xxxxxxx]
	at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:76)
	at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
	at net.sf.hibernate.persister.AbstractEntityPersister.convert(AbstractEntityPersister.java:1331)
	at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:472)
	at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:436)
	at net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.java:37)
	at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2447)
	at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2433)
	at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2390)
	at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2259)
	at com.atlassian.crowd.embedded.hibernate2.batch.Hibernate2BatchProcessor.flushSession(Hibernate2BatchProcessor.java:165)
	... 31 more
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

Cause

Confluence has locked data for writes in an outer transaction, and is attempting to lock it again to write to it from within an inner transaction that is used to process a batch. MySQL detects this and throws an error about a timeout. This can be caused by:

  1. Modifying a user property in an external user management system,
  2. Modifying the same user's group membership in the same system, and
  3. Confluence synchronising with that system

Alternative causes include:

  1. Deleting a group in an external user management system,
  2. Modifying the group membership of a user or group who used to be a member of the deleted group in the same system, and
  3. Confluence synchronising with that system

The deadlock will not occur if Confluence synchonises with the system between steps 1 and 2.

Resolution

This issue is resolved in Confluence 3.5.4. Affected customers should upgrade to at least this version. Due to the number of classes affected by the fix, no patch will be provided for this issue. Any further developments with this issue can be tracked on http://jira.atlassian.com/browse/CONF-22410.

Workaround

Possible workarounds for this issue include (in order of ease of implementation):

  1. Reduce the delay between synchronisation attempts, so that changes that affect the same user or group are less likely to be discovered in the same synchronisation attempt. In Confluence 3.5.3, there is a UI field for editing the synchronisation delay. In Confluence 3.5 - 3.5.2, customers must edit the database to modify this delay.
  2. Manually edit the database to remove the conflicting rows. To use this workaround, customers must turn on debug logging for the synchronisation related classes, watch the logs for failures, edit the database, flush the caches and trigger another synchronisation.
  3. Disable synchronisation by increasing the synchronisation delay to an arbitrarily large amount.
  4. Stop using external group management. Switch the directory type to LDAP Read Only with Local Groups, feed Confluence a dummy group name filter value, create local groups for all users as necessary, and trigger another synchronisation.

Each of these should be considered a temporary workaround, until you can install a new version of Confluence.

Last modified on Mar 30, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.