DocumentDB – Optimistic Concurrency in a Stored Procedure

Got a message from one of my blog visitors that were related to two of my previous blog posts (DocumentDB revisited Part 3 – Concurrency in DocumentDB and  DocumentDB revisited Part 4 – Stored Procedures) that I wrote when I revisited DocumentDB some time ago. The problem was that he (hi Mark) had tried to get the optimistic concurrency to work from within a stored procedure (JavaScript) but had tried some trouble getting that to work the same way as in the c# example. As Mark noted I had not presented any solution if you wanted to update documents also from a stored procedure with concurrency checks. I believe this fell a bit between the two posts as I discussed concurrency in part 3 and stored procedures in part 4. So to bridge these I will present the solution for stores procedures and optimistic concurrency below.

Default behavior of DocumentDB SPs

By default you will not get any warnings when a DocumentDB stored procedure wants to update a document that someone else have updated between the load of the document to the time when it is saved. Meaning that effectively the last write wins (overwrites any intermediate saves). This behavior is discussed in detail in DocumentDB revisited Part 3 – Concurrency in DocumentDB and I showed some techniques how to avoid that from c# code.

You need to consider the consequences before you turn on the concurrency check in the Stored Procedure. The reason for this is that the Stored Procedures are atomic – meaning that it is transactional (implicitly). If the function throws an error then the transaction is rolled back, if not – it is committed. So if you get an exception about concurrency (i.e. “Precondition failed”) and throw an error – all other changes will effectively be rolled back**.

** I will show you ways of dealing with this for alternate solutions later where you want to keep as much of the changes as possible.

But you need to consider this before you implements your SPs as it may affect  the choice of whether to turn it on or not also.

Also consider the likelihood that someone/something else would be updating the same document during the timespan of a “quick read-update value-save” operation will be very slim (depending a bit on the application and its implementation) so this might also speak for not turning on optimistic concurrency on your SP.

You also need to consider that (if you use a partitioned DocumentDB instance) the transaction must occur within a single partition.

function SingleUpdateNoConcur() {
var collection = getContext().getCollection();
var query = {query: “SELECT * FROM Orders where Orders.id = ‘webShop635874294383904028′”};
var isAccepted1 = collection.queryDocuments(
collection.getSelfLink(),
query,
function (err, documents, options) {
if (err) throw err;
var doc1;
if (documents.length > 0)
doc1 = documents[0];
else
throw “Not found order 1”;

doc1.customer.firstName = ‘Totti’;

//Start Update
var accept2 = collection.replaceDocument(doc1._self, doc1,
function (err, docReplaced) {
if (err) throw “Unable to update order 1, abort ” + err + doc1._etag;
});

if (!accept2) throw “Unable to update order 1, abort”;

});

}

The above is an example of a SP not handling optimistic concurrency. It opens a document and changes the firstname of the customer (very simplified and hardcoded)

The solution – , { etag: doc._etag}

Turn on Optimistic Concurrency in your DocumentDB SP is very simple. The code change is equivalent of the AccessConditionType.IfMatch from the C# code described in the post regarding optimistic concurrency.

function SingleUpdateWithOptConcur() {
var collection = getContext().getCollection();
var query = {query: “SELECT * FROM Orders where Orders.id = ‘webShop635874294383904028′”};
var isAccepted1 = collection.queryDocuments(
collection.getSelfLink(),
query,
function (err, documents, options) {
if (err) throw err;
var doc1;
if (documents.length > 0)
doc1 = documents[0];
else
throw “Not found order 1”;

doc1.customer.firstName = ‘Montella’;

//Start Update
var accept2 = collection.replaceDocument(doc1._self, doc1, {etag: doc1._etag},
function (err, docReplaced) {
if (err) throw “Unable to update order 1, abort ” + err + doc1._etag;
});

if (!accept2) throw “Unable to update order 1, abort”;

});

}

The example above just adds a {etag: doc1._etag} option in the replaceDocument JavaScript which really is the one change that you need to do. Just remember that the second part must be from the document that you are actually updating, in this case doc1.

So prove that it works…

