JDBC
Statement lifecycle
Locking
Two phase lock
Prevents conflicts
both Alice and Bob select a post record, both acquiring a shared lock on this record
when Bob attempts to update the post entry, his statement is blocked by the Lock Manager
because Alice is still holding a shared lock on this database row
only after Alice’s transaction ends and all locks are being released, Bob can resume his update operation
Bob’s update will generate a lock upgrade, so the shared lock is replaced by an exclusive lock,
which will prevent any other concurrent read or write operation
Alice starts a new transaction and issues a select query for the same post entry, but the
statement is blocked by the Lock Manager since Bob owns an exclusive lock on this record
after Bob’s transaction is committed, all locks are released and Alice’s query can be resumed,
so she will get the latest value of this database record.
A deadlock happens when two concurrent transactions cannot make progress because each one waits for the other to release a lock. Because both transactions are in the lock acquisition phase, neither one will release a lock prior to acquiring the next one.
Preserving the lock order becomes the responsibility of the data access layer, and the database can only assist in recovering from a deadlock situation.
The database engine runs a separate process that scans the current conflict graph for lock-wait cycles (which are caused by deadlocks). When a cycle is detected, the database engine picks one transaction and aborts it, causing its locks to be released, so the other transaction can make progress.
Multi-version concurrency control
Uses conflict detection strategy
readers don`t block writers
writers don`t block readers
writers are blocking other writers
Isolation levels
Read uncommitted
Read committed
lowest practical isolation level. Many database systems choose Read Committed as the default isolation level because it delivers the best performance, while preventing fatal anomalies such as dirty writes and dirty reads. But performance has its price as Read Committed permits many anomalies that might lead to data corruption.
no dirty reads
when reading from DB you see only committed data
no dirty writes
when writing to DB you overwrite only committed data
Repeatable read
Serializable
Phenomena
Relaxing serializability guarantees may generate data integrity anomalies, which are also referred as phenomena.
Dirty write
happens when two concurrent transactions are allowed to modify the same row at the same time
If the two transactions commit, one transaction will silently overwrite the other transaction, causing a lost update. Another problem arises when the first transaction wants to roll back. The database engine would have to choose one of the following action paths:
it can restore the row to its previous version (as it was before the first transaction changed it), but then it will overwrite the second transaction uncommitted change
it can acknowledge the existence of a newer version (issued by the second transaction), but then, if the second transaction has to roll back, it’s previous version becomes the uncommitted change of the first transaction.
If the database engine didn’t prevent dirty writes, guaranteeing rollbacks would not be possible. Because atomicity cannot be implemented in the absence of reliable rollbacks, all database systems must therefore prevent dirty writes.
However, what happens if the earlier write is part of a transaction that has not yet committed, so the later write overwrites an uncommitted value? This is called a dirty write. Transactions running at the read committed isolation level must prevent dirty writes, usually by delaying the second write until the first write’s transaction has committed or aborted.
If transactions update multiple objects, dirty writes can lead to a bad outcome. For example, consider Figure 7-5, which illustrates a used car sales website on which two people, Alice and Bob, are simultaneously trying to buy the same car. Buying a car requires two database writes: the listing on the website needs to be updated to reflect the buyer, and the sales invoice needs to be sent to the buyer. In the case of Figure 7-5, the sale is awarded to Bob (because he performs the winning update to the listings table), but the invoice is sent to Alice (because she performs the winning update to the invoices table). Read committed prevents such mishaps.
Dirty read
happens when a transaction is allowed to read the uncommitted changes of some other concurrent transaction
For every object that is written, the database remembers both the old committed value and the new value set by the transaction that currently holds the write lock. While the transaction is ongoing, any other transactions that read the object are simply given the old value. Only when the new value is committed do transactions switch over to reading the new value.
Non repeatable read
Fantom read
Transaction routing
Transaction boundaries
By default, every Connection starts in auto-commit mode, each statement being executed in a separate transaction. Unfortunately, it doesn’t work for multi-statement transactions as it moves atomicity boundaries from the logical unit of work to each individual statement.
Auto-commit should be avoided as much as possible, and, even for single statement transactions, it’s good practice to mark the transaction boundaries explicitly.
Distributed transactions
Two phase commit
Transaction propagation strategies
Declarative exception handling
Since the transaction logic wraps around the underlying service and data access logic call chain, the exception handling must also be configured declaratively. By default, both Java EE and Spring roll back on system exceptions (any RuntimeException) and commit on application exceptions (checked exceptions).
In Java EE, the rollback policy can be customized using the @ApplicationException annotation. Spring allows each transaction to customize the rolling back policy by listing the exception types triggering a transaction failure.
Application level transactions
Pessimistic locking
Most db systems offer the possibility of manually requesting shared or exclusive locks. This concurrency control is said to be pessimistic because it assumes that conflicts are bound to happen, and so they must be prevented accordingly.
Optimistic locking
Multi-version concurrency control is an optimistic concurrency control strategy since it assumes that contention is unlikely to happen, and so it doesn’t rely on locking for controlling access to shared resources.
The optimistic locking concurrency algorithm looks like this:
when a client reads a particular row, its version comes along with the other fields
upon updating a row, the client filters the current record by the version it has previously
loaded.
if the statement update count is zero, the version was incremented in the meanwhile, and the current transaction now operates on a stale record version.
Both Alice and the batch processor try to increment the product version optimistically. The batch processor can successfully update the product quantity since the SQL statement filtering criteria matches the actual database record version. When Alice tries to update the product, the database returns a zero update count, and, this way, she is notified about the concurrent update that happened in the meanwhile. The lost update can be prevented because the application can abort the current transaction when being notified of the stale record versions.
Last updated
