You must the isolation level of your database, as picking the wrong one can lead to data integrity issues. Here’s a quick write-up about the four core isolation levels to understand them better.
Isolation levels determine the degree to which a transaction is insulated from the effects of other concurrent transactions. Each isolation levels offer different trade-offs which have significant consequences on performance and integrity. The four most common isolation levels are
- Read Uncommitted
a transaction can read data that has been modified by other transactions, even if those modifications have not yet been committed. This leads to dirty reads because a transaction is using the data which is ultimately rolled back, resulting in inconsistent or incorrect data.
- Read Committed
a transaction can only read data that has been committed by other transactions. Although it prevents dirty reads, it does not prevent - non-repeatable reads and phantom reads.
- Repeatable Read
within a single transaction, any repeated read of a row will always return the same data, even if other transactions have modified the row in the meantime. Although stricter, it does not prevent the introduction of new rows (phantom reads) that may affect the results of a query. For example: the result set changes during pagination.
- Serializable
This is the strongest isolation level, ensuring that the execution of concurrent transactions is equivalent to a serial execution of those transactions, one after the other. This provides the highest level of data consistency but at the cost of potentially lower concurrency and performance.
By the way, I have a detailed video on read uncommitted isolation level, if you are interested in truly understanding it with practical examples and explanation,
give it a watch - youtu.be/AiPGbVjl3JY
Also, if you are SDE-2, SDE-3, and above and looking to build a rock-solid intuition to design any and every system, admissions for my System Design October cohort are open
arpitbhayani.me/course
I keep the seats limited to ensure I can answer every single question and have detailed brainstorming around the systems we discuss.