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...