Optimistic V.S Pessimistic databases
Published: 2022-11-01 16:09:15

## Introduction

When you have one single user that will use your application that the optimal case and Youtopia of our software industry, but when it comes to multiple users, that what will bring all evil to the application as you now have to manage concurrency and pay attention and resources to manage that, databases engineers suffered from this issue and came with multiple solutions. Let's discuss them mainly with **write** operation.

## Handling multiple writes

Let's define the problem first and then discuss the solitons.
As below image 2 users are trying to increment same counter value in DB each user read same value from DB because they start they transaction almost at the same time, correct result of this operations must to set counter value to 44 but because race condition the result is 43 and this gives us wrong information.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/enaef2zbxlyk85h8fdjp.png)

Or as below image 2 users read same record A from DB and each one runs its' own logic upon it, but regarding that user Y finished its' write late so database kept its' value, and when user X try to refresh the record he will not find his updates and this known by **Lost Updates or Last Write Win (LWW)** problem.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0gh30ux6v0fijruzqeje.png)

## First solution: Locking

first solution for these problems is locking, whenever any transaction/user want to write to database he must get a lock upon records and prevent any transaction/user from accessing these records.

**- Explicit lock**

Database allows user to acquire locks on demand as below
`SELECT * FROM customers WHRER id = 5 FOR UPDATE`

**FOR UDATE** clause indicates that user needs a lock here so he can do his updates safely, and this is helpful when you are using **read-modify-write** approach as below.

`BEGIN`

`SELECT * FROM customers WHERE id = 5 FOR UPDATE`

`//do your logic`

`UPDATE customers SET is_done = true WHERE id = 5`

`COMMIT`

But there is also another approach which is called **atomic updates** or **compare-and-set** which when you fire update statement database will acquire a lock and not let any transaction/user from accessing it what records your updating now until you commit.

`UPDATE customers SET available = true WHERE id = 5`

**- Table V.S Row locking level**

**Table level lock**

in this locking mechanism database lock the entire table you are working on and don't let any transaction accessing it even for another set of records, and keep this lock until you finish your transaction.

**Row level lock**

In this mechanism database locks only the records you are working on not entire table.

You may think Row level is better than table, but it depends because managing these locks by acquiring and releasing operations can be expensive in case of huge set of rows modification at same time, but also Table level lock can affect your user experience in case of long running transaction started on main table so it's now have a lock and no one can access the table until it finish.

## Two Phase Lock (2PL)

what we discussed till now has a performance impact as any transaction starts, it locks the others until it finishes, even if it just read-only transaction, so database engineers decided to do something smart, it's Two Phase Lock (2PL).

In this mechanism you have 2 types of lock, **Shared** and **Exclusive** and here how it works:

- When any transaction want to read a record, it first have to acquire **Shared** lock.

- You can have any number of **Shared** locks on same record, by this you allow multiple reads on same records.

- If you want to update the record you have to acquire **Exclusive** lock, to acquire this type of lock all **Shared** locks have to be released first so now you are the only person who have the lock.

- If you acquired the **Exclusive** lock, no one can access this record until you finishes your updates and release the Exclusive lock.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nug6978yxv7ejgkyr8cf.png)

This approach enhance performance a little bit as now not any transaction/operation locks the record, just write operation, but still has performance impact regarding to managing locks.

## To be Optimistic or not to be, that the question

What we discussed until now is known by **Pessimistic** approach, you are pessimistic that if you didn't do locking there a problem will happen, so in any case you do restrictions.

But there is another approach called **Optimistic**, by this approach you will not do any locks here you will keep all transactions free to read and write any time they want, **BUT** in order to solve the conflicts and prevent lost updates we will do these steps

- In each table there will be what called version column with default value 0.

- Whenever any update operation happen to this record it increments version by 1.

- In case of 2 transactions are updating same record at same time first one will increment the version by 1, so the second one has a outdated version number now, so the database will decide to terminate the second one and they have to fire the transaction again after getting the most recent version of this record.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c9wyfkz8bzc9ejqa5q6k.png)

## Conclusion

Regarding all these techniques we discussed here, there is no best technique or worst one, it depends what your application accept and what not, regarding to this you choose which database engine that provide locking mechanism will match your application requirement.

## Resources

- Designing Data-Intensive application Chapter 7 : Transactions.

- [Optimistic V.S Pessimistic Video](https://youtu.be/I8IlO0hCSgY)

**IF YOU LIKED THE POST, THEN YOU CAN SUPPPORT SUCH CONTENT WITH A CUP OF COFFEE, THANKS IN ADVANCE.**

<a href="https://www.buymeacoffee.com/samehmuh95" target="_blank"><img src="https://cdn.buymeacoffee.com/buttons/default-orange.png" alt="Buy Me A Coffee" height="41" width="174"></a>