The above SP will work, but as did the first one without concurrency checks. The problem with optimistic concurrency is of course to make it occur which it will hardly do in a well designed system. I will therefor create a special SP that will open two copies of the same document and will try to update them one after the other. The second update should fail as the etag will change after the first save.

 

function OptimisticConcurrencySP() {

var collection = getContext().getCollection();

var query = {query: “SELECT * FROM Orders where Orders.id = ‘webShop635874294383904028′”};

//get first doc ref

var isAccepted1 = collection.queryDocuments(
collection.getSelfLink(),
query,
function (err, documents, options) {
if (err) throw err;
var doc1;

if (documents.length > 0) {
doc1 = documents[0];}
else
throw “Order 1 not found”;

if (doc1 == null) throw “Not found order 1”;

//get second doc ref

var isAccepted2 = collection.queryDocuments(
collection.getSelfLink(),
query,
function (err, documents, options) {
if (err) throw err;
var doc2;

if (documents.length > 0) {
doc2 = documents[0];}
else
throw “Order 2 not found”;

//Change same property

doc1.customer.firstName = ‘First’;
doc2.customer.firstName = ‘Second’;

//Start Update 1
var accept3 = collection.replaceDocument(doc1._self, doc1, { etag: doc1._etag},
function (err, docReplaced) {
if (err) throw “Unable to update order 1, abort ” + err + doc1._etag;
//Start Update 2
var accept4 = collection.replaceDocument(doc2._self, doc2, { etag: doc2._etag},
function (err, docReplaced) {
if (err) throw “Unable to update order 2, abort ” + err + doc2._etag;

})
if (!accept4) throw “Unable to update order 2, abort”;

//end update 2

})
if (!accept3) throw “Unable to update order 1, abort”;

//end update 1

}

);
//end

}

);

}

So what happens if I run this in the portal…

Optimistic Concurrency Error on save 2nd time
Optimistic Concurrency Error on save 2nd time

So we have proven in this sample that the etag is a precondition for the save to be successful save. The sample itself is not very useful as you would not have an SP designed this way. If you need to update the same record you would not load the second document until you have saved it the first time and would therefor use the new etag in the second save.

IMPORTANT: By throwing the error you will now roll back the first save also so no change will be saved.

What if you don’t want to rollback but just exit

For this scenario – lets imagine that the second save is a bonus. If it works then fine lets save it but we don’t want to loose the first update OR overwrite any other persons updates (strange scenario but still…). So basically if we get an optimistic concurrency error I want to just ignore it BUT if I get any other error I want to roll back everything.

