Introduction
In the first part of the series we set up our sample application which has API Gateway in front Lambda which communicates with Aurora Serverless v2 PostgreSQL database via Data API to store and retrieve products stored in the database. 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.
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.
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.
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.
resourceArn(dbClusterArn).secretArn(dbSecretStoreArn).
transactionId(transactionId).build();
final CommitTransactionResponse transactionCommitResponse = rdsDataClient.commitTransaction(transactionCommitRequest);
We can check the commit transaction status by calling.
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.
resourceArn(dbClusterArn).secretArn(dbSecretStoreArn).
transactionId(transactionId).build();
final RollbackTransactionResponse transactionRollbackResponse = rdsDataClient.rollbackTransaction(transactionRollbackRequest);
We can then check the rollback transaction status by calling.
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.
