I've spent the last week grappling with what I have reported as http://issues.alfresco.com/browse/ALFCOM-1683
Basically, when 2 clients access the same versioned resources in the Alfresco repository (via Web service + JCR API in my case), the database deadlocks. I'm using 2.9 community sources from subversion, and have been experiencing this and related issues for over 4 months.
I was using MySQL, but this week I've also tried Postgres and HSQL. I get problems whichever one I use.
I have attached a simple test case to that bug report, which simulates concurrent access by creating a number of threads which manipulate the content. Things fail badly if several threads access at once. I say badly, because (i) RetryingTransactionHelper doesn't actually retry in this case, and (ii) you have to restart the database before you can get reproducible behaviour.
In the case of HSQL:
Could not synchronize database state with session org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction
A 2 second delay between threads avoids this.
However, when I run it under MySQL although the initial thread which creates the content makes a node versionable, to my surprise, each subsequent thread
javax.jcr.UnsupportedRepositoryOperationException: Node workspace://SpacesStore/667bf743-2e29-11dd-93d2-0108c9249316 is not versionable
I'd be very grateful for thoughts/suggestions how to work around these issues.