Data API for Amazon Aurora Serverless v2 with AWS SDK for Java - Part 4 Working with database transactions

Introduction

In the first part of the series we set up our sample application which has API Gateway in front of Lambda functions which communicate with Aurora Serverless v2 PostgreSQL database via Data API to create the products and retrieve them (by id). In the second part we dove dive deeper into the new Data API for Aurora Serverless v2 itself and its capabilities like executing SQL Statements and used AWS SDK for Java for it. In the third part of the series we explored Data API capabilities to batch SQL statement over an array of data for bulk update and insert operations. In this part of the series we'll look at how to use database transactions with Data API.

Working with Data API and its database transactions capabilities

To show the database transaction capability I used the same sample application but added the capabilities to create the user and user address with HTTP PUT /user request (see the CreateUserDataApiFunction Lambda definition in the SAM template). The corresponding CreateUserViaAuroraServerlessV2DataApiHandler Lambda function expects the JSON as HTTP body like

{
  "first_name": "Vadym",
  "last_name":  "Kazulkin",
  "email":  "blabla@email.com",
  "address": {
     "street": "Alexandra Platz",
     "city": "Berlin",
     "country": "Germany",
     "zip": "53334"
   }
}

which contains the information about the user and user address which will be stored within one transaction in the 2 corresponding separate tables of the PostgreSQL database. In the "Transactional Example" in the READme where I described the creation of those tables and sequences.

Now let's explore how we can implement this use case using the transactional capabilities of the Data API. For the complete implementation please visit the createUserAndAddressTransactional method of the AuroraServerlessV2DataApiDao.java.

In order to begin the transaction we need to create BeginTransactionRequest and invoke beginTransaction method on the RdsDataClient.

final BeginTransactionRequest transactionBeginRequest = 
BeginTransactionRequest.builder().database("").
resourceArn(dbClusterArn).secretArn(dbSecretStoreArn).build();

final BeginTransactionResponse transactionBeginResponse = 
rdsDataClient.beginTransaction(transactionBeginRequest);

After it we need to get the transaction id from the BeginTransactionResponse.

String transactionId = transactionBeginResponse.transactionId();

When creating all subsequent ExecuteStatementRequests which are part of the same transaction like creating user (as in the example below) and user address in our case we extra set the transaction id by invoking transactionId method.

final ExecuteStatementRequest createUserRequest = ExecuteStatementRequest.builder().
database("").resourceArn(dbClusterArn).
secretArn(dbSecretStoreArn).sql(CREATE_USER_SQL).
parameters(userIdParam, firstNameParam, lastNameParam, emailParam).
transactionId(transactionId).build();

If the creation of the user and user address within the transaction was successful (no error was thrown) we need to commit the transaction by creating CommitTransactionRequest using the transaction id and invoke commitTransaction method on the RdsDataClient.

final CommitTransactionRequest transactionCommitRequest = CommitTransactionRequest.builder().
resourceArn(dbClusterArn).secretArn(dbSecretStoreArn).
transactionId(transactionId).build();

final CommitTransactionResponse transactionCommitResponse = rdsDataClient.commitTransaction(transactionCommitRequest);

We can check the commit transaction status by calling.

transactionCommitResponse.transactionStatus();

If the creation of the user or user address within the transaction caused the error we need to rollback the transaction by creating RollbackTransactionRequest using the transaction id and invoke rollbackTransaction method on the RdsDataClient.

final RollbackTransactionRequest transactionRollbackRequest = RollbackTransactionRequest.builder().
resourceArn(dbClusterArn).secretArn(dbSecretStoreArn).
transactionId(transactionId).build();

final RollbackTransactionResponse transactionRollbackResponse = rdsDataClient.rollbackTransaction(transactionRollbackRequest);

We can then check the rollback transaction status by calling.

transactionRollbackResponse.transactionStatus();

To test both scenarios you can do the following :

• for the successfully executed transaction use the /user path of the created API Gateway and pass the following JSON:

{
  "first_name": "Vadym",
  "last_name":  "Kazulkin",
  "email":  "blabla@email.com",
  "address": {
     "street": "Alexandra Platz",
     "city": "Berlin",
     "country": "Germany",
     "zip": "53334"
   }
}

• for the transaction which won't be successful and will be rollbacked use the /user path of the created API Gateway and pass the following JSON:

{
  "first_name": "Vadym",
  "email":  "blabla@email.com",
  "address": {
     "street": "Alexandra Platz",
     "city": "Berlin",
     "country": "Germany",
     "zip": "53334"
   }
}

as the last_name is missing as the User property and the last_name column of the tbl_user table can't be null, creating the user in the database will cause and error and the transaction will be rollbacked.

Conclusion

In this part of the series, we looked at how to use database transactions with Data API. We learned how to use Data API capabilities to begin the transaction and get the transaction id which we use in the subsequent executeStatement(s) requests and then to commit or rollback this transaction.

In the next part of the series we'll make some performance measurements of the Data API for the Aurora Serverless v2.