# Databases

:clap:[Deep and hardcore analysis of Databases by Jepsen](http://jepsen.io/analyses)

[Optimistic vs Pessimistic locking](https://stackoverflow.com/a/58952004)

## ACID

ACID is a set of properties for DB transactions:

* **Atomicity** - assuming every transaction contains 1..N statements, so either all of them succeed or none of them are applied.
* **Consistency** - ensures that a transaction can only bring the database from one consistent state to another, preserving database [invariants](https://en.wikipedia.org/wiki/Invariant_\(computer_science\)): **any data written to the database must be valid according to all defined rules, including** [**constraints**](https://en.wikipedia.org/wiki/Integrity_constraints)**,** [**cascades**](https://en.wikipedia.org/wiki/Cascading_rollback)**,** [**triggers**](https://en.wikipedia.org/wiki/Database_trigger), and any combination thereof. This prevents database corruption by an illegal transaction.
* **Isolation** - ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially.
* **Durability** - guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure.

## Optimistic locking

**Optimistic locking**, also referred to as **optimistic concurrency control**, allows multiple concurrent users to attempt to update the same resource.&#x20;

There are two common ways to implement optimistic locking:&#x20;

* :thumbsup:version number&#x20;
* timestamp
  * :warning::thumbsdown: server clock can be inaccurate over time.

<figure><img src="https://415484505-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LxtoAXZwwOc4XGto8vb%2Fuploads%2FABvbg4XWpZsgS0r2D2e9%2FScreenshot%202022-10-10%20at%2009.07.59.png?alt=media&#x26;token=58ce9e1f-8690-443d-aedb-2b1a843f5fda" alt=""><figcaption></figcaption></figure>

1.A new column called “version” is added to the database table.

2\. Before a user modifies a database row, the application reads the version number of the row.

3\. When the user updates the row, the application increases the version number by 1 and writes it back to the database.

4\. A database validation check is put in place; the next version number should exceed the current version number by 1. The transaction aborts if the validation fails and the user tries again from step 2.

Optimistic locking is **usually faster than pessimistic locking because we do not lock the database**. However, the **performance of optimistic locking drops dramatically when concurrency is high.**

To understand why, consider the case when many clients try to reserve a hotel room at the same time. Because there is no limit on how many clients can read the available room count, all of them read back the same available room count and the current version number. When different clients make reservations and write back the results to the database, only one of them will succeed, and the rest of the clients receive a version check failure message. These clients have to retry. In the subsequent round of retries, there is only one successful client again, and the rest have to retry. Although the end result is correct, repeated retries cause a very unpleasant user experience.



When dealing with conflicts, you have two options:

* You can try to <mark style="background-color:orange;">avoid the conflict</mark>, and that's what <mark style="background-color:orange;">Pessimistic Locking</mark> does.
* Or, you could <mark style="background-color:orange;">allow the conflict to occur, but you need to detect it upon committing your transactions</mark>, and that's what <mark style="background-color:orange;">Optimistic Locking</mark> does.

Now, let's consider the following <mark style="background-color:purple;">Lost Update anomaly</mark>:

<figure><img src="https://i.stack.imgur.com/vCagm.png" alt=""><figcaption></figcaption></figure>

The Lost Update anomaly can happen in the Read Committed isolation level.

In the diagram above we can see that Alice believes she can withdraw 40 from her `account` but does not realize that Bob has just changed the account balance, and now there are only 20 left in this account.

## Pessimistic Locking

Pessimistic locking achieves this goal by taking a shared or read lock on the account so Bob is prevented from changing the account.

<figure><img src="https://i.stack.imgur.com/oybRy.png" alt=""><figcaption></figcaption></figure>

In the diagram above, both Alice and Bob will acquire a read lock on the `account` table row that both users have read. The database acquires these locks on SQL Server when using Repeatable Read or Serializable.

<mark style="background-color:orange;">Because both Alice and Bob have read the</mark> <mark style="background-color:orange;"></mark><mark style="background-color:orange;">`account`</mark> <mark style="background-color:orange;"></mark><mark style="background-color:orange;">with the PK value of</mark> <mark style="background-color:orange;"></mark><mark style="background-color:orange;">`1`</mark><mark style="background-color:orange;">, neither of them can change it until one user releases the read lock. This is because a write operation requires a write/exclusive lock acquisition, and shared/read locks prevent write/exclusive locks.</mark>

Only after Alice has committed her transaction and the read lock was released on the `account` row, Bob `UPDATE` will resume and apply the change. Until Alice releases the read lock, Bob's UPDATE blocks.

## Optimistic Locking

Optimistic Locking allows the conflict to occur but detects it upon applying Alice's UPDATE as the version has changed.

<figure><img src="https://i.stack.imgur.com/DEdlF.png" alt=""><figcaption></figcaption></figure>

This time, we have an additional `version` column. The `version` column is incremented every time an UPDATE or DELETE is executed, and it is also used in the WHERE clause of the UPDATE and DELETE statements. For this to work, we need to issue the SELECT and read the current `version` prior to executing the UPDATE or DELETE, as otherwise, we would not know what version value to pass to the WHERE clause or to increment.

## Application-level transactions

Relational database systems have emerged in the late 70's early 80's when a client would, typically, connect to a mainframe via a terminal. That's why we still see database systems define terms such as SESSION setting.

Nowadays, over the Internet, we no longer execute reads and writes in the context of the same database transaction, and ACID is no longer sufficient.

For instance, consider the following use case:

<figure><img src="https://i.stack.imgur.com/FCyHh.png" alt=""><figcaption></figcaption></figure>

Without optimistic locking, there is no way this Lost Update would have been caught even if the database transactions used Serializable. This is because reads and writes are executed in separate HTTP requests, hence on different database transactions.

So, optimistic locking can help you prevent Lost Updates even when using application-level transactions that incorporate the user-think time as well.

### Conclusion

Optimistic locking is a very useful technique, and it works just fine even when using less-strict isolation levels, like Read Committed, or when reads and writes are executed in subsequent database transactions.

The downside of optimistic locking is that a rollback will be triggered by the data access framework upon catching an `OptimisticLockException`, therefore losing all the work we've done previously by the currently executing transaction.

The more contention, the more conflicts, and the greater the chance of aborting transactions. Rollbacks can be costly for the database system as it needs to revert all current pending changes which might involve both table rows and index records.

For this reason, pessimistic locking might be more suitable when conflicts happen frequently, as it reduces the chance of rolling back transactions.
