Optimistic V.S Pessimistic databases

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.

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.

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.

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.

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

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