Database Transactions with JPA

• Lecture is about Transactions
• The lecture covers transactions, database-level concurrency, and managing transactions with JPA

Transaction Essentials

• A client accesses a banking service to make a transfer via transfer(amount, from, to)
• The banking service calculates account balances with sumAccountBalances()
• A transaction Tx A withdraws 100, and Tx B deposits 100

ACID Attributes

• ACID attributes ensure data integrity and reliability in database transactions
• Atomicity: All operations in a transaction execute as a single, indivisible unit
• Consistency: Concurrent transactions should not compromise the integrity of the data
• Isolation: Incomplete transactions should not be visible to other concurrent transactions
• Durability: Changes made by a transaction should persist, even in the event of system failures

Programming Transactions with JPA

• Code shows how to program transactions with JPA
• An EntityManagerFactory is created
• An EntityManageris created from the factory
• An EntityTransaction is created through em.getTransaction()
• tx.begin() is called
• Queries are executed and EntityManager invocations are made
• tx.setRollbackOnly(); will mark a transaction to rollback
• tx.commit(); will finalize the transaction
• In the catch block, log any thrown exceptions
• In the finally block, close em.close(); to ensure that the EntityManager gets closed

Database-Level Concurrency Control

• Concurrency issues arise when multiple transactions access the same data simultaneously
• Several examples illustrate potential problems and issues which can occur
• Problem #1: Lost Update: Transaction A updates data D1. Transaction B does likewise. Transaction A commits and then transaction B aborts. B's abortion returns DI to its state when B started, undoing A's update
• Problem #2: Dirty Read: Transaction A updates data DI. Transaction B reads the updated (and uncommitted) value for DI. Transaction A aborts while B commits.B may have acted incorrectly based on the state of DI that it read
• Problem #3: Unrepeatable Read: Transaction A reads data item DI and sees a different value each time
• Problem #4: Last Commit Wins: Transactions A and B each read data item DI and then go on to write to DI. The last transaction (B) to commit its write wins, overwriting the earlier transaction's write
• Problem #5: Phantom Read: A Phantom Read occurs where a transaction (A) executes a query twice and the second result includes data that wasn't visible in the first result. A concurrently executing transaction (B) inserts data between the other transaction's queries

Isolation Levels

• Isolation levels define the degree to which transactions are isolated from each other
• Isolation levels affect performance and the likelihood of concurrency problems
• Read Uncommitted can have lost updates, dirty reads, unrepeatable reads, and phantom reads
• Read Committed can have unrepeatable reads, and phantom reads
• Repeatable Read can have phantom reads
• Serializable has no problems

Persistence Context

• JPA's persistence context manages persistent objects and enforces repeatable read semantics
• Serves as a cache that enforces repeatable read semantics
• Performs dirty checking of objects and syncs them with the database
• Provides a guaranteed scope of object identity
• If entityA == entityB then entityA.getId().equals( entityB.getId())

Optimistic Concurrency Control (OCC)

• OCC involves working on a local copy of data and then updating the database
• OCC uses version numbers for data
• Whenever an entity is updated, its version number is incremented

OCC: Last Commit Wins → First Commit Wins

• OCC compares the version number of the data being updated with the version in the database
• If other transaction modifies item, other transactions increment the version, and the second (last) transaction updating Item will throw an OptimisticLockException

A Repeatable Read Problem

• A many-to-one relationship is assumed between Item and Category
• Category A has Item #1, price = $10 and Item #2, price = $20 with a total of $30
• Category B has Item #3, price = $15 and Item #4, price = $10 with a total of $25
• Category C has Item #5, price = $25 with a total of $25
• A sum total of the sums calculates $80
• Calculating the sum of all items across several categories requires that the "get items in each category" is a repeatable read

Forced Version Checking

• Forces version checking to deal with problems with repeatable reads
• For each Category, query all Item instances using an OPTIMISTIC "lock" mode
• For each Item object loaded, the JPA implementation will execute a SQL select statement to compare the Item's version value with that of its corresponding row in the database
• If the version values differ or if the row no longer exists, commit() throws an OptimisticLockException

Forced Version Increment

• On loading the Item, the JPA implementation will increment the object's version number, even though the Item isn't going to be modified
• This persists a new Bid, as long as the bid is higher than the Item's present highest bid
• At commit time, the JPA implementation executes an SQL insert for the new Bid and an update for the Item, to update its version number
• The update will fail if the Item's version has since been incremented, e.g. by other transactions running this place-bid work unit

Pessimistic Locking

• For each Category, query all Item instances, using a database-level read-lock to lock ITEM table rows
• This guarantees repeatable reads
• If the required locks can't be obtained within 5 seconds, the query throws either a LockTimeoutException or a PessimisticLockException
• The acquired locks are released

A Problem with Locking

• Example of updating ITEM rows locking row 116 and 117 by different transactions to prevent deadlocks

Solutions to Deadlock

• Use the Serializable isolation level
• Order database updates based on primary key values
• Use a deadlock detection and resolution strategy such as lock timeouts
• Break deadlock cycles

Summary

• Transactions are necessary to ensure correctness in a concurrent execution environment and to enforce atomicity of work units
• Relational databases provide isolation levels that trade correctness for performance; JPA assumes the Read Committed level from the database and, through the persistence context, guarantees repeatable reads for individual objects
• JPA offers optimistic concurrency control through version numbers
• JPA allows forced version checking and incrementing to cater for particular transactional use cases
• JPA also offers pessimistic concurrency control, utilizing locks at the database level