Chapter 9: CRUD, Transactions, and Locking

In this chapter, we'll cover a must-know mix of fundamental notions about CRUD operations, transactions, and locking. These three topics are heavily exploited in almost any database application. In a common scenario, an application has a significant number of CRUD operations that are executed in explicitly demarcated logical transactions and, in certain cases, they also need to explicitly control the concurrent access to data to prevent race conditions, lost updates, and other SQL phenomena (or SQL anomalies).

In this chapter, we will cover the following topics:

• CRUD
• Navigating (updatable) records
• Transactions
• Locking

Let's get started!

Technical requirements

The code for this chapter can be found on GitHub at https://github.com/PacktPublishing/jOOQ-Masterclass/tree/master/Chapter09.

CRUD

Besides the awesome DSL-fluent API for expressing complex SQL, jOOQ can be used to express everyday SQL operations as well. These are known as CRUD operations (Create (INSERT), Read (SELECT), Update (UPDATE), and Delete (DELETE)), and jOOQ facilitates them via a dedicated API that involves UpdatableRecord types. In other words, the jOOQ Code Generator generates a UpdatableRecord (a record that can be fetched and stored again in the database) for each table that has a primary key (not just a simple unique key!). Tables without a primary key (org.jooq.TableRecord) are rightly considered non-updatable by jOOQ. You can easily recognize a jOOQ UpdatableRecord because it has to extend the UpdatableRecordImpl class (simply inspect your generated records from jooq.generated.tables.records). Next, jOOQ exposes a CRUD API that allows you to operate directly on these updatable records instead of writing DSL-fluent queries (which fits better for complex queries that involve more than one...

Navigating (updatable) records

jOOQ exposes several navigation methods that can be used for attached (updatable) records only (TableRecord and UpdatableRecord). To use these methods, please consider the following note.

Important Note

While these methods are very convenient/appealing, they are also a big N+1 risk. UpdatableRecord is great for CRUD, but if you aren't using CRUD, then you shouldn't use UpdatableRecord. It's better to project only the columns you need and try to use joins or other SQL utilities to fetch data from multiple tables.

These methods navigate based on the foreign key references. For instance, with an attached DepartmentRecord, we can navigate its parent (OFFICE) via fetchParent(ForeignKey<R, O> key), as shown in the following example:

public OfficeRecord fetchOfficeOfDepartment(
     DepartmentRecord dr) {
  
  return dr.fetchParent(Keys.DEPARTMENT_OFFICE_FK);
  // or, Keys...

Transactions

Among other benefits, transactions give us the ACID properties. We can distinguish between read-only and read-write transactions, different isolation levels, different propagation strategies, and so on. While Spring Boot supports a comprehensive transactional API (Spring TX) that's commonly used via @Transactional and TransactionTemplate, jOOQ comes with a simple transaction API (and an org.jooq.TransactionProvider SPI) that fits perfectly in the context of fluent style.

The following diagram highlights the main implementations of this SPI:

Figure 9.8 – jOOQ transaction providers

Starting with jOOQ 3.17 we have support for transactions in R2DBC as well. So, jOOQ 3.17 come with support for reactive transactions.

Mainly, the jOOQ API for blocking transactions can be used like so:

ctx.transaction(configuration -> {
  DSL.using(configuration)...
  // or, configuration.dsl()...
}
var result = ctx.transactionResult...

Hooking reactive transactions

As mentioned earlier, starting with jOOQ 3.17, we can take advantage of reactive transactions or transactions in R2DBC. Reactive transactions are easy to use via Publisher, as they have the same semantics as JDBC’s blocking APIs Here is an example of how to write a nested reactive transaction:

Flux<?> flux = Flux.from(
 ctx.transactionPublisher(outer -> Flux.from(
  DSL.using(outer).delete(SALE) // or, outer.dsl()
                 .where(SALE.SALE_ID.eq(2L)))
     .thenMany(Flux.from(
     DSL.using(outer).transactionPublisher( // or, outer.dsl()
      inner -> Flux.from(
      DSL.using(inner).insertInto(TOKEN) // or, inner.dsl()
                 ...

Locking

Locking is used to orchestrate concurrent access to data to prevent race condition threads, deadlocks, lost updates, and other SQL phenomena.

Among the most popular locking mechanisms, we have optimistic and pessimistic locking. As you'll see shortly, jOOQ supports both of them for CRUD operations. So, let's start with optimistic locking.

Optimistic locking overview

Optimistic locking is commonly related to the lost updates SQL phenomena, so let's quickly overview this anomaly.

A lost update is a popular anomaly that can seriously affect data integrity. A transaction reads a record and uses this information to make business decisions (for instance, decisions that may lead to that record being modified) without being aware that, in the meantime, a concurrent transaction has modified that record and committed it. When the first transaction commits, it is unaware of the lost update. This may cause data integrity issues (for example, the inventory can...

Summary

I'm glad that you've come this far and that we've managed to cover the three main topics of this chapter – CRUD, transactions, and locking. At this point, you should be familiar with jOOQ UpdatableRecords and how they work in the context of CRUD operations. Among other things, we've learnt about cool stuff such as the must-know attach()/detach(), the handy original() and reset(), and the fancy store() and refresh() operations. After that, we learned how to handle transactions in the context of Spring Boot and jOOQ APIs before tackling optimistic and pessimistic locking in jOOQ.

In the next chapter, we'll learn how to batching, bulking and loading files into the database via jOOQ. We'll also do single-thread and multi-thread batching.