# JDBC

## Statement lifecycle

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-ME3yOW2U9QcKcSsQDCm%2F-ME3z6RytBkFUT3tadFr%2FScreen%20Shot%202020-08-06%20at%2020.15.12.png?alt=media\&token=6a02002a-f429-4fb0-b7a5-c3832713ff72)

## Locking

### Two phase lock

`Prevents conflicts`

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MEXUG1RVUH-9dm3Vfa7%2F-MEXUQUlqfAfyEBoMxF8%2FScreen%20Shot%202020-08-12%20at%2013.44.51.png?alt=media\&token=15e509ed-2efb-4aff-9851-96d54e3d6b44)

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

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MEXUG1RVUH-9dm3Vfa7%2F-MEXUv5kDiwlrqYwZz6M%2FScreen%20Shot%202020-08-12%20at%2013.47.47.png?alt=media\&token=b7a96465-89d4-4c8d-83ba-5278be90f0f3)

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`&#x20;

* readers don\`t  block writers
* writers don\`t block readers
* writers are blocking other writers

## Isolation levels

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MEcDzU0gwA2pONpbrHt%2F-MEcEqJVMeaADbvYlk1T%2FScreen%20Shot%202020-08-13%20at%2016.35.15.png?alt=media\&token=b6c2cf97-b286-4b14-ad06-1707ef166704)



### Read uncommitted

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MEdVErcSQFT5yYD1lsC%2F-MEfrByVn6dLvfara3p4%2FScreen%20Shot%202020-08-14%20at%2009.26.06.png?alt=media\&token=2f344eaf-c892-4b7a-8bc8-7bee5f821071)

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

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MEdVErcSQFT5yYD1lsC%2F-MEfrcdC480ENheUsj4w%2FScreen%20Shot%202020-08-14%20at%2009.27.54.png?alt=media\&token=7594f5b3-e202-49b9-9e95-e411ca716f36)

* 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

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MEfriFOHbzqHD6E_wBo%2F-MEfrxYA8siOoThciweg%2FScreen%20Shot%202020-08-14%20at%2009.29.28.png?alt=media\&token=399c9110-0c0a-4a38-b082-ad32b68a3f91)

### Serializable

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MEfriFOHbzqHD6E_wBo%2F-MEfsMkljg48kzjxdD0n%2FScreen%20Shot%202020-08-14%20at%2009.31.14.png?alt=media\&token=ef9d1622-12eb-4e17-848f-5089ae68a548)

## 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`&#x20;

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MEXXJxDzEv7gnZ7utz8%2F-MEXYCgEGFgWdDOEpGNA%2FScreen%20Shot%202020-08-12%20at%2014.02.15.png?alt=media\&token=c00a7028-bb5d-4fe7-904c-4c2a914ab130)

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.

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MEXXJxDzEv7gnZ7utz8%2F-MEXcC2Q2Ah-SFzgpL7S%2FScreen%20Shot%202020-08-12%20at%2014.23.48.png?alt=media\&token=fb7302fc-c7c0-4f60-b8f1-76939b682faa)

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`&#x20;

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MEXe75CmZaCEzN-Fcg3%2F-MEXek11rpqEriaZfAea%2FScreen%20Shot%202020-08-12%20at%2014.35.06.png?alt=media\&token=80f62cc2-e835-4974-80cb-67114e53eea9)

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MEXfKZxCtKSfcR4NZin%2F-MEXfvvRettl4YjTJ4MI%2FScreen%20Shot%202020-08-12%20at%2014.40.20.png?alt=media\&token=f4983221-3b23-40dd-b25f-ba39d0f7d572)

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

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MEc9uInoLr9CNYu1tYO%2F-MEcA1NhENZG5lUxw9Yj%2FScreen%20Shot%202020-08-13%20at%2016.13.45.png?alt=media\&token=eae6c527-10e4-4381-9f66-0556b5926bc3)

### Fantom read

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MEc9uInoLr9CNYu1tYO%2F-MEcCl0nV73BnE8SPyaO%2FScreen%20Shot%202020-08-13%20at%2016.25.56.png?alt=media\&token=8566f771-eada-4f35-ad66-57a8f7ce469b)

## Transaction routing

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MExGoOkcCOEbucSqW3j%2F-MExGywXtWPMSXOTHG2E%2FScreen%20Shot%202020-08-17%20at%2018.35.59.png?alt=media\&token=d3737b31-dd80-40d4-909c-b1942ea48188)

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

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MF5XoXFS4X0c6TTe3XQ%2F-MF5YkX7fDeytnk0C27d%2FScreenshot%202020-08-19%20at%2013.51.01.png?alt=media\&token=4c436f1c-cb03-440a-b56f-103ff32beed8)

### Transaction propagation strategies

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MF5XoXFS4X0c6TTe3XQ%2F-MF5_0X9b9JsU50n9a-U%2FScreenshot%202020-08-19%20at%2013.56.17.png?alt=media\&token=3a60c4b3-b375-47c0-bb5a-32fba26979e8)

#### 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.&#x20;

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.

    ```
    UPDATE product 
    SET (quantity, version) = (4, 2)
    WHERE id = 1 AND version = 1
    ```
* 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.

![](https://415484505-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LxtoAXZwwOc4XGto8vb%2F-MF5eixlvhplIrVrE5qo%2F-MF5f6sL9N3ZlsIZpuTa%2FScreenshot%202020-08-19%20at%2014.23.11.png?alt=media\&token=aabbc2d2-224f-43b7-b974-f9e027fea81c)

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.

