Microservice distributed transaction - single database, multiple requests

Question

I am interested in finding a way to support distributed transactions across multiple requests (we'll call them clients, since they are a client of the database) to a single database. I have read about elastic database transactions, but I believe these solve a different problem: specifically, a single client block of code (typically demarcated with a "using" block) that accesses multiple databases. My scenario is the opposite: I have multiple client requests, possibly spread across many application servers, that all want their database operations (all on a single database) to perform in a single transaction. Any sort of solution would necessitate the passing of some sort of "transactionID" between the requests so that the database would know what to group together. I would also envision some type of explicit "commit" or "rollback" operation request since the database would have no idea on its own when the transaction was complete. The scenario I am describing seems like it would be a common challenge for microservice architectures. Are there either general solutions to this scenario and/or any specific Azure SQL solutions?

Answer 1

Hi KenH,

Is Azure SQL database a required or desired platform? Sounds like you are still exploring options, so I wanted to present some additional documentation that may be of assistance to you. 

Why a microservices approach to building applications?

Microservices architecture style

Overview of Azure SQL Database services: Single Database, Elastic Pools, and Managed Instance:

What is the Azure SQL Database service?

Microservices With CosmosDB on Azure

These design patterns are useful for building reliable, scalable, secure applications in the cloud: Cloud Design Patterns

Tunable data consistency levels in Azure Cosmos DB

Some items to consider. Regards, ~Mike

Answer 2

Thanks for the reply. 

The platform (Azure SQL) is well established and cannot be changed. My reference to microservices may have confused the issue somewhat. This is an effort to move (and modernize the architecture of) an existing system to the cloud. So the monolithic database is here to stay. I understand that a proper microservices architecture would have fully discrete, autonomous components. The reason for separating the API into more loosely coupled web services was to support more fine grained scalability. My original question was hoping for something similar to MSDTC that can be used with Azure SQL or a tip or trick that could be used instead. We will likely settle for a compromise of eventual consistency where acceptable and bundling a few of the calls that must be ACID behind a single endpoint. Thanks again.

Answer 3

I am interested in finding a way to support distributed transactions across multiple requests (we'll call them clients, since they are a client of the database) to a single database. I have read about elastic database transactions, but I believe these solve a different problem: specifically, a single client block of code (typically demarcated with a "using" block) that accesses multiple databases.

My scenario is the opposite: I have multiple client requests, possibly spread across many application servers, that all want their database operations (all on a single database) to perform in a single transaction. Any sort of solution would necessitate the passing of some sort of "transactionID" between the requests so that the database would know what to group together. I would also envision some type of explicit "commit" or "rollback" operation request since the database would have no idea on its own when the transaction was complete.

The scenario I am describing seems like it would be a common challenge for microservice architectures. Are there either general solutions to this scenario and/or any specific Azure SQL solutions?

Good day,

Your description is very vague and without understand the full behavior of the system and your needs it is impossible to advice you what exactly you should do. This is always the case with questions that related to architecture.

The architects of the system are the people with the best knowledge and these who should be familiar with any aspect of the system! This is not something we can do in the forum in the scope of a thread. The maximum that we can do is give some golden rules, ideas, maybe some guidelines, and answer specific questions. 

>> I have read about elastic database transactions, but I believe these solve a different problem

True ;-)

elastic database transactions is used to execute transactions that span several databases, but you describe a single database. This for example fit a shard databases (data that is spreads over several databases). This is totally not relevant as you understood already :-)

>> I have multiple client requests, possibly spread across many application servers, that all want their database operations (all on a single database) to perform in a single transaction.

Parallel and asynchronous execution in SQL Server is a use case for Service Broker. It is not supported in Azure Database, but it is supported in Azure Managed Instance. If you can use Managed Instance then you will need some sort of mechanism to determine if the execution of each transaction was finished and if successful or not. This can be done by sending a message when it has completed or vise versa buy removing a record.

Please check my answer in this thread:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/996703bc-68d2-4b6d-a0fe-166721267216/parallel-tasks-in-sql-server-agent?forum=sqltools

Does something like this can fit your needs or do your need different solution ?

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]