In Chapter 5, you were introduced, or possibly reintroduced, to the basic concepts of transactions and their significance within relational database solutions. Most importantly, you gained an understanding of the transactional feature support that the R2DBC specification provides.
In this chapter, we’ll be using the MariaDB R2DBC driver to get a first-hand look at what it takes to create, manage, and utilize transactions in a reactive solution.
Database Transaction Support
Among the differences that exist between different relational database solutions is the number of transactional features they support. In Chapter 5, you learned the transactional capabilities that are available within the R2DBC specification.
Continuing with the trend we’ve set in the past few previous chapters; we’re going to take a look at those capabilities in action using the MariaDB R2DBC driver. We’re going to avoid diving into the intricacies of MariaDB-specific features, instead covering what is possible using R2DBC.
Database Preparation
Going forward, we’re going to be looking at Java code examples, using the MariaDB R2DBC driver, that rely on a SQL table, called tasks, that exists in a database, todo, that we added to a MariaDB instance in the last chapter.
To get us on the same page, you can execute the SQL in Listing 14-1 to reset the todo.tasks table.
Truncating the existing records and adding new records to todo.tasks
Tip
In SQL, the TRUNCATE TABLE statement is a Data Definition Language (DDL) operation that marks the extents of a table for deallocation. Truncating the tasks table will remove all preexisting information as well restart the auto-generated value count for the id column.
Executing SQL in Listing 14-1 will ensure that our table contains three records, containing id column values of 1, 2, and 3, respectively.
+----+-------------+-----------+
| id | description | completed |
+----+-------------+-----------+
| 1 | Task A | 0 |
| 2 | Task B | 0 |
| 3 | Task C | 0 |
+----+-------------+-----------+
Listing 14-2
The contents of todo.tasks after executing the SQL in Listing 14-1
Transaction Basics
The R2DBC specification provides support for controlling transactional operations via code, as opposed to using SQL directly, through the Connection interface, which all drivers are required to implement.
Transactions can be started implicitly or explicitly. When a Connection object is in auto-commit mode, transactions are started implicitly when a SQL statement is executed through a Connection object.
Auto-committing
In Chapter 5, you learned that the auto-commit mode of a Connection object can be retrieved using the isAutoCommit method and changed by invoking the setAutoCommit method (Listing 14-3).
boolean isAutoCommit = connection.isAutoCommit();
if (isAutoCommit) {
connection.setAutoCommit(false).block();
}
Listing 14-3
Disabling auto-commit for a Connection object
Tip
Auto-commit is enabled by default within the MariaDB R2DBC driver.
Explicit Transactions
Once the auto-commit mode is disabled, transactions must be explicitly started. Using the MariaDB driver, this can be accomplished by invoking the beginTransaction method on a MariadbConnection object (Listing 14-4).
connection.beginTransaction().subscribe();
Listing 14-4
Beginning a MariaDB transaction
Tip
Using the beginTransaction method on a MariadbConnection object will automatically disable auto-commit for the connection.
Committing a Transaction
Once you’ve started down the path of explicitly handling a database transaction, no matter how many SQL statements you’ve created and executed, you will need to call the commitTransaction method to make the changes to the data permanent (Listing 14-5).
MariadbStatement insertStatement = connection.createStatement("INSERT INTO tasks (description) VALUES ('Task D'));
insertStatement.execute()
.then(connection.commitTransaction())
.subscribe();
Listing 14-5
Beginning and committing a MariaDB transaction
Note
In Listing 14-5, the then method, provided by Project Reactor, is used to set up chained, declarative interactions.
Executing the code in Listing 14-5 will result in a new task row being added to the tasks table. The INSERT statement’s change becomes permanent when the transaction is committed. You can confirm the results by taking a look at the contents within the tasks table (Listing 14-6).
SELECT * FROM todo.tasks;
+----+-------------+-----------+
| id | description | completed |
+----+-------------+-----------+
| 1 | Task A | 0 |
| 2 | Task B | 0 |
| 3 | Task C | 0 |
| 4 | Task D | 0 |
+----+-------------+-----------+
Listing 14-6
Output that results after committing the transaction
Rolling Back a Transaction
However, if there is a scenario that requires you to reverse the SQL statement or, for some reason, the transaction fails, it can all be rolled back by executing and subscribing to the rollbackTransaction method (Listing 14-7).
connection.rollbackTransaction().subscribe();
Listing 14-7
Rolling back a MariaDB transaction
Executing the code in Listing 14-7 will roll back the INSERT statement’s change, preventing it from being committed. When this happens, the contents of the tasks table will look like Listing 14-8.
SELECT * FROM todo.tasks;
+----+-------------+-----------+
| id | description | completed |
+----+-------------+-----------+
| 1 | Task A | 0 |
| 2 | Task B | 0 |
| 3 | TASK C | 0 |
+----+-------------+-----------+
Listing 14-8
Output that results after rolling back the transaction
An Imperative Perspective
Think back to Chapter 1, where you learned about imperative and declarative programming. As a refresher, remember that blocking operations are common within the imperative, or step-by-step, programming paradigm and languages. By contrast, declarative approaches do not focus on how to accomplish a specific goal but rather the goal itself.
And, by now, you know that the intention of R2DBC, and reactive programming as a whole, is to provide a declarative solution. That being said, sometimes it’s easier for our brains to understand more of an imperative flow.
In an effort to most clearly lay out a transactional workflow, I’ve taken advantage of the block and blockLast methods in Listing 14-9, something you’d likely not do in a truly reactive application but helps to illustrate what’s happening a little more clearly.
try {
connection.beginTransaction().block();
MariadbStatement multiStatement = connection.createStatement("DELETE FROM tasks; INSERT INTO tasks (description) VALUES ('Task D');SELECT * FROM tasks;");
multiStatement.execute().blockLast();
connection.commitTransaction().subscribe();
}
catch(Exception e) {
connection.rollbackTransaction().subscribe();
// More exception handling code
}
Listing 14-9
Handling exceptions and transactions
Listing 14-9 takes advantage of the MariaDB R2DBC driver’s ability to execute multiple SQL statements within a single MariadbStatement object.
After successfully committing the transactions from Listing 14-9
SELECT * FROM todo.tasks;
+----+-------------+-----------+
| id | description | completed |
+----+-------------+-----------+
| 1 | Task A | 0 |
| 2 | Task B | 0 |
| 3 | Task C | 0 |
+----+-------------+-----------+
Listing 14-11
Encountering an exception and rolling back the transactions from Listing 14-9
Managing Savepoints
In Chapter 5, you learned that savepoints can be useful when it is necessary to roll back part of a transaction. This is usually the case when there is a low possibility of error in part of the transaction and the prior validation of the operation’s accuracy is too costly.
Figure 14-1
The basic workflow of a savepoint
Using Savepoints
Using the MariaDB driver, savepoints can be created using the createSavepoint method, available within a MariadbConnection object.
Boolean rollbackToSavepoint = true;
MariadbStatement insertStatement = connection.createStatement("INSERT INTO tasks (description) VALUES ('TASK X');");
MariadbStatement deleteStatement = pconnection.createStatement("DELETE FROM tasks WHERE id = 2;");
In this scenario, the rollbackOrCommit method, used in Listing 14-12, contains conditional functionality that either rolls back a transaction to savepoint_1 and then commits the transaction or commits the entire transaction.
Whether you use the declarative approach from Listings 14-12 and 14-13 or the imperative flow in Listing 14-14, the output will be the same, as indicated in Listings 14-15 and 14-16.
SELECT * FROM todo.tasks;
+----+-------------+-----------+
| id | description | completed |
+----+-------------+-----------+
| 1 | Task A | 0 |
| 2 | Task B | 0 |
| 3 | Task C | 0 |
| 4 | Task D | 0 |
+----+-------------+-----------+
Listing 14-15
Output that results after rolling back to savepoint_1
SELECT * FROM todo.tasks;
+----+-------------+-----------+
| id | description | completed |
+----+-------------+-----------+
| 1 | Task A | 0 |
| 3 | Task C | 0 |
| 4 | Task D | 0 |
+----+-------------+-----------+
Listing 14-16
Output that results after committing the entire transaction
Releasing Savepoints
Because savepoints allocate resources directly on the databases, database vendors may require that savepoints be released to dispose of resources. You learned in Chapter 5 that there are a variety of ways that savepoints will be deallocated, including through the use of the releaseSavepoint method (Listing 14-17).
Databases expose the ability to specify the level of isolation within transactions. The concept of transactional isolation defines the degree to which one transaction can be isolated from data or resource modification performed by other transactions, thereby impacting concurrent access while multiple transactions are active.
The IsolationLevel enumeration value can be retrieved by calling the getTransactionIsolationLevel method, available through a MariadbConnection object.
Tip
Head to Chapter 5 for more information on IsolationLevel.
Changing the MariaDB R2DBC driver IsolationLevel setting
Show Me the Code
You can find a complete, fully compilable sample application in the GitHub repository dedicated to this book. If you haven’t already done so, simply navigate to https://github.com/apress/r2dbc-revealed to either git clone or directly download the contents of the repository. From there you can find a sample application dedicated to this chapter in the ch14 folder.
Summary
The ability to use and control transactions is a key feature for building solutions that use relational databases. And that’s because transactions are used to provide data integrity, isolation, correct application semantics, and a consistent view of data during concurrent database access.
In Chapter 5, you learned that R2DBC-compliant drivers are required to provide transaction support. In this chapter, you were able to see that in action. Using the MariaDB R2DBC driver, you learned how to create, commit, and roll back transactions. You also learned how to create and manage savepoints. And to finish things off, you saw how to handle isolation levels within a MariaDB database using R2DBC.