To do this first we need to understand the err object (the below is taken from http://azure.github.io/azure-documentdb-js-server/Collection.html#.RequestCallback)

error Object Will contain error information if an error occurs, undefined otherwise.

Properties
Name Type Description
number ErrorCodes The HTTP response code corresponding to the error.
body string A string containing the error information.

The key here is to understand the ErrorCodes. After all we don’t want to ignore all problems we just want to handle the situation where someone else have updated our record.

The possible ErrorCodes are:

Name Type Description
BadRequest number (400) Request failed due to bad inputs
Forbidden number (403) Request was denied access to the resource
NotFound number (404) Request tried to access a resource which doesn’t exist
Conflict number (409) Resource with the specified id already exists
PreconditionFailed number (412) Conditions specified in the request options were not met
RequestEntityTooLarge number (413) Request failed because it was too large
RetryWith number (449) Request conflicted with the current state of a resource and must be retried from a new transaction from the client side
InternalServerError number (500) Server encountered an unexpected error in processing the request

And as you saw in the error earlier from the script explorer and in my other related post on optimistic concurrency – the ErrorCode we want to ignore is the PreConditionFailed error.

Knowing this and that If I raise the error everything is rolled back… I modified the function like below.

function OptimisticSaveFirstOrder() {

var collection = getContext().getCollection();

var query = {query: “SELECT * FROM Orders where Orders.id = ‘webShop635874294383904028′”};

const PreconditionFailed = 412;

var isAccepted1 = collection.queryDocuments(
collection.getSelfLink(),
query,
function (err, documents, options) {
if (err) throw err;
var doc1;

if (documents.length > 0) {
doc1 = documents[0];}
else
throw “Order 1 not found”;

if (doc1 == null) throw “Not found order 1”;

//Start

var isAccepted2 = collection.queryDocuments(
collection.getSelfLink(),
query,
function (err, documents, options) {
if (err) throw err;
var doc2;

if (documents.length > 0) {
doc2 = documents[0];}
else
throw “Order 2 not found”;

doc1.customer.firstName = ‘First Committed’;
doc2.customer.firstName = ‘Second fails’;

//Start Update 1
var accept3 = collection.replaceDocument(doc1._self, doc1, { etag: doc1._etag},
function (err, docReplaced) {
if (err) throw “Unable to update order 1, abort ” + err + doc1._etag;
//Start Update 2
var accept4 = collection.replaceDocument(doc2._self, doc2, { etag: doc2._etag},
function (err, docReplaced) {
if (err)
{
if (err.number != PreconditionFailed) //Constant defined earlier (see blue)
throw err;

}

})
if (!accept4) throw “Unable to update order 2, abort”;

//end update 2

})
if (!accept3) throw “Unable to update order 1, abort”;

//end update 1

}

);
//end

}

);

}

So what will happen in the above example.

  1. We will change the name on the order to ‘First Committed’ and successfully update it
  2. We will change the name in the second version of the document to ‘Second fails’. We then get an optimistic concurrency error BUT I will check the errorcode if (err.number != PreconditionFailed) and will only throw an error if it is NOT a Precondition Failed error.

Result: First save is persisted in the database.

Important: Though I may have solved the scenario described above…I have no clue that there was a concurrency issue. If I throw an error the first update is rolled back. So you’d better know what you are doing in this case.

Recover from Optimistic Concurrency

The next example is nicer. In this I want to update a customers first name. I will then force a concurreny error by harcoding a faulty etag. In your SP you will use the real etag. I will recover from etag error 412 PreconditionFailed error by reloading the document, setting the name on the updated document and then saving it – effectively allowing me not to overwrite any changed data (except for the name of course).  Below follows an example that handles this.

 

// Update Name for customer

function RecoverFrom412(newName) {
var collection = getContext().getCollection();
 var query = {query: “SELECT * FROM Orders where Orders.id = ‘webShop635874294383904028′”};
const PreconditionFailed = 412;
var isAccepted1 = collection.queryDocuments(
collection.getSelfLink(),
query,
function (err, documents, options) {
if (err) throw err;
var doc1;
    if (documents.length > 0) {
doc1 = documents[0];}
else
throw “Order 1 not found”;

               if (doc1 == null) throw “Not found order 1”;

   //Set new name
doc1.customer.firstName = newName;

//Start Update on doc 1 – faulty etag – should have been doc1._etag
var acceptReplaceOuter = collection.replaceDocument(doc1._self, doc1, { etag: “faultytag”},
function (err, docReplaced) {
if (err)
{
if (err.Number = PreconditionFailed)
{
//If precondition failed reload logic
//Try to reopen and save again
var isAcceptedReopen = collection.queryDocuments(
collection.getSelfLink(),
query,
function (err, documents, options) {
if (err) throw err;
var doc1Refreshed;
                                                if (documents.length > 0)
doc1Refreshed = documents[0];
else
throw err;
                                                doc1Refreshed.customer.firstName = newName;   //Set name on refreshed doc
//try save again start – this time throw all errors   
var acceptInner = collection.replaceDocument(doc1Refreshed._self, doc1Refreshed, { etag: doc1Refreshed._etag},
function (err, docReplaced) {
if (err)
throw err;
});    //end try Save againif (!acceptInner)   //Failed to save reloaded doc
throw err;} //end try second time);//End precondition failed reload logic
                                if (!isAcceptedReopen)
throw “Failed to reopen doc”;
                                }
else
throw err; //Other error  – outer throw
}
}); //Reopen docif (!acceptReplaceOuter) throw “Unable to update order 1, abort”; //after update 1 – generates 412

    }

);  //End first open  

        if (!isAccepted1) throw “Could not open document 1st time”
}

Not perfectly formatted and designed JavaScript perhaps and some inconsistent error handling but the point is more showing how you can recover from Optimistic Concurrency in your SP. I will end this post by discussing returning errors which will suggest ideas on how to handle errors in your code.

What about the error/return codes….

As Mark had noted was that he got different error codes from the C# example and the JavaScript SP raised errors.

Let’s first verify Marks claims…

If you recall the entry from the post DocumentDB revisited Part 3 – Concurrency in DocumentDB I had a method defined like this when updating with ordinary c# code.

 public static async Task<Document> UpdateDocumentOptimisticAsync(Document item)
{
var ac = new AccessCondition { Condition = item.ETag, Type = AccessConditionType.IfMatch };
return await client.ReplaceDocumentAsync(item, new RequestOptions { AccessCondition = ac });
}

 

and we caught the error like this

Optimistic Concurrency in C# code
Optimistic Concurrency in C# code

 

So now lets try again with a SP that generates a Optimistic Concurrency Error.

Optimistic Concurrency calling JS SP
Optimistic Concurrency calling JS SP

 

We can then see that the JavaScript generates a BadRequest(400) response instead of PreconditionFailed (412). So Mark was right. There was a discrepancy in the return codes. The reason is likely that the SP uses a JavaScript throw statement and we cannot specify to throw a specific status code like PreconditionFailed (a shortcoming of the untyped javaScript language). The native C# SDK however can and can return a more suitable error code. For the JavaScript errors we use the same throw statement for all errors.

However….all is not lost because we can still see the inner exception in the code

The message from SP
The message from SP

Had DocumentDB had been a bit nicer it could have converted the error in this example to 412 Preconition failed. But as it is right now we are stuck with this message and it would be simple to write some ugly code like below to handle all OptimisticConcurrency errors the same way ( for both C# AccessCondition errors  and SP etag error)

catch (DocumentClientException exDB)
{if (exDB.StatusCode == System.Net.HttpStatusCode.PreconditionFailed ||
(exDB.StatusCode  == System.Net.HttpStatusCode.BadRequest && exDB.Message.Contains(“One of the specified pre-condition is not met”))
)
{
//is concurrency exception – take action
System.Diagnostics.Debug.WriteLine(“Optimistic Concurrency error.”);
}
                else
System.Diagnostics.Debug.WriteLine(“Other exception” + exDB.Message);
}

The worst part of this solution is of course that we are depeding on a string constant. What if the error text is replaced or spelled differently – then we will not classify it as a optimistic concurrency error anymore…..

Using Customized Errors to avoid hard coded string comparison

So I would like to do something nicer and more resilient perhaps and I intend to use the details from the errorobject described in section What if you don’t want to rollback but just exit earlier. I know in the SP JavaScript code when I have a PreconditionFailed so I will try to return a more custom error message and I will allow you to return additional  JSON depending on the error and still be able to parse it. Sounds good, right?

First a new SP

function ThrowCustom412() {
var collection = getContext().getCollection();

var query = {query: “SELECT * FROM Orders where Orders.id = ‘webShop635874294383904028′”};
const PreconditionFailed = 412;
const NotFound = 404;
const Forbidden = 403;

var isAccepted1 = collection.queryDocuments(
collection.getSelfLink(),
query,
function (err, documents, options) {
if (err)
throw “{‘StatusCode’:” + err.number + “,’CustomMessage’:’Error opening order’}”;

var doc1;

    if (documents.length > 0) {
doc1 = documents[0];}

if (doc1 == null)
throw “{‘StatusCode’:” + NotFound + “,’CustomMessage’:’Couldn’t find Order’}”;

//Set new name
doc1.customer.firstName = “This will fail anyway”;

//Start Update
var acceptReplace = collection.replaceDocument(doc1._self, doc1, { etag: “faultytag”},
function (err, docReplaced) {
if (err)
{
if (err.number == PreconditionFailed)
throw “{‘StatusCode’:” + err.number + “,’CustomMessage’:’ConcurrencyError’}”;
else
throw “{‘StatusCode’:” + err.number + “,’CustomMessage’:’Some other error’}”;                }
});

if (!acceptReplace)
throw “{‘StatusCode’:” + Forbidden + “,’CustomMessage’:’Unable to update order, abort’}”;
}

);

if (!isAccepted1)
throw “{‘StatusCode’:” + Forbidden + “,’CustomMessage’:’Could not open document, abort’}”;

}

Notice that I don’t throw the Err object directly anymore. Instead I throw a valid JSON string throw  “{‘StatusCode’:” + err.number + “,’CustomMessage’:’Error opening order’}”;  which will resemble the following string {‘StatusCode’:412, ‘CustomMessage’:’Error opening order’} .  The method will fake a conscurrency error by setting a faulty etag.

So what will happen in our client?

Message: {“Errors”:[“Encountered exception while executing function. Exception = {‘StatusCode’:412,’CustomMessage’:’ConcurrencyError’}”]}
ActivityId: 738f784f-0ee0-4482-95fc-bf7448991bf3, Request URI: /apps/e6b37ff8-8342-41ad-adc6-48e69c567355/services/4bbd87e0-279c-4bce-935d-b6e8d80c169b/partitions/f865ced3-973f-4809-87c0-13fcf0a3f07c/replicas/131254989406094374p

Well you can see that my JSON is there. So now we will just have to extract it. The nice thing about the method I created is that you could return many more values for specific errors, such as etag, order number etc. You can just add it in the JSON on the server side and write logic to consume the additional columns if you want to.

So first I need to parse the JSON. I use a very simplified RegEx expression and try to extract the JSON part. I will then Deserialize the JSON as dynamic to be able to use it flexible with custom properties. If there is no match I will return a custom return object to not generate errors later.

Here is the function. Note that it does not support JSON hierarchies.

///<summary>
/// Get the custom JSON part of the message
///</summary>
///<param name=”exceptionMessage”>The message to be parsed for the Custom JSON</param>
///<returns>The dynamic JSON part</returns>
private dynamic ParseSplitException(string exceptionMessage)
{
Match match = Regex.Match(exceptionMessage, @”(Exception = )({[\’|\””|\:|\,|\s|a-z|A-Z|\d\’]*})”, RegexOptions.IgnoreCase);// Here we check the Match instance.
if (match.Success)
return (dynamic)JsonConvert.DeserializeObject(match.Groups[2].Value);
else
return (dynamic)JsonConvert.DeserializeObject(“{‘StatusCode:400, ‘CustomMessage’= ‘Incompatible JSON’}”);}

Now we can add the check for StatusCode 412 also for bad requests and treat them the same way as our other concurrency errors from the SDK.

try

{

var res2 = await DocumentDBRepository<Order>.ExecuteSPByName<string>(“ThrowCustom412”);

}

catch (DocumentClientException exDB)

{

if (exDB.StatusCode == System.Net.HttpStatusCode.PreconditionFailed || (exDB.StatusCode == System.Net.HttpStatusCode.BadRequest && (System.Net.HttpStatusCode)ParseSplitException(exDB.Error.Message).StatusCode == System.Net.HttpStatusCode.PreconditionFailed))

{

//is concurrency exception – take action

System.Diagnostics.Debug.WriteLine($”Optimistic Concurrency error. Message from SP=’{(string)ParseSplitException(exDB.Error.Message).CustomMessage}‘”);

}

else

System.Diagnostics.Debug.WriteLine(“Other exception” + exDB.Message);

}

So with this I check if it is either a StatusCode PreconcitidonFailed or if the StatusCode is Badrequest and the custom StatusCode is PreconditionFailed. Note also that the Debug write accesses the CustomMessage that I sent.

This is of course but one way to do it. Only imagination stops you from doing your own solution for this – but at least give me some credit and agree that this is nicer (propagates original error to check for precondition failed and allows for custom properties to be read as well as). The string comparison is likely somewhat faster but it is less flexible and a error string change would break the functionality.

Good luck with your own choice in concurrency selection. I hope that this have given you some thoughts that you can reuse in your solution,

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s