Supporting database transactions in a distributed computing environment
An access service is positioned between a set of container groups and a SQL database service. Upon receipt, at the access service, of a REST request from any container group indicating that a SQL transaction should be started, an entry is created in a staging database, separate from the SQL database service, to track the SQL transaction. Multiple connections are allowed to be established from the container groups to the access service over which other REST requests from any container group are sent. The other REST requests include details of SQL operations to be performed as part of the SQL transaction. The details are cached in the staging database. Upon receiving a REST request from any container group indicating that the SQL transaction should be committed, a single connection is established between the access service and SQL database service to commit the transaction, including the details, as an atomic transaction.
1. A method comprising:
positioning an access service between a plurality of Kubernetes pods and a structured query language (SQL) database service, the plurality of Kubernetes pods hosting instances of microservices from a containerized application and positioned to receive requests from a load balancer that distributes the requests across the Kubernetes pods, and
the access service comprising a staging database, separate from the SQL database service, that temporarily holds SQL operations that are requested by different Kubernetes pods over different connections, but are to be atomically committed;
receiving, at the access service, a REpresentational State Transfer (REST) application programing interface (API) request from any Kubernetes pod indicating that a SQL transaction should be started;
creating an entry in the staging database to track the SQL transaction;
allowing multiple connections to be established from the plurality of Kubernetes pods to the access service over which other REST requests from any of the Kubernetes pods are sent, the other REST requests comprising details of SQL operations to be performed as part of the SQL transaction;
caching the details in the staging database;
receiving, at the access service, a REST request from any Kubernetes pod indicating that the SQL transaction should be committed; and
establishing a single connection from the access service to the SQL database service to commit the SQL transaction including the details as an atomic transaction via the single connection.
2. The method of claim 1 further comprising:
after the SQL transaction including the details have been committed via the single connection to the SQL database service, deleting the transaction including the details from the staging database.
3. The method of claim 1 wherein a load balancer load balances the SQL operations to be performed as part of the SQL transaction over the plurality of Kubernetes pods.
4. The method of claim 1 further comprising:
upon receiving the REST request indicating that the SQL transaction should be committed, acquiring a lock on a database of the SQL service to prevent other SQL transactions from modifying data associated the SQL transaction while the SQL transaction is in progress.
5. The method of claim 1 further comprising:
upon receiving the REST request indicating that the SQL transaction should be started, generating and returning to a Kubernetes pod an identifier for the SQL transaction;
caching the identifier for the SQL transaction in a first table associated with the staging database; and
upon receiving the details of the SQL operations to be performed as part of the SQL transaction, caching the details in a second table associated with the staging database.
6. The method of claim 1 further comprising:
after establishing the single connection from the access service to the SQL database service, flushing the SQL transaction including the details of the SQL operations from the staging database to the SQL database service via the single connection.
7. A system comprising: a processor; and memory configured to store one or more sequences of instructions which, when executed by the processor, cause the processor to carry out the steps of:
positioning an access service between a plurality of Kubernetes pods and a structured query language (SQL) database service, the plurality of Kubernetes pods hosting instances of microservices from a containerized application and positioned to receive requests from a load balancer that distributes the requests across the Kubernetes pods, and
the access service comprising a staging database, separate from the SQL database service, that temporarily holds SQL operations that are requested by different Kubernetes pods over different connections, but are to be atomically committed;
receiving, at the access service, a REpresentational State Transfer (REST) application programing interface (API) request from any Kubernetes pod indicating that a SQL transaction should be started;
creating an entry in the staging database to track the SQL transaction;
allowing multiple connections to be established from the plurality of Kubernetes pods to the access service over which other REST requests from any of the Kubernetes pods are sent, the other REST requests comprising details of SQL operations to be performed as part of the SQL transaction;
caching the details in the staging database;
receiving, at the access service, a REST request from any Kubernetes pod indicating that the SQL transaction should be committed; and
establishing a single connection from the access service to the SQL database service to commit the SQL transaction including the details as an atomic transaction via the single connection.
8. The system of claim 7 wherein the processor further carries out the steps of:
after the SQL transaction including the details have been committed via the single connection to the SQL database service, deleting the transaction including the details from the staging database.
9. The system of claim 7 wherein a load balancer load balances the SQL operations to be performed as part of the SQL transaction over the plurality of Kubernetes pods.
10. The system of claim 7 wherein the processor further carries out the steps of:
upon receiving the REST request indicating that the SQL transaction should be committed, acquiring a lock on a database of the SQL service to prevent other SQL transactions from modifying data associated the SQL transaction while the SQL transaction is in progress.
11. The system of claim 7 wherein the processor further carries out the steps of:
upon receiving the REST request indicating that the SQL transaction should be started, generating and returning to a Kubernetes pod an identifier for the SQL transaction;
caching the identifier for the SQL transaction in a first table associated with the staging database; and
upon receiving the details of the SQL operations to be performed as part of the SQL transaction, caching the details in a second table associated with the staging database.
12. The system of claim 7 wherein the processor further carries out the steps of:
after establishing the single connection from the access service to the SQL database service, flushing the SQL transaction including the details of the SQL operations from the staging database to the SQL database service via the single connection.
13. A computer program product, comprising a non-transitory computer-readable medium having a computer-readable program code embodied therein, the computer-readable program code adapted to be executed by one or more processors to implement a method comprising:
positioning an access service between a plurality of Kubernetes pods and a structured query language (SQL) database service, the plurality of Kubernetes pods hosting instances of microservices from a containerized application and positioned to receive requests from a load balancer that distributes the requests across the Kubernetes pods, and
the access service comprising a staging database, separate from the SQL database service, that temporarily holds SQL operations that are requested by different Kubernetes pods over different connections, but are to be atomically committed;
receiving, at the access service, a REpresentational State Transfer (REST) application programing interface (API) request from any Kubernetes pod indicating that a SQL transaction should be started;
creating an entry in the staging database to track the SQL transaction;
allowing multiple connections to be established from the plurality of Kubernetes pods to the access service over which other REST requests from any of the Kubernetes pods are sent, the other REST requests comprising details of SQL operations to be performed as part of the SQL transaction;
caching the details in the staging database;
receiving, at the access service, a REST request from any Kubernetes pod indicating that the SQL transaction should be committed; and
establishing a single connection from the access service to the SQL database service to commit the SQL transaction including the details as an atomic transaction via the single connection.
14. The computer program product of claim 13 wherein the method further comprises:
after the SQL transaction including the details have been committed via the single connection to the SQL database service, deleting the transaction including the details from the staging database.
15. The computer program product of claim 13 wherein a load balancer load balances the SQL operations to be performed as part of the SQL transaction over the plurality of Kubernetes pods.
16. The computer program product of claim 13 wherein the method further comprises:
upon receiving the REST request indicating that the SQL transaction should be committed, acquiring a lock on a database of the SQL service to prevent other SQL transactions from modifying data associated the SQL transaction while the SQL transaction is in progress.
17. The computer program product of claim 13 wherein the method further comprises:
upon receiving the REST request indicating that the SQL transaction should be started, generating and returning to a Kubernetes pod an identifier for the SQL transaction;
caching the identifier for the SQL transaction in a first table associated with the staging database; and
upon receiving the details of the SQL operations to be performed as part of the SQL transaction, caching the details in a second table associated with the staging database.
18. The computer program product of claim 13 wherein the method further comprises:
after establishing the single connection from the access service to the SQL database service, flushing the SQL transaction including the details of the SQL operations from the staging database to the SQL database service via the single connection.