Confluence: Lock wait timeout exceeded; try restarting transaction

WARNING! Atlassian themselves recommend STRONGLY against this procedure. If any action, take the action that shows you which table is locking, DO NOT DELETE anything unless you are 100% confident you can reverse your deletions. DO NOT DELETE, DO NOT DELETE!

Seeing this?

2013-05-14 16:39:55,581 ERROR [QuartzScheduler_Worker-1] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Lock wait timeout exceeded; try restarting transaction
2013-05-14 16:39:55,581 ERROR [QuartzScheduler_Worker-1] [sf.hibernate.impl.SessionImpl] execute Could not synchronize database state with session


The first is actually reported from MySQL itself, the second from Hibernate, which wraps databases for Java apps.

If you are desperate, try deleting all rows from mysql's crowd.cwd_membership table after backing it up, worked for me, syncs started working again in under 16ms.

1. mysqldump crowd | bzip2 -c > /mnt/dump_crowd_`date +%Y%m%d`.sql.bz2
2. mysql crowd -e 'delete from cwd_membership'

If that doesn't help, try deleting old users from any confluence groups that are still in your LDAP dir, be brutal. While your at it, delete old users period from LDAP.

To spot the problem table, this might help, if another table is your problem:

1. watch "mysql -e 'show processlist'"
2. Then, run LDAP sync update via admin web GUI, and watch to see which table is locking

Other things you might be seeing in your logs if you have this issue:

"batch failed falling back to individual processing java.lang.RuntimeException: could not flush session"

"Error occurred while refreshing the cache for directory"
"synchroniseCache full synchronisation for directory [ XXXX ] starting"
"could not insert: [com.atlassian.crowd.embedded.hibernate2.HibernateMembership#YYYYY]"
"Lock wait timeout exceeded; try restarting transaction"
"Could not synchronize database state with session"
"could not flush session"