0

I have a Customer container with items representing a single customer in SQL API (DocumentDB) in CosmosDB. I also have a Gremlin API (GraphDB) with the customers' shoppingcart data. Both these data are temporary/transient. The customer can choose clear shopping cart which will delete the temporary customer and the shoppingcart data.

Currently I make separate calls, one to the SQL API (DocumentDB) and Gremlin API (GraphDB) which works but I want to do both as a transaction (ACID principle). To delete a customer, I call the Gremblin API and delete the shoppingcart data, then call the SQL API to delete the customer. But if deleting the customer with the SQL API (second step) fails, I want to roll back the changes done in the first call which will roll back the shoppingcart data which were deleted. In the T-SQL world, this is done with a commit and rollback.

How can I achieve distributed transaction coordination around the delete operations of the customer and shoppingcart data?

wonderful world
  • 10,969
  • 20
  • 97
  • 194

1 Answers1

1

Since you don't have transactions in Cosmos DB across different collections (only within the partition of one container), this won't be directly possible.

Next best thing could be to use the Change Feed. It gets triggered whenever an item gets changed or inserted. But: It does not get triggered on deletes. So you need another little workaround of "soft deletes". Bascially you create a flag to that document ("to-be-deleted" etc.) and set its TTL to something very soon. This does trigger then change feed and you can from there delete the item in the other collection.

Is all that better than what you currently have? Honestly, not really if you ask me.

//Update: To add to the point regarding commit/rollback: This also does not exist in Cosmos DB. One possible workaround for this that comes to mind:

  1. Update elements in collection shopping cart. Set a flag to-be-deleted to true and set the TTL for those elements to something like now() + 5 minutes
  2. Delete the element in customer collection. If this works, all good.
  3. If deletion failed, update the shoppingcart again. Remove the to-be-deleted flag and remove the TTL so Comsos DB won't automatically delete it.

Of course, you also need to update any queries you run against your shoppingcart to exclude any elements with the deletion flag in place.

silent
  • 14,494
  • 4
  • 46
  • 86
  • I forgot to add the ```commit/rollback``` requirement in the original question. Now I have added to the second paragraph in the original question. – wonderful world Jul 28 '19 at 22:08
  • 1
    I added another idea in my question. However, if transactions are that important for your application scenario, you might need to rethink if you are not better off with an actual SQL Database instead of a NoSQL store. – silent Jul 29 '19 at 08:12
  • I don't know whether I used the right term used here which is ```transaction```. My only requirement is that the distributed systems like GraphDB and DocumentDB should have the state of the ```customer``` and ```shoppingcart``` in sync. Or in other words, there should not be any orphan documents due to failure of the operation done by the last distributed system in the chain of operations. – wonderful world Jul 29 '19 at 12:17