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 Read operation.
Here we discussed DB concurrency from Write operation point of view
Our use case
Let's say we have below use case, we have an E-commerce application with 2 actors user and admin.
At the same moment each on of them fire a request to the application that will start a transaction on DB
- User want to submit new Order.
- Admin want to generate weekly report of financial and order counts state of application.
The point is, the currently processing order should be displayed on the admin report or not (it's not finished yet we are still processing)?
Some people say, yes we should include it as it coming order, and not included it me display a wrong data on report, but what if we include the order and while processing some failure happens on this transaction and DB decided to rollback and delete all the data regarding to this order, now you also have wrong report data.
Because of this database engineers decided that, this decision depends on multiple factors, like business domain criticality, admin and business requirements and etc.., So they decided to defer this decision up to other engineers to decide which case is applicable for their situation.
Isolation levels
As ACID principals of database engine the letter 'I' stands for Isolation, that each transaction should run in isolated environment, and database engine provides multiple isolation levels/types to describe how transactions should behave regarding to another transaction work.
Read Committed isolation level
This is default transaction isolation level for many databases, by using this the transaction is allowed only to read data that committed as below.
Read Uncommitted / Dirty Read isolation level
In this isolation level the transaction is allowed to read any data written by any transaction at any time, but this may lead to wrong results as this can be rolled back at any time as below.
Snapshot isolation level
This isolation level is capturing a database state when a new transaction begin even the data have been committed or not and when asking for values database provides data based on this snapshot even if it changed during time as below.
Also known by "Repeatable Read" but the most descriptive one is Snapshot.
Serializable isolation level
This isolation level is the strongest one, as it prevents transaction race at all each transaction is processed one by one and not concurrent, this will prevent the issue from its source but this will make a performance impact on your application as below image.
Isolation levels comparison
Conclusion
At the end, there is no best isolation level you can choose it depends what is your use case and what is the requirements and business needs.
Resources and References
IF YOU LIKED THE POST, THEN YOU CAN SUPPPORT SUCH CONTENT WITH A CUP OF COFFEE, THANKS IN ADVANCE.
Top comments (0)