AnsweredAssumed Answered

Using SERIALIZABLE transaction isolation with Alfresco

Question asked by matthewpatin on Nov 30, 2015
Latest reply on Dec 4, 2015 by matthewpatin
This is a crosspost from stackoverflow:
http://stackoverflow.com/questions/33962022/using-serializable-transaction-isolation-with-alfresco


<strong>Background</strong>

Alfresco uses the default database transaction isolation, which for our Oracle database is READ_COMMITED. I'm working on a project where non-repeatable and phantom reads could be a major issue, so I'm looking into using SERIALIZABLE transaction isolation to avoid this.

On one hand, we'll have an custom API service which groups changes into atomic transactions - basically CRUD operations on documents. On the other hand, we'll have background processes updating the metadata of this documents running in parallel.

These operations will use Transactional Metadata Queries, as to not further complicate matters by adding eventually consistent SOLR queries into the mix.

The goal is to be able to undertake a major metadata model migration while the API service is running. For the purpose of this question, I'm going to use one attribute as the example, but IRL there will be many changes of this sort. For example, we currently have a metadata field with a constraint: mymodel:doctypes1. But we need to remap the values in mymodel:doctypes1 to a new field with a different constraint: mymodel:doctypes2. (Don't ask me why, I have no control over this decision and I personally question the wisdom of this kind of change).

My understanding of READ_COMMITTED isolation tells me that in this scenario, we are very vulnerable to the following situation:

The background process starts a transaction and reads the value of mymodel:doctypes1.
The API writes a change in mymodel:doctypes1 before the background process commits.
The background process updates the value of mymodel:doctypes2 based on the original value of mymodel:doctypes1.
The two values are now inconsistent: I believe this error is called a non-repeatable read.

<strong>The questions</strong>

Would setting the Oracle database to SERIALIZABLE prevent this issue? Alfresco is using Spring transactions under the hood. My understanding is that a serializable tx isolation with Spring transactions would prevent this issue from happening "transparently".

Does anyone have any real world experience setting an Alfresco database to SERIALIZABLE? Were you trying to solve a similar issue? Did it work? What kind of performance impact did it have for you?

Thanks you very much for sharing your experiences!

Outcomes